Sum Array Index Bug

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My worksheet has values 1:4 in cells A1:A4, respectively.

The formula SUMPRODUCT(OFFSET(A1,0,0,3,1),OFFSET(A1,1,0,3,1)) gives the
correct result, 20.

The formula SUMPRODUCT(INDEX(A1:A5,{2;3;4},1),INDEX(A1:A5,{1;2;3},1)),
entered in a single cell, gives the incorrect result, 2.

The formula SUMPRODUCT(INDEX(A1:A5,{2;3;4},1),INDEX(A1:A5,{1;2;3},1)),
entered as an array formula in a range of at least two cells, gives the
correct result, 20, in each cell.

My suggestion is to fix this bug.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...f10f65d&dg=microsoft.public.excel.crashesgpfs
 
Interesting. The issue appears to be with the INDEX function, that returns 3
values when array entered in a 3+ row selection, but which returns only a
single value when array entered as the argument of another function, such as
=COUNT(INDEX(A1:A5,{2;3;4},1))
My recollection is that other functions have also been discussed in the
newsgroups with this kind of inconsistent array behavior.

The real surprise here is that you get a result of 20 using the INDEX
function provided that more than one cell is selected when you array enter
the formula. If you array enter the preceeding COUNT(INDEX()) formula, you
still get a result of 1 in each cell even if more than one cell s selected.

Your suggestion might get more votes and therefore carrry more weight with
MS if you had posted it in an appropriate group such as "Worksheet Functions"
or "General Questions". "Application Errors" does not refer to Excel not
calculating properly, but rather to Excel crashing (which does not happen
here). Since Excel rarely crashes, this group is not widely read, which will
limit the number of votes for your suggestion.

Jerry
 
Back
Top