Numbers formula with multiple options

In the IFS function you should first test < 26, then test <51, and then test <101.

And not the other way around: each number will be < 101. Hence if the first test in the IFS function is <101, you will always get the score 4 for <101.

Paul.

You could use the IFS function like this:

Replace ; with , in the function shown depending on your region.

If you intervals are uniform (often a good idea) then you can try something short and simple like this:

=CEILING(A2,25%)/25%+1

Or if original scores are in numbers, not %:

=CEILING(A2,25)/25+1

SG

Stfflspl mistyped the percentage to number equivalence in an earlier post. 70% =0.7 not 0.07. But if you use the % values in your formula (rather than converting them), you shouldn't have any problem.

I am guessing that you did not use the ROUND function in the formula in column AA. If you did not, while the percentage values in column AA are displayed rounded to the nearest percent, the actual values in the cells are not rounded. You may see "20%" in column AA but the true value may actually be anywhere from 19.50% to 20.4999...%. In the IFS formula, the first will will give the answer of 1, the second will give 2, even though the input looked like 20% for both. You will have to fix that for your IFS formula to work correctly. Using your description of the cells as "Cell one" and "Cell two" the formula in column AA will be

=ROUND(Cell two/Cell one,2)

With that fixed, your IFS formula is

=IFS(AA14<20%,1,AA14<40%,2,AA14<60%,3,AA14<80%,4,AA14>=80%,5)

You did not specify what to do if AA14<1 so I assumed anything less than 20% will give the result of 1

You did not specify what to do if AA14>100 so I assumed the result will be 5

You could try something like this:

MIN(FLOOR($A18÷20,1)+1,5)

The MIN function is only needed for covering the case of "100".

Paul.

Could you please take a screenshot of your formula in the table and insert the image (clicking on the mountain token).

It might be helpful displaying what you are doing.

Paul.

Of course: 70% = 0,07 (or 0.07 in the English notation).

I changed the IFS function: testing for percentages like this:

As you can see in my table 1, this will work in your case (percentages in column AA).

Paul.