Nested IF formula

Help -

In column 'H' I have calculated percentages. I need to be able to divide the rows based on the percentage derived in column H.
The five categories I need to divide the data into are: 0% to <25%, 25% to <50%, 50% to <75%, 75% to < 90%, and 90% to 100%.
Each of these categories will be given a numerical value of 1 through 5 so that 0-<25% = 1, 25-<50% = 2, 50-<75% = 3, 75-<90% = 4, and 90-100% = 5.

I have tried this formula several different ways and none of them work. Here's a few of the ways that I tried and thought
might work but didn't:



=IF(($H2>=0.9), 5, IF(AND($H2>=0.75,$H2<=0.89)), 4, IF(AND($H2>=0.5,$H2<=0.74)), 3, IF(AND($H2>=0.25,$H2<=0.49)), 2, IF(AND($H2>=0.0,$H2<=0.24)), 1)
=IF($H2>=0.9, 5, IF(AND($H2>=0.75,$H2<0.9)), 4, IF(AND($H2>=0.5,$H2<0.75)), 3, IF(AND($H2>=0.25,$H2<0.5)), 2, IF(AND($H2>=0.0,$H2<0.25)), 1)

I even put the percentage values in seperate cells to reference but that didn't work either:
=IF($H2>=$J$2,5,IF(AND($H2>=$J$3,$H2<$J$2)),4,IF(AND($H2>=$J$4,$H2<$J$3)),3,IF(AND($H2>=$J$5,$H2<$J$4)),2,IF(AND($H2>=$J$6,$H2<$J$5)),1)

What am I doing wrong and what do I need to do to fix it?

Mike

For a single cell solution...
Try this:

Please Login or Register  to view this content.

or...for more flexibility...

Put this table in A1:B5

Please Login or Register  to view this content.

Then use this formula:

Please Login or Register  to view this content.

Does that help?

Originally Posted by MAPepin

I don't think that LOOKUP or the VLOOKUP function will work with what I have. It's looking for an exact number and I need to look at a range.

Not true...See Excel Help for each of the functions:
LOOKUP does not look for an exact match.
VLOOKUP uses approximate matches when the 4th argument is 1 or omitted.
MATCH uses approximate matches when the 3rd argument is 1 or omitted.

Using the setup from my previous post and your percent samples in H1:H8
AND...any of these formulas:

Please Login or Register  to view this content.

Copy the I1 formula down through I8.

These are the returned values:

Please Login or Register  to view this content.

Are those value incorrect? Am I missing something?