Difficulty with IF, OR, AND formula

[RW1992]

Hi!

Fairly new to Excel and having a bit of difficulty. I'm trying to get the cell to return "TRUE" if AA21 and AC21 are both equal to "1", and also if AA21 and AC21 are both equal to "0". Then I'd like the cell to return "FALSE" if AA21 equals "1" but AC21 equals "0". I've used the following formula and it seems to always return "FALSE" no matter what I do.

=IF(OR(AND(AA21=,AC21=1),AND(AA21=0,AC21=0)),TRUE,IF(AA21=1,AC21=0,FALSE))

Anyone able to help?

Thanks!

[AliGW]

You will need this:

=IF(OR(AND(AA21=1,AC21=1),AND(AA21=0,AC21=0)),TRUE,IF(AND(AA21=1,AC21=0),FALSE))

[BadlySpelledBuoy]

You're missing an AND in the second IF.
Try this:

Please Login or Register  to view this content.

It will leave the cell blank if none of the conditions are met.

BSB

[RW1992]

Hi AliGW,

Thanks for your help!

I've entered that formula but for some reason it's still only returning FALSE. The cells AA and AC have formulas in them too - will that affect it in any way?

Cells2.PNG Formula2.PNG

[AliGW]

It shouldn't do.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[RW1992]

Hi BSB,

Thanks for the help as well. However, this is now returning blank cells all the time. I'm not entirely sure what I've done wrong haha. The cells AA and AC have formulas in them too - but they're working and returning either 1 or 0 accordingly.

Ryan

[RW1992]

Hi Ali,

Have attached the tab of the worksheet I'm working on.

Thanks for your help!

Ryan

[BadlySpelledBuoy]

The values in column AA are stored as text rather than numbers and that is causing the problem.
Change that or change the formula to this in AA3 and copied down:

Please Login or Register  to view this content.

BSB

[samba_ravi]

in AA3 modify your formula as =IFERROR(IF(TODAY()-1=INT(U3),1,0),"TBC")
instead of =IFERROR(IF(TODAY()-1=INT(U3),"1","0"),"TBC") and copy towards down

[AliGW]

What are you expecting to see for this one?

=IF(OR(AND(M3=1,N3=1),AND(M3=2,P3=1),AND(M3=0,O3=1)),1,0)

It is correctly returning 1, but you can't see it because of your conditional formatting!

[RW1992]

What are you expecting to see for this one?

=IF(OR(AND(M3=1,N3=1),AND(M3=2,P3=1),AND(M3=0,O3=1)),1,0)

It is correctly returning 1, but you can't see it because of your conditional formatting!

If any of the AND sections are true, then return 1, if none, return 0. Then it's set to change to green if the cell contains a 1 and red if the cell contains a 0

[RW1992]

The values in column AA are stored as text rather than numbers and that is causing the problem.
Change that or change the formula to this in AA3 and copied down:

Please Login or Register  to view this content.

BSB

That worked perfectly. Thanks very much!!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[RW1992]

Thanks everyone for your help! Much appreciated!!

[AliGW]

You're welcome!