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
Bookmarks