Using IF with more than 3 items

Agata Wozniak

Hi,

I gotten so much help here before, so once again... I have a column Total Rank Score where we assign points to each task.

I want to be able to group those into 3 categories. If value is between 0 and 7 = "Low", if between between 7 to 10= Medium , If over 10 points ="High"

=IF([Total Rank Score]@row <= 7, "LOW", IF([Total Rank Score]@row > 7, "MEDIUM", IF([Total Rank Score]@row > =10, "HIGH")))

But it seems to be missing something, because the HIGH never shows ups

Nick Korna

Hi @Agata Wozniak,

Your IF statements are reading sequentially. As you have "medium" before the "high", anything which should be "high" is already captured by "medium" (greater than 7). You can get round this by either reversing the order of the IF statements, or changing how you work out the "Medium" as below:

=IF([Total Rank Score]@row <= 7, "LOW", IF([Total Rank Score]@row <= 10, "MEDIUM", IF([Total Rank Score]@row > 10, "HIGH")))

Anything 7 or below should be recorded as LOW.

Anything equal/less than 10 that isn't already LOW, will be MEDIUM.

Anything over 10 will be HIGH.

Hope this helps, but if you've any problems/questions then just post! ☺️

Agata Wozniak

Hi,

Unfortunately this doesn't work, when I amended the formula to be this:

=IF([Total Rank Score]@row <= 7, "LOW", IF([Total Rank Score]@row < =10, "MEDIUM", IF([Total Rank Score]@row > 10, "HIGH")))

There is a mistake coming up "Invalid Operation" .

Nick Korna

Remove the space here between the < and =:

This should fix the error.

Agata Wozniak

Thank you, that solved the "Invalid Operation", but a new issue comes up. Now when the value is above 10 the returned information is blank field.

Apologies for the trouble, but if you would have an answer to this as well I would be very grateful!

Nick Korna

I'm not 100% certain from the screenshot - it seems to work fine for me:

What is your formula in the Total Rank Score? And the column heading isn't showing as a formula - is the high/med/low definitely in that cell?

Agata Wozniak

Thank you! It works now.

Nick Korna

Glad it's all resolved now. 🙂