Mark --

Here are the modifications that you need:

{=AVERAGE(AVERAGE(INDIRECT("A"&LARGE(ROW(A1:A10)*(A1:A10<>0),{1,2,3,4,5,6,7}))))}

{=AVERAGE(AVERAGE(OFFSET(A10,-ROW(A10)+LARGE(ROW(A1:A10)*(A1:A10<>0),{1,2,3,4,5,6,7}),)))}

The extra AVERAGE forces EXCEL to do a second round of evaluation.

I had also constructed array formulas where I needed an extra SUM. I've never been able to give a satisfactory explanation to people I gave those formulas why the extra SUM was needed.

I'll be heading home in a minute. Perhaps we can continue to look at this particular issue a bit further.

Aladin

============

I haven't encountered this phenomenon/workaround before now...

Needless to say, the "fix" is not intuitive. How

did you discover it? Have you found any references

to this situation in Microsoft's Knowledge Base?

I wonder if the folks at Microsoft are even aware

of this quirk.

Re: I haven't encountered this phenomenon/workaround before now...

> Needless to say, the "fix" is not intuitive.

Very much so.

> How did you discover it?

Questions like the sum or average of last N values were the occasion. I fetched up a few from my hard disk. Similar thus to that of Kevin's:

{=SUM(SUM(INDIRECT(ADDRESS(LARGE(ISNUMBER(INDIRECT(C5))*ROW(INDIRECT(C5)),ROW(INDIRECT("1:"&C4))),COLUMN(INDIRECT(C5))))))}

and

{=SUM(SUM(INDIRECT(ADDRESS(LARGE(ISNUMBER(INDIRECT(C5))*ROW(INDIRECT(C5)),ROW(INDIRECT("1:"&C4))),COLUMN(INDIRECT(C5)))))/C4)}

where

C2: N [ N last values ]

C3: =MATCH(9.99999999999999E+307,B:B)

C4: =MIN(C2:C3)

C5: =ADDRESS(ROW(B2),2)&":"&ADDRESS(C3,2)

to be applied to samples like

{"WEEK","SCORE";1,3;2,4;3,2;4,7;5,2;6,6;7,8;8,4;9,5}

which is A1:B10.

Another:

=IF(AND(LastNvalues,COUNT(INDIRECT(B10))>=LastNvalues,LEN(C10)>=1),SUM(SUM(INDIRECT(ADDRESS(ROW(INDIRECT(B10)),LARGE(ISNUMBER(INDIRECT(B10))*COLUMN(INDIRECT(B10)),ROW(INDIRECT("1:"&LastNvalues))))))/LastNvalues),"")

where B10 computes the address a dynamically & C10 is supposed to ave a name in it. This formula ignores (as requested) empty cells.

How I discoverd the fix? Using F9, you get e.g.,

AVERAGE({1,2,3}) or SUM({1,2,3})

Dismayed, I applied the logic of (and also a bit LISPish thinking)

=SUM(9) ==> 9

to

=SUM(SUM({1,2,3}))

and bingo. Not very illimunating, is it?

> Have you found any references to this situation in Microsoft's Knowledge Base? I wonder if the folks at Microsoft are even aware of this quirk.

Didn't even try. Not good at searching thru that KB as you are, I'm afraid.

Not sure but this shows up in array formulas with address constructions.

Aladin

Deferencing or Extra Round of Evaluation

We have been discussing

[1] {=AVERAGE(INDIRECT("A"&LARGE(ROW(A1:A10)*(A1:A10<>0),{1,2,3,4,5,6,7})))}

[2] {=AVERAGE(OFFSET(A10,-ROW(A10)+LARGE(ROW(A1:A10)*(A1:A10<>0),{1,2,3,4,5,6,7}),))}

would both return 8, applied to

{1;2;3;4;5;0;0;6;7;8}

instead of 5 which is the average.

Wrapping each formula within an additional AVERAGE,

[3] {=AVERAGE(AVERAGE(INDIRECT("A"&LARGE(ROW(A1:A10)*(A1:A10<>0),{1,2,3,4,5,6,7}))))}

[4] {=AVERAGE(AVERAGE(OFFSET(A10,-ROW(A10)+LARGE(ROW(A1:A10)*(A1:A10<>0),{1,2,3,4,5,6,7}),)))}

produce the desired average.

However, so do the following two pairs:

[5] {=AVERAGE(N(INDIRECT("A"&LARGE(ROW(A1:A10)*(A1:A10<>0),{1,2,3,4,5,6,7}))))}

[6] [=AVERAGE(N(OFFSET(A10,-ROW(A10)+LARGE(ROW(A1:A10)*(A1:A10<>0),{1,2,3,4,5,6,7}),)))]

and

[7] {=AVERAGE(TRANSPOSE(INDIRECT("A"&LARGE(ROW(A1:A10)*(A1:A10<>0),{1,2,3,4,5,6,7}))))}

[8] {=AVERAGE(TRANSPOSE(OFFSET(A10,-ROW(A10)+LARGE(ROW(A1:A10)*(A1:A10<>0),{1,2,3,4,5,6,7}),)))}

When one applies F9 on the INDIRECT or OFFSET bit, the following constant array is shown:

[9] {8,7,6,5,4,3,2} [*]

This array indexes the pos of the values in the sample. N, TRANSPOSE, or additional AVERAGE is said "to deference" the reference of each of the numbers/pos indicators in the array [9], or, as I've put, evaluate these pos indicators to get the values they reference.

What do you think?

Aladin