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?

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?

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.

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?

=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?

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?