IF and AND function

[Splitz07]

Hi guys,

I know this may be a very basic problem for many advanced users of excel but I am just beginning to get into the thick of things for a future job and need help with some basic things.

I tried googling for the past hour and the HELP information within excel did not give me the exact answer I was looking for.

My problem is as follows:

Assuming A1=5, A2=5, A3=5, A4=5

1. I want to use the IF and AND functions together on range of cells with True or False for specific value

I have only been able to get a True value when using the formulas =IF(AND(A1,A2,A3,A4=5),"True","False") or =IF(A1=5,A2=5,A3=5,A4=5),"True,"False")

Is there anyway to condense the formula for a range of cells? I tried =IF(AND(A1:A4=5),"True","False") but instead turned up a #VALUE error.

2. Based on some reference notes I have from a course I will be taking in the future, they listed the formula in a manner such as =IF(AND(A1,A2,A3,A4)=5,"True","False")

Using their formula results in a FALSE no matter what the values of the cells are unless i put the '=5' condition within the parenthesis i.e: (A1,A2,A3,A4=5) and not (A1,A2,A3,A4)=5

Is this an error in the notes or is there something I do not understand?

Thanks in advance for the help!

[ratcat]

G'day Splitz and welcome to the forum,

Try this

=IF(AND(A1=5,A2=5,A3=5,A4=5),"True","False")

Or

=IF(SUM(A1:A5)=20,"True,"False")


and good luck on your future job !

[benishiryo]

welcome to the forum, my fellow Singaporean.

1. TRUE & FALSE are recognized words of the formula. so it means you can actually use it without the double quotes:
=IF(AND(A1=5,A2=5,A3=5,A4=5),True,False)

2. by using logical tests (i.e. A1=5), it will already return TRUE or FALSE. so do away with the IF:
=AND(A1=5,A2=5,A3=5,A4=5)

3. The formula you stated =IF(AND(A1,A2,A3,A4=5),"True","False") does not work. try changing any of them to any other values than 0 & it'll still show as TRUE.

4. not sure where your notes come from. doesn't seem right. the only way you can do it by testing a range of cells with a logical test is:
=A1:A4=5
this is called an array though. it's something more advanced. the cell actually produces 4 results but are unable to show it. it's actually to do something more complex. you can try:
=AND(A1:A4=5)
and instead of ENTER, press CTRL + SHIFT + ENTER

for your case, you can avoid doing the above. array formulas slow down the files. you can simply use:
=COUNTIF(A1:A4,5)=4

that is to count how many cells in A1:A4 is 5. i then do a logical test to see if the total is 4 counts. hope that helps

[Splitz07]

Hi Benishiryo,

Thanks for the reply! I'm still somewhat confused with certain stuff..

welcome to the forum, my fellow Singaporean.

1. TRUE & FALSE are recognized words of the formula. so it means you can actually use it without the double quotes:
=IF(AND(A1=5,A2=5,A3=5,A4=5),True,False

Sorry about the True/False confusion. I actually used "Yes" and "No" when playing around but I thought that True or False would be more widely 'understandable'.

Am I safe to assume that anything other than True or False will require the double quotes?


2. by using logical tests (i.e. A1=5), it will already return TRUE or FALSE. so do away with the IF:
=AND(A1=5,A2=5,A3=5,A4=5)

3. The formula you stated =IF(AND(A1,A2,A3,A4=5),"True","False") does not work. try changing any of them to any other values than 0 & it'll still show as TRUE.

Just tested it out again. You are correct. Is this just a bad formula?

Edit: I tested a bit more.. it seems that changing any values from A1:A3 will result in a True. But if I change only A4 then the result is a false. I think it must be something with the way the formula is written. This further confuses me. haha


4. not sure where your notes come from. doesn't seem right. the only way you can do it by testing a range of cells with a logical test is:
=A1:A4=5
this is called an array though. it's something more advanced. the cell actually produces 4 results but are unable to show it. it's actually to do something more complex. you can try:
=AND(A1:A4=5)
and instead of ENTER, press CTRL + SHIFT + ENTER

Tested it out. It works. Also tested it with the in combination with IF as per my initial attempts and it works when i end the formula with the array thing you mentioned {=IF(AND(A1:A4=5),TRUE,FALSE)}

Now you got me wondering what an array is. More for me to read up on. =D


for your case, you can avoid doing the above. array formulas slow down the files. you can simply use:
=COUNTIF(A1:A4,5)=4

that is to count how many cells in A1:A4 is 5. i then do a logical test to see if the total is 4 counts.

What is the logical test to see if the total is 4 counts? does COUNTIF already tell you that? I'm confused.

hope that helps

[Splitz07]

Thanks ratcat!

Your first statement is what I was actually trying to avoid.. its really long winded. Beni managed to shed some light for me.

Your 2nd statement is not what I was trying to do.

Neverthesless, Thanks for the help and well wishes!

G'day Splitz and welcome to the forum,

Try this

=IF(AND(A1=5,A2=5,A3=5,A4=5),"True","False")

Or

=IF(SUM(A1:A5)=20,"True,"False")


and good luck on your future job !

[benishiryo]

Am I safe to assume that anything other than True or False will require the double quotes?

any text besides TRUE/FALSE will require double quotes

Just tested it out again. You are correct. Is this just a bad formula?

Edit: I tested a bit more.. it seems that changing any values from A1:A3 will result in a True. But if I change only A4 then the result is a false. I think it must be something with the way the formula is written. This further confuses me. haha

if you type in the AND formula, you'll see a screen tip showing:
logical1,[logical2],...
those are the logical test you're supposed to do. if you simply put in the cell reference, any text or number other than 0 will return TRUE. so you didn't actually do any logical test for A1:A3 but only for A4. change any of A1:A3 to 0 & you'll get FALSE. if it confuses you, don't bother about the rationale. just don't put any logical test as such. logical tests for you should always include an operator (=, <, <=, >, >=, <>)

What is the logical test to see if the total is 4 counts? does COUNTIF already tell you that? I'm confused.

=COUNTIF(A1:A4,5)=4
the red portion is the logical test. COUNTIF will give me 4 counts ONLY when A1:A4 is all filled in with 5.

[benishiryo]

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

[Splitz07]

Thanks Beni.

I understand how the commas denote each logical test.

I guess the array thing {} is what shortens the formula to the range of values instead of typing out each cell 1 by 1. Sounds complex but if its the rule then I'll just have to go with it at the moment. I'll probably pick up more as I go along.

Thanks for your help!