MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Attn: Juan Pablo, Aladin... These should work, but don't...


Posted by Mark W. on January 07, 2002 8:12 AM

Here's a couple of array formulas that I devised
to solve Kevin Smith's inquiry ...

>I enter several rows of data. My need is to compute an average of the
>last seven entries in a column. However I want to ignore any zeroes.
>Example if mu last 10 entries were 1,2,3,4,5,0,0,6,7,8. I would want the
>average of 2,3,4,5,6,7,8. Any help would be appreciated.


When A1:A10 contains {1;2;3;4;5;0;0;6;7;8} both...

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

...return 8! When I decompose these formulations
both INDIRECT(...) and OFFSET(...) return {8,7,6,5,4,3,2},
but AVERAGE returns 8. Strange! Can you confirm
these results? I'm always reluctant to cry, "Bug",
but I'm beginning to wonder.


Posted by Aladin Akyurek on January 07, 2002 8:49 AM

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

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

Posted by Mark W. on January 07, 2002 9:42 AM

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.

Posted by Aladin Akyurek on January 07, 2002 11:08 AM

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

Posted by Aladin Akyurek on on January 20, 2002 at 13:53:13

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