Excel formula IF for multiple conditions on value-CodePudding

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?

CodePudding user response：

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)))))

CodePudding user response：

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)