# Boolean values are numbers sometimes?

#### JenniferMurphy

##### Well-known Member
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

##### MrExcel MVP
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

##### MrExcel MVP
or some variation of the idea. I've assumed cells contain either TRUE or FALSE

#### Rick Rothstein

##### MrExcel MVP
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

##### MrExcel MVP
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.

1,081,575
Messages
5,359,715
Members
400,545
Latest member
Damntheman30

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...