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

  Kiến thức lập trình

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

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

I have this but it doesn’t work

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

Thanks

  • nested
  • ifs

New contributor

Charles Clark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website

LEAVE A COMMENT