hello, I’m practicing using the IFS function with a range of criteria like the picture. The formula i wrote is:
=IFS(0<=H5<1,25;1;H5<=2,5;2;H5<=3,75;3;H5<=5;4;TRUE;5)
but the score not given 1 if the result is between 0 and 1,25. It also not given 5 if the result is <0.
please help
>Solution :
You have to use AND() function inside IFS(). Try-
=IFS(AND(B3>0,B3<1.25),1,AND(B3>=1.25,B3<=2.5),2,AND(B3>=2.51,B3<=3.75),3,AND(B3>=3.76,B3<=5),4,OR(B3<0,B3>5),5)
You can simplify it using XLOOKUP(). Try-
=XLOOKUP(B3,{-9999,0,1.25,2.51,3.76,5},{5,1,2,3,4,5},"",-1)
Please note: Comma
,and semicolon;depends on regional settings. Please use semicolon instead of comma for your excel.
