If statement for a range of numbers

**whalleyb** · 08-28-2009, 10:50 AM

Hello,

I have tried over and over to get a point value from the percentages posted below but my formula will not return consistent results. Am I doing something wrong.

94% - below 1 Point
95%-96% 2 Points
97% 3 points
98%-99% 4 Points
0% or 100% 5 Points

Reference Cell is AG2 and is .99 but for some reason I return a 5
=IF(AG2=0,5,IF(AND(AG2>0,AG2<=0.94),1,IF(AND(AG2>=0.95,AG2<=0.96),2,IF(AG2=0.97,3,IF(AND(AG2>=0.98,AG2<=0.99),4,5)))))

Please help

**Palmetto** · 08-28-2009, 11:01 AM

I haven't tested your formula, but you do have a space character in the range reference in the last argument: "A G2" should be AG2

**ChemistB** · 08-28-2009, 11:02 AM

Is AG2 coming from a formula? It may be > 0.99 and only displaying as 0.99. You'd need to use the ROUND function on the formula to make sure it's rounding to the same as the display. Otherwise it looks like it should work. Did that help?

**NBVC** · 08-28-2009, 11:03 AM

Try

=Lookup(AG2,{0,.0001,.95,.97,.99,1},{5,1,2,3,4,5})

**ChemistB** · 08-28-2009, 11:06 AM

Originally Posted by Palmetto

I haven't tested your formula, but you do an a space character in the range reference in the last argument: "A G2" should be AG2

FYI, For some reason the forum inserts a blank space every 50 characters (if there is none within the last 50). So not Whally's fault. Guess that's for readability.

**whalleyb** · 08-28-2009, 11:08 AM

The lookup works perfectly, thank you so much

**NBVC** · 08-28-2009, 11:17 AM

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save