Array formulas

[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.

 

[Ken Wright]

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

 

[Jon Parker]

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.

 

[Ken Wright]

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.

 

[Jon Parker]

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

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

 

[Ken Wright]

Good stuff - Very much appreciate the feedback, thanks.