Adding Booleans

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
I comfused and confused!

In Cell G47 I have =1=1, which displays as TRUE. In Cell G48 I have =1=2 which displays as FALSE. In cell G49 I have =sum(g47:g48) which results in 1 AS EXPECTED.

Now In cells e39 through e44 I have: 1,2,3,1.1,-1,4

In cells f39 through f44 I have the array formula {=abs(int(e39:e44))} which results in 1,2,3,1,1,4

In cells g39 through g44 I have the array formula {=(f39:f44=1)} which results in TRUE,FALSE,FALSE,TRUE,TRUE,FALSE

In cell g45 I have sum(g39:g44) which results in 0 NOT AS EXPECTED.

Why does the sum of an array of TRUE's and FALSE's sum differently than a non array?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Gene

Replicating above, I get 0 in G49 which is what I would have expected, not a 1 as you have. What formatting etc have you done to get the 1?

Basically, while a TRUE / FALSE is viewed as a 1 / 0, it has to be convered from a boolean to a number to perform mathematical calculation. This is why there is a -- in a lot of the SUMPRODUCT functions around.

Also, I believe that FALSE is 0, while TRUE is non zero. It is just converted to 1. This is why you can do things like

=if(a1,"Non Zero","Zero")

If you have a 0 (or blank) in A1, the result is Zero, otherwise it will be Non Zero.

I remember seeing some esoteric commentry about there being 3 values (True, False and Null [I think]). However, I don't recall the detail....


Tony
 

Erdinç E. Karaçam

Board Regular
Joined
Sep 23, 2006
Messages
202
<table border=1><tr><td align=left valign=center><font size=1 color=red face=verdana>Gönderen XL Ver.:</font><font size=1 face=verdana> Office 2003 / </font><font size=1 color=red face=verdana>OS Ver.:</font><font size=1 face=verdana> Windows XP </font></td></tr><tr valign=top><td><table border=0 bgcolor=d4d0c8 cellspacing=1 cellpadding=1 align=center><tr align=center valign=center bgcolor=white><td bgcolor=d4d0c8 align=center><font color=black size=2>+</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>E</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>F</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>G</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>39</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>1</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=ABS(INT(E39:E44))'>1 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=(F39:F44=1)'>-1 (ƒx)</ACRONYM></font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>40</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>2</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=ABS(INT(E39:E44))'>2 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=(F39:F44=1)'>0 (ƒx)</ACRONYM></font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>41</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>3</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=ABS(INT(E39:E44))'>3 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=(F39:F44=1)'>0 (ƒx)</ACRONYM></font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>42</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>1</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=ABS(INT(E39:E44))'>1 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=(F39:F44=1)'>-1 (ƒx)</ACRONYM></font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>43</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='-1,4'>-1 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=ABS(INT(E39:E44))'>2 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=(F39:F44=1)'>0 (ƒx)</ACRONYM></font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>44</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>45</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=SUM(G39:G44)'>0 (ƒx)</ACRONYM></font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>46</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>47</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=1=1'>-1,00 (ƒx)</ACRONYM></font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>48</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=1=2'>0,00 (ƒx)</ACRONYM></font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>49</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=SUM(G47:G48)'>0,00 (ƒx)</ACRONYM></font></td></tr></table></td></tr><tr><td bgcolor=f0f8ff align=center><font color=black size=1 face=verdana>XLtoHTML v1.1 / ExcelTürkiye - 2006©</font></td></tr></table>

Hi,

I propared step by step your message but, both of them didn't expected 1 or different. Just result: 0

And,

Your question is really interestring. Now i wondering about the answer...
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Tony - I actually had (sorry about that) =g47+g48 which gets me 1. You are right, to my surprise, =sum(g47:g48) does get 0. (I'm surprised about the result - not that you are right). Shouldn't these two give the same answer?

Also,
Why then does
=SUMPRODUCT(--ABS(INT(E39:E44))=1)

still result in a 0? I have included the --

(I suspect if I could understand the above questions - I would know a lot more about Excel then I do now. I use -- (when I use it) more as an idiom then as something I really understand)
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

Gene

More of an order of calculation / conversion issue.

Try

=SUMPRODUCT(--(INT(ABS(E39:E44))=1))

What you have to do is convert the boolean result to a number.

First you have to take the ABS of the number, then get the integer component, then test that against 1. It is this final test that has to be converted to a number.


Tony
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Ok - yes I see that now - a nice clear concise explanation goes a long way!

Thanks mucho.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
These are among the reasons why I rail against "funny" formulas that (a) mask intermediate results and (b) rely on various rules XL has about when and what data coercions it will carry out.

You've already found out that -- with a pair of parenthesis and w/o apply the double negation operations to totally different entities yielding totally different results.

But, you still don't know why the + operator and the SUM() function return different results, do you?

XL knows the + operator requires numbers and it will convert each operand to a number if it can. So, a boolean operand will be converted to a 0 or a 1.

The SUM function, on the other hand, is *documented* as ignoring anything that is not a number. So, a boolean argument will be ignored.

And, hence, the 2 seemingly inconsistent results.

Tony - I actually had (sorry about that) =g47+g48 which gets me 1. You are right, to my surprise, =sum(g47:g48) does get 0. (I'm surprised about the result - not that you are right). Shouldn't these two give the same answer?

Also,
Why then does
=SUMPRODUCT(--ABS(INT(E39:E44))=1)

still result in a 0? I have included the --

(I suspect if I could understand the above questions - I would know a lot more about Excel then I do now. I use -- (when I use it) more as an idiom then as something I really understand)
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
<snip>
But, you still don't know why the + operator and the SUM() function return different results, do you?

XL knows the + operator requires numbers and it will convert each operand to a number if it can. So, a boolean operand will be converted to a 0 or a 1.

The SUM function, on the other hand, is *documented* as ignoring anything that is not a number. So, a boolean argument will be ignored.

And, hence, the 2 seemingly inconsistent results.

<snip>
Tony - I actually had (sorry about that) =g47+g48 which gets me 1. You are right, to my surprise, =sum(g47:g48) does get 0. (I'm surprised about the result - not that you are right). Shouldn't these two give the same answer?

Also,
Why then does
=SUMPRODUCT(--ABS(INT(E39:E44))=1)

still result in a 0? I have included the --

(I suspect if I could understand the above questions - I would know a lot more about Excel then I do now. I use -- (when I use it) more as an idiom then as something I really understand)

That's interesting. So even though the + operator *requires* numbers, it is ... what - intelligent, adaptable, loose - enough to accept a different data type as long as ... what - there is a "clear" isomorphsim between the two data types? I'm not sure what type of behavior I would want: the current intelligent, adaptable, loose behavior, or a strict error message informing me that my data type is incorrect.

Perhaps the ideal is for this "undocumented" type of isomorphic behavior to be documented as an actual feature.

Gene, "The Mortgage Man", Klein
 

Forum statistics

Threads
1,136,262
Messages
5,674,709
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top