Need an Expanding array formula

H

havocdragon

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

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
[email protected]
Replace@mailinator with @tiscali.co.uk
 
H

Harlan Grove

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

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

"Roger Govier" wrote: ....
Click to expand...
....

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

Duke Carey

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

Harlan Grove

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

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?
Click to expand...

OFFSET is also volatile.

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

havocdragon

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

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.
Click to expand...

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

Biff
 
B

Biff

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

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
 
H

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
Click to expand...
....

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