# IF cell is between % AND % return text + IF another...

1. ## IF cell is between % AND % return text + IF another...

Hi guys,

I got this formula which should be simple, but for some reason can't make it work.

Original one is:

=(IF(AND(AA5>10%,AA5<19.999%),"C")

and it returns result C, so all good here.

But trying to blend few criterias, and nothing happens, I get ERROR.

Basically, the goal is: IF values are between IF below 10% show D, IF between 10%-19.99%, show C, IF between 20%-29.99% show B, IF >= 30% show A

How do I blend these together, please?  Register To Reply

2. ## Re: IF cell is between % AND % return text + IF another...

Your formula isn't telling it what to do if the condition is outside of those values. it should be something like =IF(AND(AA5>10%,AA5<19.999%),"C", something else)  Register To Reply

3. ## Re: IF cell is between % AND % return text + IF another...

=vlookup(c1,\$a\$1:\$b\$7,2)  Register To Reply

4. ## Re: IF cell is between % AND % return text + IF another...

Here is an alternative because I only addressed the first part of your question but not the second part...
=LOOKUP(AA5,{0,0.1,0.2,0.3},{"D","C","B","A"})  Register To Reply

5. ## Re: IF cell is between % AND % return text + IF another...

Thanks for your help guys. But it isn't lookup function we looking for here, sorry if made it unclear somewhere.

It really is just:

=(IF(AND(AA5>10%,AA5<19.999%),"C") (which works well on it's own, but a problem starts when blending in additional criterias)
+
(IF(AND(AA5>20%,AA5<29.999%),"B")
+
(IF(AA5>30%,"A")
+
(IF(AA5<10%,"D")

Is there a way to mix these criterias together? I tried

=(IF(AND(AA5>10%,AA5<19.999%),"C")+ (IF(AND(AA5>20%,AA5<29.999%),"B")

or

=(IF(AND(AA5>10%,AA5<19.999%),"C"),(IF(AND(AA5>20%,AA5<29.999%),"B")

but it keeps returning ERROR or VALUE results :-(  Register To Reply

6. ## Re: IF cell is between % AND % return text + IF another...

You want nested IF statements: ``Please Login or Register  to view this content.``
...which returns the same as the LOOKUP formula in post#4

The tests are done sequentially, so testing for higher values is only done if lower values were not found. This eliminates the need for AND statements with two criteria each.  Register To Reply

7. ## Re: IF cell is between % AND % return text + IF another...

well this would be the "consolidation" of those formulas, I would reorder it to this...
=IF(A1155>=30%,"A",IF(AND(AA5>20%,AA5<29.999%),"B",IF(AND(AA5>10%,AA5<19.999%),"C",IF(AA5<10%,"D"))))
but it will produce D as a result even if the cell is blank. You might want to proceed the formula with IF(AA5="","" then follow it with the rest.
and my lookup formula is easier and shortens the IF / then formula.

EDIT: oh, and leelnich's formula is also cleaner and simpler than yours too. EDIT2: goes to show you what just trying to answer the question does, I should've known to give you the simpler version that leelnich gave you in post #6 as I know that you don't need the AND statements in between. Sheesh! Good one leelnich!  Register To Reply

8. ## Re: IF cell is between % AND % return text + IF another...

=IF(AA5<10%,"D",IF(AA5<20%,"C",IF(AA5<30%,"B","A")))

That was it! Thank you guys, appreciate your help. You're absolutely awesome :-)

Have a great weekend.  Register To Reply

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