Nested IF formula

[MAPepin]

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

[Ron Coderre]

For a single cell solution...
Try this:

=LOOKUP(H2,{0;0.25;0.5;0.75;0.9},{1;2;3;4;5})
or this:
=MATCH(H2,{0;0.25;0.5;0.75;0.9})

or...for more flexibility...

Put this table in A1:B5

 0%     1
25%     2
50%     3
75%     4
90%     5

Then use this formula:

=VLOOKUP(H2,A1:B5,2,1)

Does that help?

[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.
Here's what I have (columns F, G, H, and I are the only columns containing working data so these are what I have copied here.

Column H is the percentile of F/G. In column I, I need to place the numbers 1 through 5 based on the percentile in column H and the criteria previously posted ( 0% - <25% = 1, 25% - <50% = 2, 50% - <75% = 3, 75% - <90% = 4, 90% - 100% = 5 )

row
1 F G H I
2 91 46 51%
3 16 0 0%
4 103 3 3%
5 155 73 47%
6 26 4 15%
7 62 12 19%
8 52 6 12%
9 10 1 10%

[Ron Coderre]

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:

I1: =VLOOKUP(H1,$A$1:$B$5,2,1)
I1: =LOOKUP(H1,{0;0.25;0.5;0.75;0.9},{1;2;3;4;5})
I1: =MATCH(H1,{0;0.25;0.5;0.75;0.9})

Copy the I1 formula down through I8.

These are the returned values:

51%     3
 0%     1
 3%     1
47%     2
15%     1
19%     1
12%     1
10%     1

Are those value incorrect? Am I missing something?

[estige]

Hi.
Lookup will work just fine, but you could also use a nested IF formula as you first intended if you prefer that:

=IF(H1<0.25,1,IF(H1<0.5,2,IF(H1<0.75,3,IF(H1<0.9,4,5))))