INCORRECT RESULT FROM NESTED INDEX ?

[Tapani]

Proposition:
Nested index-function may give false results in excel 2007.

Proof:
Assume
1. Range A1:A5 includes numbers 6, 7, 8, 9, 10 and has name A
2. Range B1:B2 includes numbers 2, 4 and has name B
3. Range D12 includes formula =INDEX(A,B) and looks as numbers 7, 9 and
has name D
4. Cells E1:E2 include the formula=(D=INDEX(A;B)) and look as true, true
5. Cell F1 includes formula =SUM(D) and looks as 16 as expected
6. Cell G1 includes formula =SUM(INDEX(A,B)) and looks as 7, surprisingly!

So item 4 verifies D=INDEX(A;B) and therefore following the rules of
traditional logic we get 16=sum(D)=SUM(INDEX(A;B))=7 i.e. 16=7.

Proof completed.

The problem seems to be the same if I replace SUM with any other function.

I have preferred to like to skip unnecessary interim results in excel to
keep the limit the size of files used (the size of file where I realized the
error above is 40 MB) and therefore this error in excel is most unpleasant.
Does anyone know how to pass this problem?

Tapani

 

[Charles Williams]

I get the same results in Excel 2003.

The reason you get 7 in Cell G1 is that Excel is doing Implicit Intersection
of Named Range B with Row1.

If you copy the formula down 2 rows then in Row2 you get 9 (Implicit
Intersection with Row2), and in row3 you get #Value because there is no
Implicit Intersection of Row3 with B1:B2.

It is possible to bypass using for instance
{=SUM(N(OFFSET(A,B-1,0,1,1)))}

entered using Control-Shift-Enter (without the {}) as an array formula, but
calculation performance may suffer.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Herbert Seidenberg]

Excel 2007
Tables, Sumproduct
http://www.mediafire.com/file/uzi2vjzjq4y/04_24_09.xlsx