AND() Function

[ErikG1165]

Had something come up today when working on solution for someone on a Facebook post. They had test questions, (n is the row number) if all questions were answered Column Hn would turn True (from blank) and if no questions were skipped column Jn would turn true (from Blank) -- i don't know why they didn't have False, but its not my worksheet. They wanted the row to be conditional formatted if BOTH Hn & Jn were TRUE. So it looks like you need the AND function, but what I found out is =AND($H2,$J2) in conditional formatting didn't work
Seems =AND(Blank,Blank) --> #Value, =AND(True,0) --> TRUE, =AND(0,True)--> True... Seemed odd that 0 & True are TRUE (=AND(--$H2,--$J2) worked cause its (0,0) or (1,1) )

Some InSite as to why 0 & True = TRUE?????

 

[6StringJazzer]

What is the difference between "all questions were answered" and "no questions were skipped"? They seem to mean the same thing to me.

Anyway,

=AND(0,TRUE)

is FALSE because 0 evaluates as FALSE.

=AND("",TRUE)

is TRUE because any string evaluates as TRUE, even the null string.

=AND(A1,TRUE)

evaluates to TRUE if A1 is empty.

So maybe you need:

=AND($H2<>"",$H2,$J2<>"",$J2)

This requires the cells to be non-blank and also evaluate to TRUE.

 

[ErikG1165]

What is the difference between "all questions were answered" and "no questions were skipped"? They seem to mean the same thing to me.

Anyway,

=AND(0,TRUE)

is FALSE because 0 evaluates as FALSE.

=AND("",TRUE)

is TRUE because any string evaluates as TRUE, even the null string.

=AND(A1,TRUE)

evaluates to TRUE if A1 is empty.

So maybe you need:

=AND($H2<>"",$H2,$J2<>"",$J2)

This requires the cells to be non-blank and also evaluate to TRUE.

Thanks for the info... yes, it was AND(True,"") ... when pressing F9 it would show True, 0. Just didn't think that a TRUE and "" would be true.
the first part had nothing really to do with MY question, just background. Also it was missing a word It was for CORRECT Answers vs SKIPPED answers.... guess it was to check for Questions answered WRONG vs NO answers (Wasn't my spreadsheet, just helping with conditional formatting problem)

My question was for the AND() function as to why "" evaluates to True.

 

[6StringJazzer]

This seems to be the case only when the logical expression appears in an AND function, and not other logical expressions. See sample sheet below.

As to the "why", I don't know. Maybe only Microsoft knows. Your title is pretty terse and doesn't really explain what your question is, but it may be too late to edit it to attract more interest.

I have posted a more narrowly worded question elsewhere and will post back if I get meaningful responses.

$scratch.xlsm
	A	B	C
1	FALSE	0	FALSE
2	TRUE	1	TRUE
3	TRUE	2	TRUE
4	#VALUE!	a	TRUE
5	#VALUE!	b	TRUE
6	#VALUE!	c	TRUE
7	#VALUE!	c	TRUE
8	FALSE		TRUE
Sheet8

Cell Formulas
Range	Formula
A1:A8	A1	=IF(B1,TRUE,FALSE)
C1:C8	C1	=IF(AND(TRUE,B1),TRUE,FALSE)

 

[6StringJazzer]

If you provide a string value where a boolean value is expected, it will be treated as NULL. This is not really defined if you read any Excel user documentation but think of as meaning "there is nothing here."

If you include it as one of multiple arguments to an AND function, it is ignored and the AND result is calculated with the remaining arguments.

If you try to use it as an IF condition, IF cannot evaluate the condition so returns a #VALUE error.

I have still not worked out what is going on in row 8 of my example.

 

[RoryA]

AND ignores text or empty cells, so AND(TRUE,B1) is the same as AND(TRUE)

IF has to evaluate the first argument as an expression. Since a simple reference to an empty cell evaluates to 0, you have IF(0,TRUE,FALSE) and hence the result is FALSE.