Array formulas

  • Thread starter Thread starter Jon Parker
  • Start date Start date
J

Jon Parker

I like the power offered by array formulas, but I always forget/confuse
basic thumb rules in using them.

Typically I often need to write array formulas that use at least one
parameter as a running sequence number starting from 1 and going to a value
n. How do I generate this running value in the array formula?

MTIA.
 
Either hard code it in, eg:-

{1,2,3,4,5,6,7.....etc} :-

=SUM(LARGE(A1:A100,{1,2,3,4,5,6,7}))

or use the following:-

ROW(INDIRECT("1:7")) :-

=SUM(LARGE(A1:A100,ROW(INDIRECT("1:7"))))

Change numbers to suit
 
The ROW(INDIRECT("...")) is what I was expecting. Or
anything on similar lines.

Basically a means of programmatically generating the
sequence 1, 2, 3, .... n

Many thanks.
 
Do you mean you want to generate a different array by simply changing the value in a helper cell?

If so then you can try the following:-

=SUM(LARGE(A1:A100,ROW(INDIRECT("1:"&G1))) array entered

with your n value going into cell G1. Changing the value in cell G1 will change the sequence
length.

If that's not what you are after then apologies, but I'm just not twigging on to what you are
looking for.
 
Ken,

You have hit the nail on the head. This is exactly what I
was looking for.

jon
-----Original Message-----
Do you mean you want to generate a different array by
Click to expand...
simply changing the value in a helper cell?
If so then you can try the following:-

=SUM(LARGE(A1:A100,ROW(INDIRECT("1:"&G1))) array entered

with your n value going into cell G1. Changing the value
Click to expand...
in cell G1 will change the sequence
length.

If that's not what you are after then apologies, but I'm
Click to expand...
just not twigging on to what you are
 
Back
Top