Forum Discussion
Excel IF/IFS formula not calculating scenario "B"
Hi,
Hopefully an easy solution that's staring me in the face, but I've been trying to fix this for an hour and I'm getting nowhere. Any pointers appreciated.
I've been working on a new calculator workbook and in the final stages I'm having issues with the IFS function.
The workbook will calculate a different total for each cell depending on the scenario chosen. I have two variable outcomes, due to a known value only being possible for either S15 or S21 (and the unknown value being resultant/residual).
Cell S15 = A residual or fixed number dependant on toggle selection
Cell S21 = A residual or fixed number dependant on toggle selection
I have two "control" cells that determine the fixed value of S15/S21 as applicable. The control for S15 is Z17 and the control for S21 is Z18.
The other cells referenced in the formula below are all subtotals of either revenue or cost.
I have managed to get S15 to calculate correctly using only the following "IF" formula as such:
=IF(S9="YES",SUM(N52+N57)-(S16+S17+S18+S19+S20+S22+S43+S49+T64),Z17)
This completes the residual calculation if the scenario is "YES". It defers to cell Z17 if "NO" is selected. Perfect.
On the second part of the equation I cannot get it to calculate correctly. So far I have tried:
=IF(S9="YES",(N52+N57)*Z18,SUM(N52+N57)-(S15+S16+S17+S18+S19+S20+S22+S43+S49+T64))
This returns the correct calculation for "YES" no matter the list selection.
=IFS(S9="YES",(N52+N57)*Z18,S9="NO",SUM(N52+N57)-(S15+S16+S17+S18+S19+S20+S22+S43+S49+T64))
This returns the correct calculation for "YES", but returns a blank for "NO".
3 Replies
what is in S15? that is the only difference. I'm surprised it returns blank as opposed to some sort of error.
also try using SUM this way:
=IF(S9="YES",(N52+N57)*Z18, SUM(N52,N57)-SUM(S15:S20,S22,S43,S49,T64))
because SUM(N52+N57) really isn't using the SUM function because it will add N52+N57 and then apply the SUM() to a single digit. Similarly I applied the SUM() to the other list and took advantage of referencing a range of cells S15:S20 instead of adding each individually. The ability to add a range of cells is one of the advantages of using SUM(). Another is that SUM() will ignore text values and not throw an error.
It depends. If in N52 and N57 we have "1" and "2" (not 1 and 2), when SUM(N52+N57) gives 3. SUM(N52,N57) returns zero.
Without file or at least screenshot that's only guesses.
that is an excellent point that TEXT that can be interpreted as numbers will be evalutaed using + while SUM will ignore. But I still don't see a case for having both. how is SUM(N52+N57) any different than N52+N57
