Mar 23 2021 02:39 PM
=IFS(I14=0,"0",1>=I14<=20,"1",21>=I14<=40,"2",41>=I14<=60,"3",61>=I14<=80,"4",I14>=81,"5")
How do I do this for 2016 Excel version?
Mar 23 2021 02:58 PM
=IF(I14=0,"0",IF(I14<=20,"1",IF(I14<=40,"2",IF(I14<=60,"3",IF(I14<=80,"4","5")))))
But do you really want the formula to return a text value that looks like a number? You won't be able to sum or average the return values. If you'd prefer to return a number, omit the " " around the return values:
=IF(I14=0,0,IF(I14<=20,1,IF(I14<=40,2,IF(I14<=60,3,IF(I14<=80,4,5)))))
Alternatively:
=(I14>0)+(I14>20)+(I14>40)+(I14>60)+(I14>80)
or
=SUMPRODUCT(--(F1>{0,20,40,60,80}))