Google-sheets – Need a formula to make Google Sheets choose between two formulas

formulasgoogle sheetsworksheet-function

I need a formula that will choose between two formulas. =COUNT(L2,M2,N2,O2,P2,Q2,R2) and =IF(S2<232,0,7).

In this instance, I'm weighing totals on given days individually vs. the total for the week. L2-R2 correspond to a numeric value above 43 on each of the days of the week. S2 represents the total numeric value for the week.

If any of L2-R2 have a numeric value above 43, 1 point is added in the first formula, contained in CELL V and that total is the point value for the week.

However if the total numeric value for the week exceeds 232 for the week, as indicated in the second formula, the point value for the week is 7.

I want a formula that decides that if =IF(S2<232,0,7) returns true, have that formula show its output. if it returns false, I want =COUNT(L2,M2,N2,O2,P2,Q2,R2) show its output.

As it stands right now, I have each formula in its own CELL and have to visually compare, which is tedious, given the sheer number of times I have to compare.

Best Answer

Formulas can contain other formulas: if(condition, formula1, formula2). In your example, one can do 

=IF( IF(S2<232,0,7), IF(S2<232,0,7), COUNT(L2,M2,N2,O2,P2,Q2,R2) )

so that when the output of the first formula is True, the If gets used, and otherwise COUNT gets used.

The above can be simplified to 

=IF(S2<232, 7, COUNT(L2,M2,N2,O2,P2,Q2,R2) )

but I included the more complicated version to show the general idea.

Related Topic