Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Another SUMPRODUCT array anomaly
In an otherwise empty worksheet, enter 1 in G1 and I1 and a number (<0)
in I5. Now, in each of C1:C2 enter the formula =SUMPRODUCT((I1:DV1=1)*TRANSPOSE(G1:G118=G2),I5:DV 5) Why does the result of this formula depend upon the cell that it is entered in? How is the formula in C2 returning the value in I5 even though (I1:DV1=1)*TRANSPOSE(G1:G118=G2) should be an array of zeros? Jerry |
#2
|
|||
|
|||
Fid you array enter it? TRANSPOSE even inside SUMPRODUCT always need to be
array entered -- Regards, Peo Sjoblom "Jerry W. Lewis" wrote in message ... In an otherwise empty worksheet, enter 1 in G1 and I1 and a number (<0) in I5. Now, in each of C1:C2 enter the formula =SUMPRODUCT((I1:DV1=1)*TRANSPOSE(G1:G118=G2),I5:DV 5) Why does the result of this formula depend upon the cell that it is entered in? How is the formula in C2 returning the value in I5 even though (I1:DV1=1)*TRANSPOSE(G1:G118=G2) should be an array of zeros? Jerry |
#3
|
|||
|
|||
Hi Jerry,
Using Excel 2003. This is really weird. If I copy the formula to C3 and C4, I have your identical formula in four cells. With 1 in G1 and I1, 100 in I5, and all other cells blank (except c1:c4, of course)... If G2 is blank or 0, I get: C1: 0 C2: 100 C3: 100 C4: 100 If G2 is 1, I get: C1: 100 C2: 100 C3: 0 C4: 0 If G2 is 2 or text, I get: C1: 0 C2: 100 C3: 0 C4: 0 Ugh!!! Regards, Jay Petrulis |
#4
|
|||
|
|||
Hi Peo,
Nice catch. Array entry does return the correct and expected results. However, I do not understand why the array entry is required, in THIS instance. The item of concern is the first entry in the corresponding arrays. Thus, the transpose non-array entered should still calculate the first set, I would think. Other items in the arrays might be wrong without the array entry for TRANSPOSE, but not the first. Obviously, I could be off my rocker here. Regards, Jay Petrulis |
#5
|
|||
|
|||
No, and array entry does fix the problem. I second Jay's kudos about a
good catch! Any idea about what it is doing without array entry that the result depends on where the formula resides? Also, has anyone compiled a list of functions that require array entry inside SUMPRODUCT? Jerry Peo Sjoblom wrote: Did you array enter it? TRANSPOSE even inside SUMPRODUCT always need to be array entered |
#6
|
|||
|
|||
And possibly the more important follow-on question, can anyone think of
a SUMPRODUCT formula where there is a different result with array entry and the array entered result is the less desirable result? Jerry Jerry W. Lewis wrote: No, and array entry does fix the problem. I second Jay's kudos about a good catch! Any idea about what it is doing without array entry that the result depends on where the formula resides? Also, has anyone compiled a list of functions that require array entry inside SUMPRODUCT? Jerry Peo Sjoblom wrote: Did you array enter it? TRANSPOSE even inside SUMPRODUCT always need to be array entered |
#7
|
|||
|
|||
Jerry W. Lewis wrote...
.... Any idea about what it is doing without array entry that the result depends on where the formula resides? Implicit array indexing. Enter {1;2;3;4;5;6;7;8} in A2:A9, {1,10,100,1000} in B1:E1, select B2:E9, type the formula =$A2:$A9*B$1:E$1 and press [Ctrl]+[Enter] rather than [Ctrl]+[Shift]+[Enter]. You should get the same result as if you had entered B2:E9 as an array formula. Clear B2:E9, select C4:F11, type the same formula and press [Ctrl]+[Enter]. This has been in Excel for a LONG, LONG time, but as with too much else has never been properly documented. Also, has anyone compiled a list of functions that require array entry inside SUMPRODUCT? It's not just inside SUMPRODUCT. And we're talking phylum, class, order, family, genus and species when it comes to a full description of built-in function array semantics. There are three phyla: functions that always return arrays (FREQUENCY), functions that always return scalars (SUM) and functions that can return both (IF). Within the first phylum, there are two classes. Those that provide implicit array indexing, and those that don't. Given the setup above in A2:A9 and B1:E1, select C3:E5, type the formula =TRANSPOSE($A$2:$A$9) and press [Ctrl]+[Enter]. Now select C7:E9, type the formula =MMULT($C$3:$E$5,$C$3:$E$5) and press [Ctrl]+[Enter]. Then again, select C3:C5 again, change the formula to =TRANSPOSE({1;2;3;4;5;6;7;8}) and press [Ctrl]+[Enter]. Then change the formula to =TRANSPOSE($A$2:$A$9*10) So it seems implicit array indexing should be called implicit range indexing. Anyway, if you want to learn how Excel works, read the developer documentation and source code for Gnumeric. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing a range of an array in a SUMPRODUCT formula gives a #N/A error | Excel Discussion (Misc queries) | |||
How can I use a cell reference in Sumproduct array formula? | Excel Discussion (Misc queries) | |||
sumproduct help | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |