Freddy: Aren't there any lights in this place?[...]by The FrankensTeam This short post is just to share a great mathematical trick associated with an excel formula.
There was a very interesting discussion on Linkedin started by Brian about a special sumproduct-like case, where Peter Bartholomew posted a very clever and beautiful mathematical solution. Here we would like to share the trick with you in a bit more generalized way.The challenge is to calculate the sum of the product of matrix elements by rows - using one formula in one cell only, without help columns. On the picture above we used a help column with PRODUCT formula. For the one-cell formula solutions we will separate two cases: 1. if your data is in a sheet range 2. if your data is an array of numbers In case of range data, the solution is based on the special feature of SUBTOTAL and OFFSET formula described in an older post: Array of ranges using OFFSET formula The formula for the above case could be as simple as: `=SUM(SUBTOTAL(6,OFFSET(A1:C1,{0;1;2;3},)))` To be array-entered, the result will be 196. The limitation of this solution is that OFFSET could only be used with sheet ranges. So if you need to do any other change on your data, it could not be used. The idea to solve the array-case comes from secondary school mathematics when we learned the fact that logarithm of a product is the sum of the logarithms of the factors (http://en.wikipedia.org/wiki/Logarithm) E voilá, the formula: `=SUM(EXP(MMULT(LN(A1:C4),{1;1;1})))` So we summarize the logarithms by rows (MMULT will do it) then use EXP to reverse the logarithm and SUM to produce the total result. With this formula you can use array of numbers: `=SUM(EXP(MMULT(LN({1,2,4;3,5,6;2,1,7;3,4,7}),{1;1;1})))` The real challengeSo you can do the data-mainpulation steps within this formula. Let's see how it was in the original question: There is only one vector with numbers between 0 and 1 in range a1:a10. The task was to calculate the sum of the following pattern: `=SUM(` `(1-A1),` `(1-A1)*(1-A2),` `(1-A1)*(1-A2)*(1-A3),` `(1-A1)*(1-A2)*(1-A3)*(1-A4),` `(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5),` `(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5)*(1-A6),` `(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5)*(1-A6)*(1-A7),` `(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5)*(1-A6)*(1-A7)*(1-A8),` `(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5)*(1-A6)*(1-A7)*(1-A8)*(1-A9),` `(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5)*(1-A6)*(1-A7)*(1-A8)*(1-A9)*(1-A10)` `)` To use the first, range-based solution, we need a help column: In column B we have to calculate 1-values, then the above formula could be used based on the numbers of column B: `=SUM(SUBTOTAL(6,OFFSET(B1,,,ROW(A1:A10)-MIN(ROW(A1:A10))+1)))` (array-enter)Using the logarithm-based formula, no help column is needed, the final solution could be written as: ` ` `=SUM(EXP(MMULT(1-(ROW(A1:A10)<TRANSPOSE(ROW(A1:A10))),LN(1-A1:A10))))` (array-enter)You can see the data manipulation step (1-A1:A10) is within the formula, and it works like a charm! This is the base idea - now you can take a look at the original discussion where you can find the original challenge with more details and explanation. Finally, let's finish with a quote told by Peter at the end of the discussion: "How do adders multiply? They do it by logs." |