Boolean values are numbers sometimes?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
Windows
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/CCD
4FALSEC4: FALSE
5TRUEC5: TRUE
6TRUEC6: TRUE
7#DIV/0!C7: =AVERAGE(C4:C6)
80C8: =SUM(C4:C6)
92C9: =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
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
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
Joined
May 17, 2006
Messages
9,189
what about =COUNTIF(rng,TRUE)/COUNTA(rng)
or some variation of the idea. I've assumed cells contain either TRUE or FALSE
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,501
Office Version
2010
Platform
Windows
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
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
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.
 

Forum statistics

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

Some videos you may like

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...
Top