How are boolean values treated in Excel?

Question

I decided to learn how to work with the function SumProduct(), so I created this very basic Excel sheet:

A    B
1    1
2    2

I calculated =SumProduct(A2:A3,B2:B3) and the result was 5, which was as expected.

Then I got the idea to replace B2 by =(1=1), which obviously is TRUE, which is confirmed by what I see.

However, the =SumProduct() result changed into 4, apparently booleans are treated as zero in Excel.
So, I decided to write another formula: =A2*B2+A3*B3 (which is exactly the same as the SumProduct()), but instead of getting the expected value 4, the value remained 5.

So, apparently, regular mathematical operators (*, +, ...) treat boolean values as "to be expected" (TRUE becomes 1 and FALSE becomes 0), but for some more elaborated functions (like SumProduct()) this seems not to be the case.

What's the explanation behind this? I would like to understand what's happening in order to avoid unpleasant surprises. I think I'm working with Excel-365 (how can I be sure?).

user_436560 · Answer

Refer to this document: https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/

which explains that you need to prepend -- before your value in order to convert it into the 1 or 0 you have expected. Thus, the function does not support boolean values, as you have correctly observed. You can also use an IF function whose first parameter is the logical expression that you have, the second parameter being 1 and the third being 0. As about the reason for the lack of support for implicit conversion of logical data into arithmetic data at this function, I think we will not be able to factually determine its reason at an online forum, but that is rather a question that you could direct to the authors of the function.

How are boolean values treated in Excel?

1 Answers