Adding Booleans

mortgageman · Oct 18, 2006

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?

acw · Oct 18, 2006

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 · Oct 18, 2006

<table border=1><tr><td align=left valign=center>Gönderen XL Ver.: Office 2003 / OS Ver.: Windows XP </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>+</td><td bgcolor=d4d0c8 width=60>E</td><td bgcolor=d4d0c8 width=60>F</td><td bgcolor=d4d0c8 width=60>G</td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>39</td><td bgcolor=white nowrap=true align=right>1</td><td bgcolor=white nowrap=true align=center><ACRONYM title='=ABS(INT(E39:E44))'>1 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=(F39:F44=1)'>-1 (ƒx)</ACRONYM></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>40</td><td bgcolor=white nowrap=true align=right>2</td><td bgcolor=white nowrap=true align=center><ACRONYM title='=ABS(INT(E39:E44))'>2 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=(F39:F44=1)'>0 (ƒx)</ACRONYM></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>41</td><td bgcolor=white nowrap=true align=right>3</td><td bgcolor=white nowrap=true align=center><ACRONYM title='=ABS(INT(E39:E44))'>3 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=(F39:F44=1)'>0 (ƒx)</ACRONYM></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>42</td><td bgcolor=white nowrap=true align=right>1</td><td bgcolor=white nowrap=true align=center><ACRONYM title='=ABS(INT(E39:E44))'>1 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=(F39:F44=1)'>-1 (ƒx)</ACRONYM></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>43</td><td bgcolor=white nowrap=true align=center><ACRONYM title='-1,4'>-1 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=ABS(INT(E39:E44))'>2 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=(F39:F44=1)'>0 (ƒx)</ACRONYM></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>44</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>45</td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=SUM(G39:G44)'>0 (ƒx)</ACRONYM></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>46</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>47</td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=1=1'>-1,00 (ƒx)</ACRONYM></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>48</td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=1=2'>0,00 (ƒx)</ACRONYM></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>49</td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=SUM(G47:G48)'>0,00 (ƒx)</ACRONYM></td></tr></table></td></tr><tr><td bgcolor=f0f8ff align=center>XLtoHTML v1.1 / ExcelTürkiye - 2006©</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 · Oct 18, 2006

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 · Oct 18, 2006

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 · Oct 19, 2006

Ok - yes I see that now - a nice clear concise explanation goes a long way!

Thanks mucho.

tusharm · Oct 19, 2006

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 · Oct 19, 2006

<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)

Click to expand...

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

Adding Booleans

mortgageman

Well-known Member

Excel Facts

acw

MrExcel MVP

Erdinç E. Karaçam

Board Regular

mortgageman

Well-known Member

acw

MrExcel MVP

mortgageman

Well-known Member

tusharm

MrExcel MVP

mortgageman

Well-known Member

Similar threads

Forum statistics

Share this page

Adding Booleans

Well-known Member

Excel Facts

MrExcel MVP

Board Regular

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock