#### mortgageman

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

#### mortgageman

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

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
Ok - yes I see that now - a nice clear concise explanation goes a long way!

Thanks mucho.

#### tusharm

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

Replies
6
Views
117
Replies
1
Views
179
Replies
1
Views
383
Replies
3
Views
188
Replies
1
Views
248

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.

### Which adblocker are you using?

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

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