My Assistant
Custom Search

Sumproduct() and the Unary Minus (a brief overview) 
Jul 14 2007, 01:24 PM Post#1  
Remembered Posts: 5,055 Joined: 27March 03 From: Minneapolis, MN, USA  First, a note on the Unary Minus:
X = 0X and: x = 0(0x) So, 2 = 2 You are implicitly coercing Booleans, e.g., True = 1, False = 0, True = 1 and False = 0. And this approach to implicit coercion is fast: http://www.mcgimpsey.com/excel/formulae/doubleneg.htm Now, a note on Sumproduct(): Sumproduct() = Sum of the Products. Products as in multiplication. You're performing Matrixmath, and you can't multiply 1 * True, but you can multiply 1 * 1, or 1 * 0. So, we coerce. Here's two Matrices (or two arrays) you may start with: [True] [1] [False] [2] [True] [2] Once we coerce, we end up with: [1] [1] [0] [2] [1] [2] Now we Sum the Products of our Matrices, that is (in pseudo code): Sum(1*1, 0*2, 1*2) Thus the answer is 3. This is a simple Matrix (or group of Arrays)... It's all about the Matrix. Now, with the preceding in mind, let's look at two simplified examples, one which performs a conditional count with 2 conditions and one which performs a conditional sum with 2 conditions. For both examples, we'll use the following Spreadsheet layout: CODE A B C
1 Test1 Test2 1 2 Test1 Test2 2 3 Test1 Test1 3 4 Test1 Test1 4 5 Test1 Test2 5 6 Test2 Test2 6 7 Test2 Test2 7 8 Test1 Test2 8 9 Test2 Test1 9 10 Test1 Test2 10 Conditional Counting with SumProduct(): Let's say we want to Count Column A for Test1 and Column B for Test1 and return the count in the case that both conditions are True. We can use the following SumProduct() construct: CODE =SUMPRODUCT((A1:A10="Test1"),(B1:B10="Test1"))
Great you say, but what on Earth is this doing? Good question! The Range A1:A10 and B1:B10 are converted to two individual Arrays of Values. Because we're using an equality test, these arrays are populated with Booleans. The two arrays in question evaluate to the following: CODE {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}
And: {FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE} Now the trick is to mathematically operate on these arrays, to convert the Booleans to a Numeric Data types. To do this we apply the unary minus to the Array of Booleans in the following manner: ({array}) When we do this we end up with the following two arrays: CODE =SumProduct({1;1;1;1;1;0;0;1;0;1},{0;0;1;1;0;0;0;0;1;0})
Now, SumProduct() takes the two Arrays as two arguments and multiplies them by each other which results in the following Array: CODE =SumProduct({0;0;1;1;0;0;0;0;0;0})
Note that anything multiplied by 0 equals 0, so only in the event that both elements are True, coercing to 1, where 1 X 1 = 1, will evaluate to 1. At this point, SumProduct sums the final Array evaluation from the Arrays products and returns the desired of 2. Conditional Summing with SumProduct(): The logic in Conditional Summing with SumProduct() is the exact same, but we add another Array of the actual Values we want applied to our conditions. So, with our example, let's say we want to Sum the Values in Column C where Column A equals Test1 and Column B equals [/i]Test1[/i]. We could use the following Sumproduct() construct: CODE =SUMPRODUCT((A1:A10="Test1"),(B1:B10="Test1"),(C1:C10))
The logic is the same as in our first example, but we've added a 3rd Range, which creates a third Array of actual Values. Keeping in mind what the final evaluated Array of our counting example, we end up with the following two Arrays: CODE =SumProduct({0;0;1;1;0;0;0;0;0;0},{1;2;3;4;5;6;7;8;9;10})
SumProduct() again multiplies the two Array arguments and evaluates to the following: CODE =SumProduct({0;0;3;4;0;0;0;0;0;0})
And at this point SumProduct() sums the final evaluated Array from the Arrays products and returns our desired result of 7. SumProduct() is a robust and very powerful Worksheet Function, while these are two simplified examples, perhaps this helps to explain the madness to the method. If there are further questions, feel free to ask in our forums. 
Custom Search

Search Top LoFi  24th March 2018  05:25 AM 