Problem with IF/AND/OR formula

[WillM66]

I'm trying to construct a spreadsheet and I'm having trouble with a formula.

I want the formula to be able to do the following:

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]-->IF cell e3 is greater than 3 and cell m3=true or cell n3=true show 'true' if not show 'false'

Try as I might, I just can't get it to work.

Thanks in advance for any responses.

Will M

 

[Legacy 185660]

=IF((E3 > 3)*((M3=TRUE)+(N3=TRUE)),TRUE,FALSE)
Try that, I have not tested it, but it should work

 

[WillM66]

=IF((E3 > 3)*((M3=TRUE)+(N3=TRUE)),TRUE,FALSE)
Try that, I have not tested it, but it should work

Thanks for that - works a treat.....BUT! I'd missed one of the arguments...

also needs to show TRUE if cell i3 = "Y"

Cheers,

Will

 

[Legacy 185660]

So if I3 shoulds anything else the entire argument should be false?
=IF((E3 > 3)*(I3="Y")*((M3=TRUE)+(N3=TRUE)),TRUE,FALSE)

And basically * means and, while + means or.
the excel built in and/or fail with big arguments, so its a lot easier to use the */+

 

[WillM66]

So if I3 shoulds anything else the entire argument should be false?
=IF((E3 > 3)*(I3="Y")*((M3=TRUE)+(N3=TRUE)),TRUE,FALSE)

And basically * means and, while + means or.
the excel built in and/or fail with big arguments, so its a lot easier to use the */+

Cheers - I changed the * after the (I3="Y") aprt to a + and it worked just great.

I promise this will be the last one (I promise I've tried, but just can't get this to work)

I need another formula that does the following

If Cell P3 is "FALSE" cell should equal 0 (zero)

If P3 = "TRUE" and M3 reads "College" - result needs to be "College Pass"

But if M3 reads "Public" I need to show a value of £250.00


Sorry to pester you again!

Will M

 

[Legacy 185660]

=IF(P3="FALSE",0,IF((P3="TRUE")*(M3="College"),"College",IF(M3="Public","$250","ERROR))

Try that

 

[Scott Huish]

You don't need all those If's. Boolean functions return Boolean Values;

=AND(E3>3,I3="Y",OR(M3=TRUE,N3=TRUE))

should suffice for your first question.

 

[Scott Huish]

Second question, perhaps:

=IF(P3,LOOKUP(M3,{"College","College Pass";"Public",250}),0)

 

[WillM66]

=IF(P3="FALSE",0,IF((P3="TRUE")*(M3="College"),"College",IF(M3="Public","$250","ERROR))

Try that

Doesn't seem to be working.

When I have 'PUBLIC" in cell M3, but "FALSE" IN P3 it returns £250 (should be zero)

When I input COLLEGE in M3 it returns #NAME?

Sorry to be a pain.

Will M

 

[Scott Huish]

Did you try my formula?