Boolean values are numbers sometimes?

[JenniferMurphy]

It appears that Boolean values (TRUE, FALSE) can be used in calculations in some situations, but not others. In this table, Col D shows the formula in Col C.

R/C	C	D
4	FALSE	C4: FALSE
5	TRUE	C5: TRUE
6	TRUE	C6: TRUE
7	#DIV/0!	C7: =AVERAGE(C4:C6)
8	0	C8: =SUM(C4:C6)
9	2	C9: =C4+C5+C6

<tbody>
</tbody>

Why does the expression in C9 work, but the ones in C7 & C8 don't?

Is there a simple expression that will calculate the average of a variable column of Boolean values as if TRUE=1 and FALSE=0? I need the expression to specify a range (C4:C6) and not an explicit sum (C4+C5+C6).

 

[shg]

When Booleans are used in arithmetic expressions, TRUE is coerced to 1 and FALSE to 0. SUM, AVERAGE, and many other functions ignore text and Booleans.

 

[Fazza]

what about =COUNTIF(rng,TRUE)/COUNTA(rng)
or some variation of the idea. I've assumed cells contain either TRUE or FALSE

 

[Rick Rothstein]

AVERAGE, and many other functions ignore text and Booleans.

This appears to work when array-entered**...

=AVERAGE(0+C4:C6)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[shg]

This appears to work ...

It works for the reason previously stated, Rick:

When Booleans are used in arithmetic expressions, TRUE is coerced to 1 and FALSE to 0

What arrives at the function are numbers from the expression evaluator; the function never sees the Booleans.