Google Sheets if and ifs statements-CodePudding

I have a range of results data between 1-9 , which I want to convert to a grade.

=IFS(I2<2,"2B",I2<3,"2P",I2<4,"2A",I2<5,"3B",I2<6,"3P",I2<7,"3A",I2<8,"4B",I2<9,"4P",I2<10,"4A",I2<11,"5B")

The problem I have is how to include a grade when the number is a decimal. Above the .4 the grade stays down, above .4 it goes up. Foe example 6.4 ="3A", 6.6="4B"

CodePudding user response：

try:

=IFS(I2<2.5, "2B", I2<3.5, "2P", I2<4.5, "2A", I2<5.5, "3B", I2<6.5, "3P", 
     I2<7.5, "3A", I2<8.5, "4B", I2<9.5, "4P", I2<10.5, "4A", I2<11.5, "5B")

CodePudding user response：

You can try round function.

=IFS(round(I2)<2,"2B",round(I2)<3,"2P",round(I2)<4,"2A",round(I2)<5,"3B",round(I2)<6,"3P",round(I2)<7,"3A",round(I2)<8,"4B",round(I2)<9,"4P",round(I2)<10,"4A",round(I2)<11,"5B")