Make IFS Formula with certain range criteria-CodePudding

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

CodePudding user response：

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.