CODEWITHSUNDEEP
nestedifs
Charles Clark
Charles Clark

Reputation: 1

How to simplify formula - IFS nested multi-formulas or other method

I would like to create a formula that will do the following:

If SUM(G20:M20) is between 45 and 60 then = 100%.
If SUM(G20:M20) is between 37 and 44 then = 95%.
If SUM(G20:M20) is between 29 and 36 then = 90%.
If SUM(G20:M20) is between 21 and 28 then = 85%.
If SUM(G20:M20) is between 13 and 20 then = 80%.
If SUM(G20:M20) is between 5 and 12 then = 75%.
If SUM(G20:M20) is between 0 and 4 then = 70%.

I have this but it doesn't work

=IF(AND((SUM(G20:M20>45, "100%",IF(AND(G20:M20>37, SUM(G20:M20<44), "95%",IF(AND(G20:M20>29, SUM(G20:M20<36), "90%",IF(AND(SUM(G20:M20>21, SUM(G20:M20<28), "85%",IF(AND(SUM(G20:M20>13, SUM(G20:M20<20, "80%",IF(AND(SUM(G20:M20<12), SUM(G20:M20>5, "75%" ,IF(AND(SUM(G20:M20>0, SUM(G20:M20<4), "70%")))

Upvotes: 0

Views: 21

Answers (0)

Related Questions