Annoying array formula

  • Thread starter Thread starter Stephen Murphy
  • Start date Start date
S

Stephen Murphy

Hi All,

I am hoping someone can help. I am working my way through a chapter on
useful array formulas - trying to fully understand the logic of each.
However this one has got me baffled. It is used for summing the n largest
values in a range and is written as follows:

{=SUM(LARGE(Data,ROW(INDIRECT("1:10"))))} ,where data is a named range.

Now the bit I don't understand is ROW(INDIRECT("1:10").

Can anyone explain please.


Many Thanks

Stephen
 
Hi
- INDIRECT will return a reference according to the given string. In
your case it returns a cell/range reference for the row 1:10. The ROW
function will now return the row number for this cell reference (so the
values starting with 1, ending with 10).
- Entered as array formula the statement ROW(INDIRECT("1:10)) will
evaluate to 1-10 and using SUM will sum the 10 largest values in the
range
 
Thanks Frank,

What I still can't get my head round is if I key in the formula
=INDIRECT("1") either as an array or not I get and error. Should the
INDIRECT function not have an argument like "A1", "D3" etc.

Stephen
 
Hi Stephen
=INDIRECT("1:1")
would do (this selects the entire row 1). You will get this kind of
reference for example if you enter
=SUM(
and then click on the row header '1'
 
I see it now!

I notice that when you use =INDIRECT("1:1") it only returns the contents in
A1, the first cell of the entire row.

Your help is much appreciated.

Cheers,
Stephen
 
Hi
this is correct, as A1 is the first cell of the range 1:1. If you enter
the formula
=SUM(INDIRECT("1:1"))
you will get the sum for the first row
 
Back
Top