How can I combine two Excel formulas into one?

Question

In a spreadsheet, I have five columns (total) and four of them have the components of the students' grade. The fifth has the final grade.

Now, I need to combine two formulas so that when the four columns have the grades, the first formula executes and when columns 1 and 2 have the grades, the second formula executes.

1st formula:

=(CEILING(IF(AND(D2=97;E2=97);(F2+G2*2+F2+G2*4)/8;IF(D2=97;(F2+E2*2+F2+G2*4)/8;IF(E2=97;(D2+G2*2+F2+G2*4)/8;(D2+E2*2+F2+G2*4)/8)));0.25))

2nd formula:

=(CEILING(IF(AND(D2=97);(E2*2)/2;IF(E2=97;(D2*1)/1;(D2+E2*2)/3));0.25))

Answer 1

To combine two Excel formulas into one, you need to ensure that each condition is evaluated correctly and integrated logically. For example, if you have two COUNTIFS formulas that you want to combine based on specific criteria, you can do so by summing the results of both COUNTIFS.

Here’s an example based on combining formulas:

1. First formula: Counts entries within a date range.

   =COUNTIFS('FFRCA - FMLA Archive'!D8:D2500, ">=1/1/2020", 'FFRCA - FMLA Archive'!D8:D2500, "<=12/31/2020")
   

2. Second formula: Counts entries based on a specific value in another column.

   =COUNTIFS(B10:B4821, 10086143)
   

To combine these, you can use the + operator if you want to sum the results or * if you want a logical combination (e.g., both conditions must be met):

=COUNTIFS('FFRCA - FMLA Archive'!D8:D2500, ">=1/1/2020", 'FFRCA - FMLA Archive'!D8:D2500, "<=12/31/2020") + COUNTIFS(B10:B4821, 10086143)

In another example with more complex formulas:

1. First formula:

   =CEILING(IF(AND(D2=97, E2=97), (F2+G2*2+F2+G2*4)/8, IF(D2=97, (F2+E2*2+F2+G2*4)/8, IF(E2=97, (D2+G2*2+F2+G2*4)/8, (D2+E2*2+F2+G2*4)/8))), 0.25)
   

2. Second formula:

   =CEILING(IF(D2=97, (E2*2)/2, IF(E2=97, (D2*1)/1, (D2+E2*2)/3)), 0.25)
   

You can use an IF statement to choose between these formulas based on specific conditions:

=IF(AND(D2<>"" , E2<>"" , F2<>"" , G2<>""), CEILING(IF(AND(D2=97, E2=97), (F2+G2*2+F2+G2*4)/8, IF(D2=97, (F2+E2*2+F2+G2*4)/8, IF(E2=97, (D2+G2*2+F2+G2*4)/8, (D2+E2*2+F2+G2*4)/8))), 0.25), CEILING(IF(D2=97, (E2*2)/2, IF(E2=97, (D2*1)/1, (D2+E2*2)/3)), 0.25))

This combined formula will check if all the relevant cells (D2, E2, F2, G2) are not empty and apply the first formula. If some of these cells are empty, it applies the second formula.