In cell A1 value is 50. If the value is less than 50 then result should be 0. If its >= 50 and below 60 then value should be 5. If its above 60 and below 69 the value should be 6. If its above 70 and below 80 the value should be 6 and above 80 should be 7. How to write a nested formula for this?
3 Answers
Should be something like this
=IF(A1<50,0,IF(AND(A1>=50,A1<60),5,IF(AND(A1>60,A1<69),6,IF(AND(A1>70,A1<80),6,IF(A1>80,7)))))
Comments
If you have Excel-365 then could use XLOOKUP() with match mode -1.
=XLOOKUP(A1,{0,50,60,70,80},{0,5,6,6,7},"",-1)
Comments
So, if you have to nest 3 or more if(), then I prefer a list, but the if() can also be shorter, see:
=IF(A1<50,0,IF(A1<60,5,IF(A1<69,6,IF(A1<80,6,IF(A1>80,7)))))
=VLOOKUP(A1,G3:H7,2,1)
The list is easily seen and edited to change values as needed, compared to the long if() or putting the values directly in the vlookup().
