Need an Expanding array formula
 

Hey all, I am fairly adept with formulas, however I have run into a blank on
what I need to do for this one. I will admit, that I am least adept with
statistical formulas. Here is what I am trying to go. In column A1 will
appear a number, that will be variable lets say, from 1 to 10. Columns A2:A11
will have static numbers based on other things, but thats neither here nor
there. There will be a sum formula in A12, that will sum A2 down to whatever
value is in A1. For instance if there is a 3 in A1, the sum formula will sum
A2:A5. I know an IF statement wouldn't work due to the 7 limitation, nor
would I want a formula that is that long =). I know there must be a simple
way to do this. Any thoughts?

Hi

=SUM(INDIRECT("A2:A"&A1))

Regards

Roger Govier



havocdragon wrote:

Hey all, I am fairly adept with formulas, however I have run into a blank on
what I need to do for this one. I will admit, that I am least adept with
statistical formulas. Here is what I am trying to go. In column A1 will
appear a number, that will be variable lets say, from 1 to 10. Columns A2:A11
will have static numbers based on other things, but thats neither here nor
there. There will be a sum formula in A12, that will sum A2 down to whatever
value is in A1. For instance if there is a 3 in A1, the sum formula will sum
A2:A5. I know an IF statement wouldn't work due to the 7 limitation, nor
would I want a formula that is that long =). I know there must be a simple
way to do this. Any thoughts?

=SUM(OFFSET(A2,0,0,A1,1))

"havocdragon" wrote:

Hey all, I am fairly adept with formulas, however I have run into a blank on
what I need to do for this one. I will admit, that I am least adept with
statistical formulas. Here is what I am trying to go. In column A1 will
appear a number, that will be variable lets say, from 1 to 10. Columns A2:A11
will have static numbers based on other things, but thats neither here nor
there. There will be a sum formula in A12, that will sum A2 down to whatever
value is in A1. For instance if there is a 3 in A1, the sum formula will sum
A2:A5. I know an IF statement wouldn't work due to the 7 limitation, nor
would I want a formula that is that long =). I know there must be a simple
way to do this. Any thoughts?

I think it'd have to be

=SUM(INDIRECT("A2:A"&A1+1))

"Roger Govier" wrote:

Hi

=SUM(INDIRECT("A2:A"&A1))

Regards

Roger Govier



havocdragon wrote:

Hey all, I am fairly adept with formulas, however I have run into a blank on
what I need to do for this one. I will admit, that I am least adept with
statistical formulas. Here is what I am trying to go. In column A1 will
appear a number, that will be variable lets say, from 1 to 10. Columns A2:A11
will have static numbers based on other things, but thats neither here nor
there. There will be a sum formula in A12, that will sum A2 down to whatever
value is in A1. For instance if there is a 3 in A1, the sum formula will sum
A2:A5. I know an IF statement wouldn't work due to the 7 limitation, nor
would I want a formula that is that long =). I know there must be a simple
way to do this. Any thoughts?

Thanks Duke, you are quite right.
Well spotted!!

Regards

Roger Govier



Duke Carey wrote:

I think it'd have to be

=SUM(INDIRECT("A2:A"&A1+1))

"Roger Govier" wrote:



Hi

=SUM(INDIRECT("A2:A"&A1))

Regards

Roger Govier



havocdragon wrote:



Hey all, I am fairly adept with formulas, however I have run into a blank on
what I need to do for this one. I will admit, that I am least adept with
statistical formulas. Here is what I am trying to go. In column A1 will
appear a number, that will be variable lets say, from 1 to 10. Columns A2:A11
will have static numbers based on other things, but thats neither here nor
there. There will be a sum formula in A12, that will sum A2 down to whatever
value is in A1. For instance if there is a 3 in A1, the sum formula will sum
A2:A5. I know an IF statement wouldn't work due to the 7 limitation, nor
would I want a formula that is that long =). I know there must be a simple
way to do this. Any thoughts?

Well it's got me puzzled.

from the OP:
............. For instance if there is a 3 in A1, the sum formula will
sum
A2:A5

so shouldn't it be:

=SUM(INDIRECT("A2:A"&A1+2))

or if the OP had a typo and the 3 should have been a 5 then Roger's formula
would be correct.


--
Puzzled,

Sandy

Replace@mailinator with @tiscali.co.uk


"Duke Carey" wrote in message
...
I think it'd have to be

=SUM(INDIRECT("A2:A"&A1+1))

"Roger Govier" wrote:

Hi

=SUM(INDIRECT("A2:A"&A1))

Regards

Roger Govier



havocdragon wrote:

Hey all, I am fairly adept with formulas, however I have run into a
blank on
what I need to do for this one. I will admit, that I am least adept with
statistical formulas. Here is what I am trying to go. In column A1 will
appear a number, that will be variable lets say, from 1 to 10. Columns
A2:A11
will have static numbers based on other things, but thats neither here
nor
there. There will be a sum formula in A12, that will sum A2 down to
whatever
value is in A1. For instance if there is a 3 in A1, the sum formula will
sum
A2:A5. I know an IF statement wouldn't work due to the 7 limitation, nor
would I want a formula that is that long =). I know there must be a
simple
way to do this. Any thoughts?

Duke Carey wrote...
I think it'd have to be

=SUM(INDIRECT("A2:A"&A1+1))

"Roger Govier" wrote:
....
=SUM(INDIRECT("A2:A"&A1))
....
havocdragon wrote:
....
. . . For instance if there is a 3 in A1, the sum formula will sum
A2:A5. . . .
....

A1 == 3, then SUM(INDIRECT("A2:A"&A1+1)) == SUM(A2:A4), not SUM(A2:A5).
Perhaps the OP misspecified or made a typo. Either way, this uses a
volatile function. That could be avoided by using

