Oct 07 2019 12:37 PM
I have created formulas that generate a number based on the value in another cell. What I can't figure out how to do is then create a formula to add the generated numbers. Can someone please help?
Cell n2 has the formula: =IF(SUM(F2)>= 220, "0", IF(SUM(F2)>=214, "1", IF(SUM(F2)>=208, "2", IF(SUM(F2)>=0, "3"))))
Cell o2 has the formula: =IF(SUM(H2)>= 220, "0", IF(SUM(H2)>=214, "1", IF(SUM(H2)>=208, "2", IF(SUM(H2)>=0, "3"))))
Cell p2 has the formula: =IF(SUM(K2)>= 3, "0", IF(SUM(K2)>=2, "2", IF(SUM(K2)>=1, "3")))
Cell q2 has the formula: =IF(SUM(L2)>= 220, "0", IF(SUM(L2)>=214, "1", IF(SUM(L2)>=208, "2", IF(SUM(L2)>=0, "3"))))
I need cell r2 to add the values from n2:q2. Putting is the typical sum formula is giving me a value of zero each time.
Though
Oct 07 2019 12:44 PM
That since you return texts by your initial formulas, not numbers. SUM() ignores any texts.
Use something like
=IF(F2>= 220, 0, IF(F2>=214, 1, IF(F2>=208, 2, IF(F2>=0, 3, -1))))
Oct 07 2019 10:00 PM
SolutionIf you don't want to revise your formulas in N2:Q2, you may use any of these formulas in R2:
1. Array formula (entered with Ctrl+Shift+Enter): =SUM(--N2:Q2)
2. Non-array formula: =SUMPRODUCT(--N2:Q2)
Oct 08 2019 09:29 AM
Nov 03 2019 07:14 PM
i am looking to understand financial formulas and how to explain them to a class
Nov 03 2019 07:33 PM
@holy90 You posted that you're "looking to understand financial formulas and how to explain them to a class" but you posted at the tail end of a string on a different subject.
A new topic--which yours is--should be started as a "New Conversation" where you'd get a much better response. Do that on this page: https://techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral
Nov 05 2019 09:02 AM
Oct 07 2019 10:00 PM
SolutionIf you don't want to revise your formulas in N2:Q2, you may use any of these formulas in R2:
1. Array formula (entered with Ctrl+Shift+Enter): =SUM(--N2:Q2)
2. Non-array formula: =SUMPRODUCT(--N2:Q2)