Donate SIGN UP

Excel IF formula with two logical tests?

Avatar Image
funkylad20 | 09:54 Mon 05th Sep 2011 | Computers
18 Answers
Hello...

Does anyopne know how I can say 'if A1 says 'Yes' AND B1 says 100, then the value if true will be 'Incorrect', value if false will be 'Correct'?

I know about 'IF', but not sure how to work it out if I have two cells I want to look at (the logical test bit)? One assumes I need to use the & function?

Thanks!
Gravatar

Answers

1 to 18 of 18rss feed

Best Answer

No best answer has yet been selected by funkylad20. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
Funky is the value 100 a constant or does it change?
Question Author
Well, I have simplified the scenario...
The value of 100 is variable as each line could be different, but the 'cut off' willbe 100 (so for example, 99 would be correct, 100 will be incorrect, 101 willbe incorrect etc.)
=IF(AND(A1="yes", B1=100),"true","false")
OK then given your second answer

=IF(AND(A1="yes", B1<99),"true","false")
i think it will be =IF(AND(A1="yes", B1=100),"true","false")
Sorry Chuck's answer wasnt there when i typed that :)
=IF((AND(A1="yes",B1=100)=TRUE),"Incorrect","
Correct")
Chuck given Funkys reply would

=IF(AND(A1="yes", B1<=100),"true","false")

Be correct?
Question Author
OK, thanks. It worked for a bit, then stopped...I may have to use the actuals to see if any of you can help...

I have the following formula:
=IF(AND(E51=$E$3, G51<50001),"Eligible","Ineligible")

E51 is either type 'Statutory' or 'Non-Statutory'. G51 is the value of money.

If the value of money is OVER £50,000 and it is also Statutory, then the result should be 'Eligible'. If the type is 'Non-Statutory', then it should be 'Ineligible'.

as I say, the foruma you ahve worked and I have applied into my scenario works to an extent, but then for some it doesn't...?
=IF(AND(E51=$E$3, G51<50001),"Eligible","Ineligible")

Why are you referencing absolute cell E3 with the first argument? (E51=$E$3) surely the following would be easier...

=IF(AND(E51="Statutory", G51<50001),"Eligible","Ineligible")
Question Author
Oh I fix it because I use a drop down, drawing from E2 and E3. E3 is 'Non-statutory' and E2 is 'Statutory'.

This formula works for 'Non-statutory' and less than £50,000, BUT when I hit a line that is 'Statutory' and the value is £63,000, it says 'ineligible' (the threshold for 'Statutory' is up to £150,000 before it becomes ineligible....)
Chuck . .

If I understand correctly - To be 'Eligible' we need to be 'Statutory' and between 50,000 and 150,000 . . .

so try . .

=IF(AND(E51="Statutory",AND( G51>50000,G51<150000)),"Eligible","Ineligible
")
sorry - my answer should have been to funkylad, not chuck - my bad!

also - you may need to add or subtract 1 from either figure (depending on how your limits work)
If im grasping what you are trying to do correctly it would be something like...

=IF(OR(AND(A1="yes",B1<50000),AND(A1="no",B1>
15000)),"Eligible","ineligible")


You see to be able to modify quite well once pointed in the correct direction so see what you can do.
Question Author
CHUCK, that works a treat, thank you, spot on.
Just trying to work out the 'if A1 = BLANK, then return a blank cell' as they are all 'inelgible at present' until they are gradually filled in!
you can have nested IF statements, so:

if(A1="","",IF(OR(AND(A1="yes",B1<50000),AND(
A1="no",B1>

15000)),"Eligible","ineligible"))

(Cut the 2nd "if" statement from chuck's post above; make sure you have all your parentheses in order!)
Question Author
that's it...I just put the =IF(A1="","" too late!
Final formula is:
=IF(A21="","",IF(OR(AND(E21=$E$3,G21<50001),A
ND(E21=$E$2,G21>15001)),"Eligible","Ineligibl
e"))


As ever, thank you, massively appreciated!
One of the more complicated excel ones I've seen on this site :)

Glad it worked.

1 to 18 of 18rss feed

Do you know the answer?

Excel IF formula with two logical tests?

Answer Question >>

Related Questions

Sorry, we can't find any related questions. Try using the search bar at the top of the page to search for some keywords, or choose a topic and submit your own question.