Assign a value to a percentage with decimals

[rhybeka]

Hi All!

Boss came to me for a hand creating formula that would look at the adjacent cell's percentage (obtained by adding several cells from other tabs together) and assign a value. I thought it would be simple enough to set up a lookup table but it turns out the percentages can include a decimal and are not only whole number percentages.

0 is anything	<95%
1 =	95-96%
2 =	96.1-97%
3 =	97.1-98%
4 =	98.1-99%
5 =	99.1-100%

So the data would look something like this:

97.25%	3
99.59%	5
98.53%	4

This should be somewhat simple, but I'm stumped. I've also started an IF/AND nested statement for similar data, but it doesn't completely work (it's assigning anything greater than 7 and 0's incorrectly) . I could modify it for this as well though, I think?

Please Login or Register  to view this content.

I think I'm missing something really simple, I'm just not sure what it is and I can't seem to find anything on the forum to point me in the right direction. Any help would be greatly appreciated.

[AliGW]

Have a look at the VLOOKUP function.

[63falcondude]

Try something like this:

=LOOKUP(A1,{0,0.95,0.961,0.971,0.981,0.991},{0,1,2,3,4,5})

[JohnTopley]

This in A1:B6

0 0
95.0% 1
96.1% 2
97.1% 3
98.1$ 4
99.1% 5


= VLOOKUP(G1,$A$1:$B$6,2,1)


G1 is your lookup %

[rhybeka]

Thanks all! I've had to amend a few of the statements so they pick up numbers outside of the ranges, otherwise the VLOOKUP is working well.

Please Login or Register  to view this content.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.