# Assign a value to a percentage with decimals

1. ## Assign a value to a percentage with decimals

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.

2. ## Re: Assign a value to a percentage with decimals

Have a look at the VLOOKUP function.

3. ## Re: Assign a value to a percentage with decimals

Try something like this:

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

4. ## Re: Assign a value to a percentage with decimals

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)

5. ## Re: Assign a value to a percentage with decimals

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.``

6. ## Re: Assign a value to a percentage with decimals

There are currently 1 users browsing this thread. (0 members and 1 guests)