Quick Explanation

[T De Villiers]

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))

This array formula averages the 10th largest values in the Range
A1:A60,
I understand it:

so its taking the average value of

LARGE(A1:A60,1), LARGE(A1:A60,2), LARGE(A1:A60,3)..........

But how does ROW(INDIRECT("1:10") return the array 1,2,3,....


Thanks

 

[Bob Phillips]

It just does.

If you type =ROW(1:10) in a cell and use F9 to evaluate it, you will see
{1;2;3;...;10}. INDIRECT(ROW("1:10")) is doing the same thing, just passing
a string to INDIRECT to stop Excel from updating it when you move the
formula.

=ROW(A1) returns an array, even a single element array, always.

--
HTH

Bob Phillips

"T De Villiers" <[email protected]>
wrote in message
news:[email protected]...

 

[Biff]

Hi!

You don't need the Indirect:

=AVERAGE(LARGE(A1:A60,ROW($1:$10)))

Making the ROW argument absolute does the same thing as Indirect.

Biff

"T De Villiers" <[email protected]>
wrote in message
news:[email protected]...

 

[Dave Peterson]

What happens if you delete rows 2:8 or insert more rows above row 11?

(Although, with the data in A1:A60, I'm not sure if the user would do that.)

 

[Biff]

That's a good point!

But then, why isn't *EVERY* formula written for that possibility?

At some point robustness = overkill!

Biff

 

[Dave Peterson]

Oh, but lots of people use this syntax:
INDIRECT(ROW())

some include warnings when they don't.