Quick Explanation

=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

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" <T.De.Villiers.1vnjqb_1127210724.3351@excelforum-nospam.com>
wrote in message
news:T.De.Villiers.1vnjqb_1127210724.3351@excelforum-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" <T.De.Villiers.1vnjqb_1127210724.3351@excelforum-nospam.com>
wrote in message
news:T.De.Villiers.1vnjqb_1127210724.3351@excelforum-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" <T.De.Villiers.1vnjqb_1127210724.3351@excelforum-nospam.com>
> wrote in message
> news:T.De.Villiers.1vnjqb_1127210724.3351@excelforum-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" <petersod@verizonXSPAM.net> wrote in message
news:4330AE05.3D4F0F06@verizonXSPAM.net...
> 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"
>> <T.De.Villiers.1vnjqb_1127210724.3351@excelforum-nospam.com>
>> wrote in message
>> news:T.De.Villiers.1vnjqb_1127210724.3351@excelforum-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" <petersod@verizonXSPAM.net> wrote in message
> news:4330AE05.3D4F0F06@verizonXSPAM.net...
> > 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"
> >> <T.De.Villiers.1vnjqb_1127210724.3351@excelforum-nospam.com>
> >> wrote in message
> >> news:T.De.Villiers.1vnjqb_1127210724.3351@excelforum-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