Array formula returning the cumulative sum

[vsoler]

Because I am studying in depth the array formulas, I would like to
know if it is possible to build a formula such that, given a set of
numbers in a vertical range (1 column wide), it returns an array with
the cumulative sum.

For example, if A1:A5 contains:

3
8
5
1
6

Then, the formula should return

3
11
16
17
23

Of course, I know that the goal can be achieved by mean of a simple
formula:

=SUM($A$1:A1) copied down

However, I keep an interest (let's call it theoretical) in knowing if
the exercise is possible?

Can anybody help?

 

[vsoler]

Because I am studying in depth the array formulas, I would like to
know if it is possible to build a formula such that, given a set of
numbers in a vertical range (1 column wide), it returns an array with
the cumulative sum.

For example, if A1:A5 contains:

3
8
5
1
6

Then, the formula should return

3
11
16
17
23

Of course, I know that the goal can be achieved by mean of a simple
formula:

=SUM($A$1:A1) copied down

However, I keep an interest (let's call it theoretical) in knowing if
the exercise is possible?

Can anybody help?

Perhaps I should explain a little further.

I have the impression that array formulas cannot handle correctly the
difference between absolute and relative references. They are only
able to iterate a single cell reference inside a range of cells.

For example, if I write {=SUMIF(A1:A5,A1:A5)} entered with Ctrl-
Shift-Enter, the array formula takes one single cell at a time inside
the A1:A5 range and makes the comparison, thats all. In this case,
array calculating mean iterating.

If I write {=SUMIF($A$1:$A$5,$A$1:$A$5)} entered with Ctrl-Shift-
Enter I get exactly the same result. Conclusion: there is no real
handling of absolute and relative references.

Even if you find no solution to my question, I would like to have your
feedback, I would appreciate it.

Thank you

 

[Guest]

ASSUMING THE RANGE IS IN A1:A5 USE THIS FORMULA

=SUM(SUBTOTAL(9,(OFFSET(A$1:A5,,,ROW(INDIRECT("1:5")),1))))

 

[Bob Phillips]

As far as I can see, no function differentiates between absolute and
relative references. A1 is the same cell as $A$1. It becomes relevant when
copying that cell reference to another cell, not in the evaluation of a
formula.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I think the OP just wants to return the entire array, so I took the liberty
of removing the sum from your formula.
=SUBTOTAL(9,(OFFSET(A$1:A5,,,ROW(INDIRECT("1:5")),1)))

 

[krcowen]

I think this should work for any number of rows, provided the top row
is in row 1. If the top row is not row 1, then the row() arugument
will need to be adjusted.

=SUM(OFFSET(A$1,0,0,ROW(),1))

entered as an array function of course.

Good luck.

Ken
Norfolk, Va

 

[Harlan Grove]

For example, if A1:A5 contains:

3
8
5
1
6

Then, the formula should return

3
11
16
17
23

....

FTHOI, an approach that uses no volatile functions, and could work as
easily with an array as a range. If your original range were named D,
then the array formula

=MMULT(--(ROW(D)>=TRANSPOSE(ROW(D))),D)

returns

{3;11;16;17;23}

 

[vsoler]

...>For example, if A1:A5 contains:




...

FTHOI, an approach that uses no volatile functions, and could work as
easily with an array as a range. If your original range were named D,
then the array formula

=MMULT(--(ROW(D)>=TRANSPOSE(ROW(D))),D)

returns

{3;11;16;17;23}

Harlan,

I really like your proposed solution, it works really well.
Thank you

 

[Bernd P]

Hello,

Harlan's solution is fine.

Another approach:
=SUM($A$1:INDEX($A$1:$A$5,ROW($1:$5)))
array-entered, non-volatile.

Regards,
Bernd

 

[Lori]

Also non-volatile (and assuming no negative values):

=PROB(ROW(A1:A5),A1:A5/SUM(A1:A5),,ROW(A1:A5))*SUM(A1:A5)

 

[Bernd P]

Hello again,

Let us have a look at current suggestions.

I tested them on 1000 rows with random integers between 1 and 999.

Calculation time in milliseconds (FastExcel) was:
Bernd 0.96
Ken 1.25
JMB 12.32
Lori 114.84
Harlan 534.70

The (optimal?) non array solution (B1: =A1, B2: A2+B1, copy down) took
0.51

Regards,
Bernd

 

[Bob Phillips]

Would you have posted that if Bernd's had been 2500?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Harlan Grove]

I tested them on 1000 rows with random integers between 1 and 999.

Calculation time in milliseconds (FastExcel) was:
Bernd 0.96
Ken 1.25
JMB 12.32
Lori 114.84
Harlan 534.70

The (optimal?) non array solution (B1: =A1, B2: A2+B1, copy down)
took 0.51

OK, now try each approach with a derived array rather than a range.

I won't (and didn't) claim my approach was fast, but it is flexible.

Note that your approach also relies on implicit indexing. You can
enter the array formula

=SUM($A$1:INDEX($A$1:$A$5,ROW($A$1:$A$5)))

[note: don't be overly broad with your range argument to ROW - with
your original argument, $1:$5, any change in rows 1 through 5 outside
column A would trigger recalculation of this array formula] in a 5-
cell range, but

=SUM(SUM($A$1:INDEX($A$1:$A$5,ROW($A$1:$A$5))))

would return the same thing as the previous formula, entered as an
array or not.

 

[vsoler]

Hello,

Harlan's solution is fine.

Another approach:
=SUM($A$1:INDEX($A$1:$A$5,ROW($1:$5)))
array-entered, non-volatile.

Regards,
Bernd

Bernd,

Your formula seems fine for me. It is perhaps what I was looking for
(one never knows what one's is after until found).

Let me say something. The level of activity and of knowledge that this
group is reaching is absolutely remarkable. It has moved forward since
I posted some questions some years ago.

My congratulations to all of you.

I have a field of interest which I may talk you about sometime in the
future.

In addition to learning how excel works, I'm keen of trying to
describe how it should work.

My personal opinion is that, putting aside perhaps the new Excel 2007,
Microsoft has concentrated a lot more on coloring cells, formatting
and visual effects than in increasing the power of calculation of a
tool that today is used in every office (or company) in the world.

I have given an eye to openoffice calc, but found that it's main
purpose is replicating the way excel works.

Congratulations again to you all for your good job.

 

[Lori]

Actually the nonnegative condition is not needed. PROB can take any
values positive or negative contrary to what the help file might say.

 

[Bernd P]

Hello Bob,

Why not? Harlan's sum(sum(...)) takes only 0.92ms.

Regards,
Bernd