=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


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=469109

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"
wrote in message
news:T.De.Villiers.1vnjqb_1127210724.3351@excelfor um-nospam.com...

=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


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=469109

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"
wrote in message
news:T.De.Villiers.1vnjqb_1127210724.3351@excelfor um-nospam.com...

=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


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=469109

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 wrote:

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"
wrote in message
news:T.De.Villiers.1vnjqb_1127210724.3351@excelfor um-nospam.com...

=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


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=469109


--

Dave Peterson

That's a good point!

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

At some point robustness = overkill!

Biff

"Dave Peterson" wrote in message
...
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 wrote:

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"

wrote in message
news:T.De.Villiers.1vnjqb_1127210724.3351@excelfor um-nospam.com...

=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


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread:
http://www.excelforum.com/showthread...hreadid=469109


--

Dave Peterson

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

some include warnings when they don't.

Biff wrote:

That's a good point!

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

At some point robustness = overkill!

Biff

"Dave Peterson" wrote in message
...
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 wrote:

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"

wrote in message
news:T.De.Villiers.1vnjqb_1127210724.3351@excelfor um-nospam.com...

=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


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread:
http://www.excelforum.com/showthread...hreadid=469109


--

Dave Peterson

--

Dave Peterson