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.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]FALSE[/TD]
[TD]C4: FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]TRUE[/TD]
[TD]C5: TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]TRUE[/TD]
[TD]C6: TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]#DIV/0![/TD]
[TD]C7: =AVERAGE(C4:C6)[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]0[/TD]
[TD]C8: =SUM(C4:C6)[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]2[/TD]
[TD]C9: =C4+C5+C6[/TD]
[/TR]
</tbody>[/TABLE]

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.