If between 3 different cirteria

[KevyWevz]

Hello Excel peeps!

I'm a little embarrased to admit I'm having a little trouble with a nested if between formula

I am trying to assign a value to 1 of 4 distinct categories

<30
Between 30 and 60
Between 60 and 90
> 90

So if cell A1 is 29, it should return the 1st category above. If A1 id 87, it should return the 3rd category etc

I know to use IF(AND( for this but I am having a little trouble nesting is appropriately. It's likely the order I am attempting to arrange the formula in but would anyone be able to provide an example of how I could construct this if possible?

Any help at all much appreciated!

Kind regards,
Kevin

[JohnTopley]

Try

=LOOKUP(A1,{0,30,60,90},{1,2,3,4})

1-4 are your "Categories"

or

=LOOKUP(A1,{0,30,60,90},{"1st","2nd","3rd","4th"})

[queuesef]

I started my data in Column A1 and had other values in a2:a6 to test this formula:

Please Login or Register  to view this content.

;
A correction to your scheme was made to allow the 4th tier to be >= 90. If I understand your Tiers, the 3rd tier is between 60 & 90 but can't equal 90.

[Pepe Le Mokko]

To return 1,2,3,or 4 =MAX(1,MIN(CEILING(B1,30)/30,4))

[KevyWevz]

That has indeed done it, thank you so much guys!!