Need an Expanding array formula

[Guest]

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?

 

[Roger Govier]

Hi

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

Regards

Roger Govier

 

[Guest]

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

 

[Guest]

I think it'd have to be

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

 

[Roger Govier]

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

Regards

Roger Govier

 

[Sandy Mann]

Well it's got me puzzled.

from the OP:
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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Harlan Grove]

Duke Carey wrote...

I think it'd have to be

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

"Roger Govier" wrote: ....

....

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))

 

[Guest]

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]

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.

 

[Guest]

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.

 

[Biff]

{=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:"&B1))))

Biff

 

[Biff]

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]

...
....

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.