=SUM(A2:INDEX(A:A,A1+2))

Harlan - "Either way, this uses a volatile function. That could be avoided by
using.."

What's the 'volatile' issue? Why should one avoid a volatile function?

Is solution using OFFSET() also to be avoided?

"Harlan Grove" wrote:

Duke Carey wrote...
I think it'd have to be

=SUM(INDIRECT("A2:A"&A1+1))

"Roger Govier" wrote:
....
=SUM(INDIRECT("A2:A"&A1))
....
havocdragon wrote:
....
. . . For instance if there is a 3 in A1, the sum formula will sum
A2:A5. . . .
....

A1 == 3, then SUM(INDIRECT("A2:A"&A1+1)) == SUM(A2:A4), not SUM(A2:A5).
Perhaps the OP misspecified or made a typo. Either way, this uses a
volatile function. That could be avoided by using

=SUM(A2:INDEX(A:A,A1+2))

Duke Carey wrote...
....
What's the 'volatile' issue? Why should one avoid a volatile function?

Excel uses minimal recalculation. A formula like =SUM(A2:A5) is
evaluated when entered then only when any of the cells in A2:A5 change.
Volatile functions always recalc, so =SUM(INDIRECT("A2:A5"))
recalculates whenever anything triggers recalculation. If there are
many volatile function calls, Excel's performance degradation becomes a
problem.

Is solution using OFFSET() also to be avoided?

OFFSET is also volatile.

I'm not saying never to use volatile functions, but use them only when
there's no good nonvolatile alternative.

Thanks guys, heres what I have so far

{=SUM(LARGE(OFFSET(A3,0,0,A1,1),ROW(INDIRECT("1:10 "))))}

The only thing I can't seem to do, is get Indiferent("1:10") to be based off
of a variable source, or a value determined in lets say B1.

"Duke Carey" wrote:

=SUM(OFFSET(A2,0,0,A1,1))

"havocdragon" wrote:

Hey all, I am fairly adept with formulas, however I have run into a blank on
what I need to do for this one. I will admit, that I am least adept with
statistical formulas. Here is what I am trying to go. In column A1 will
appear a number, that will be variable lets say, from 1 to 10. Columns A2:A11
will have static numbers based on other things, but thats neither here nor
there. There will be a sum formula in A12, that will sum A2 down to whatever
value is in A1. For instance if there is a 3 in A1, the sum formula will sum
A2:A5. I know an IF statement wouldn't work due to the 7 limitation, nor
would I want a formula that is that long =). I know there must be a simple
way to do this. Any thoughts?

{=SUM(LARGE(OFFSET(A3,0,0,A1,1),ROW(INDIRECT("1:10 "))))}

The only thing I can't seem to do, is get Indiferent("1:10") to be based
off
of a variable source, or a value determined in lets say B1.

=SUM(LARGE(OFFSET(A3,0,0,A1,1),ROW(INDIRECT("1:"&B 1))))

Biff


"havocdragon" wrote in message
...
Thanks guys, heres what I have so far

{=SUM(LARGE(OFFSET(A3,0,0,A1,1),ROW(INDIRECT("1:10 "))))}

The only thing I can't seem to do, is get Indiferent("1:10") to be based
off
of a variable source, or a value determined in lets say B1.

"Duke Carey" wrote:

=SUM(OFFSET(A2,0,0,A1,1))

"havocdragon" wrote:

Hey all, I am fairly adept with formulas, however I have run into a
blank on
what I need to do for this one. I will admit, that I am least adept
with
statistical formulas. Here is what I am trying to go. In column A1 will
appear a number, that will be variable lets say, from 1 to 10. Columns
A2:A11
will have static numbers based on other things, but thats neither here
nor
there. There will be a sum formula in A12, that will sum A2 down to
whatever
value is in A1. For instance if there is a 3 in A1, the sum formula
will sum
A2:A5. I know an IF statement wouldn't work due to the 7 limitation,
nor
would I want a formula that is that long =). I know there must be a
simple
way to do this. Any thoughts?

I'm not saying never to use volatile functions, but use them only when
there's no good nonvolatile alternative.

I've always wondered why some functions are volatile. I can understand why a
couple are, Now, Today, but why the others?

Rand, Randbetween, Indirect, Offset

Biff

"Harlan Grove" wrote in message
oups.com...
Duke Carey wrote...
...
What's the 'volatile' issue? Why should one avoid a volatile function?

Excel uses minimal recalculation. A formula like =SUM(A2:A5) is
evaluated when entered then only when any of the cells in A2:A5 change.
Volatile functions always recalc, so =SUM(INDIRECT("A2:A5"))
recalculates whenever anything triggers recalculation. If there are
many volatile function calls, Excel's performance degradation becomes a
problem.

Is solution using OFFSET() also to be avoided?

OFFSET is also volatile.

I'm not saying never to use volatile functions, but use them only when
there's no good nonvolatile alternative.

"Biff" wrote...
....
I've always wondered why some functions are volatile. I can understand
why a couple are, Now, Today, but why the others?

Rand, Randbetween, Indirect, Offset
....

RAND[BETWEEN] needs to be volatile for the same reason NOW does. It *should*
change value on *every* recalc.

As for INDIRECT, it'd be awkward and inefficient to try to figure out its
own dependencies and the dependencies it induces. If it were passed a
constant array, e.g., ROW(INDIRECT("1:1024")), then it could be nonvolatile,
but that's an unreasonable level of semantic overloading.

As for OFFSET, better point since volatile OFFSET(r,a,b,c,d) is presumably
equivalent to nonvolatile INDEX(r,a+1,b+1):INDEX(r,a+c,b+d),
at least if one can rely on INDEX being nonvolatile.