Excel IF formula with two logical tests?

[funkylad20]

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!

[Davethedog]

Funky is the value 100 a constant or does it change?

[funkylad20]

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

[ChuckFickens]

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

[ChuckFickens]

OK then given your second answer

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

[BrokenWife]

i think it will be =IF(AND(A1="yes", B1=100),"true","false")

[BrokenWife]

Sorry Chuck's answer wasnt there when i typed that :)

[rojash]

=IF((AND(A1="yes",B1=100)=TRUE),"Incorrect","
Correct")

[Davethedog]

Chuck given Funkys reply would

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

Be correct?

[funkylad20]

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

[ChuckFickens]

=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")

[funkylad20]

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

[OCD1]

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
")

[OCD1]

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)

[ChuckFickens]

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.

[funkylad20]

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!

[dr b]

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!)

[funkylad20]

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!

[ChuckFickens]

One of the more complicated excel ones I've seen on this site :)

Glad it worked.