Running Total by formula

D

Dallman Ross

Folks, is it possible to have a running total of a column of numbers via
formula alone? No helper-column, I mean. I could then name the formula
and chart it, for example. With a helper-column it's easy, of course.
I'm wondering if it's possible without, though. Anybody see a way?

Thanks,
=dman=
 
R

Ragdyer

Say you start your values in A2, and will continue to enter additional data
down Column A.

In B2 enter:

=SUM(A$2:A2)

And copy down as needed.

This is a very resource intensive formula.
Shouldn't be used for ranges in excess of say 5,000 rows.
 
D

Dallman Ross

Ragdyer,

Your suggestion of course works just fine. Thank you.

I would like to ask why the formula you offered is seen as
resource-intensive, and what I might do about it if I still
want running totals for more than circa 5,000 rows.


I was actually trying to figure out if one can do the whole thing
without using any extra column for the running total. Just have a
named formula, for example. But it seems pretty hard to conceive
of. It might be impossible. And if it's not, it might be very
resource intensive. Still, I was racking my brain trying to see if
it could be done and decide if I want to. :)

An analogy would be if we suppose there are numbers entered into
A1:A100 already, and I create a named formula like this:

=($A$1:$A$100)^0

I name it, then use the name in a chart. Now I have a line 100
ticks long with a height of 1. It won't matter what the numbers
are in A, as long as they are numbers.

If I make the formula this:

=($A$1:$A$100)^0*AVERAGE($A$1:$A$100)

I can plot an average line without having to bother having
a separate column to contain the average for my chart.

Well, that's where I was coming from. I wanted to try to
take that further and create other sorts of plottable lines
and curves without using up a column to set them up for the chart.

Best,
=dman=
 
R

RagDyeR

This old post should explain:

http://tinyurl.com/2xawjs

PLUS ... it gives alternatives.

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Ragdyer,

Your suggestion of course works just fine. Thank you.

I would like to ask why the formula you offered is seen as
resource-intensive, and what I might do about it if I still
want running totals for more than circa 5,000 rows.


I was actually trying to figure out if one can do the whole thing
without using any extra column for the running total. Just have a
named formula, for example. But it seems pretty hard to conceive
of. It might be impossible. And if it's not, it might be very
resource intensive. Still, I was racking my brain trying to see if
it could be done and decide if I want to. :)

An analogy would be if we suppose there are numbers entered into
A1:A100 already, and I create a named formula like this:

=($A$1:$A$100)^0

I name it, then use the name in a chart. Now I have a line 100
ticks long with a height of 1. It won't matter what the numbers
are in A, as long as they are numbers.

If I make the formula this:

=($A$1:$A$100)^0*AVERAGE($A$1:$A$100)

I can plot an average line without having to bother having
a separate column to contain the average for my chart.

Well, that's where I was coming from. I wanted to try to
take that further and create other sorts of plottable lines
and curves without using up a column to set them up for the chart.

Best,
=dman=
 
T

T. Valko

=SUM(A$2:A2)
I would like to ask why the formula you offered is seen as
resource-intensive, and what I might do about it if I still
want running totals for more than circa 5,000 rows.
Click to expand...

Think of it like this:

=A2
=A2+A3
=A2+A3+A4
=A2+A3+A4+A5
=A2+A3+A4+A5+A6
=A2+A3+A4+A5+A6+A7

Now, imagine what that looks like when you get to row 5000!

When you get to row 5000 you will have calculated 12,492,501 cells. 4998
formulas, each one calculating more cells than the formula in the previous
row.

The best way to do this on big ranges is like this:

......A.....B.....
2...5.............
3...1.....=A3+A2
4...6.....=A4+B3
5...1.....=A5+B4
6...2.....=A6+B5
7...5.....=A7+B6

This way you still have the same number of formulas but each formula is
calculating just 2 cells.

As far as your problem with the chart...

It sounds like what you need is an array of subtotals. I rarely use charts
so I'm not sure if that can be done using just a single formula. It can't be
done for a worksheet formula calculation but as the source for chart data,
??????
 
R

RagDyeR

Check out this old post:

http://groups.google.com/group/microsoft.public.excel.misc/msg/e16f3958191dde02


--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Ragdyer,

Your suggestion of course works just fine. Thank you.

I would like to ask why the formula you offered is seen as
resource-intensive, and what I might do about it if I still
want running totals for more than circa 5,000 rows.


I was actually trying to figure out if one can do the whole thing
without using any extra column for the running total. Just have a
named formula, for example. But it seems pretty hard to conceive
of. It might be impossible. And if it's not, it might be very
resource intensive. Still, I was racking my brain trying to see if
it could be done and decide if I want to. :)

An analogy would be if we suppose there are numbers entered into
A1:A100 already, and I create a named formula like this:

=($A$1:$A$100)^0

I name it, then use the name in a chart. Now I have a line 100
ticks long with a height of 1. It won't matter what the numbers
are in A, as long as they are numbers.

If I make the formula this:

=($A$1:$A$100)^0*AVERAGE($A$1:$A$100)

I can plot an average line without having to bother having
a separate column to contain the average for my chart.

Well, that's where I was coming from. I wanted to try to
take that further and create other sorts of plottable lines
and curves without using up a column to set them up for the chart.

Best,
=dman=
 
D

Dallman Ross

RagDyeR said:
This old post should explain:

http://tinyurl.com/2xawjs

PLUS ... it gives alternatives.
Click to expand...

Thank you very much.

I do have about 4,000 lines at present and will likely have about 10,000
by the end of the year. But for now I'm using the processor-intensive
method (a named formula):

=SUBTOTAL(9,OFFSET($G$1,ROW()-1,0,myCountA-ROW()+headerRow-1))

Which is essentially similar to your first offering (except with the
oldest data at the bottom of the column). (I can't easily
reproduce the previous articles from the thread because you put them below
a "-- " ".signature" demarker, and my newsreader automatically deletes
the ".sig" and below on reply. Sorry about that.)

I'm using SUBTOTAL because I am constantly filtering the data with
auto-filter and I am able to have the running totals this way
without more formula adjustment necessary.

I appreciated your great help and answer!

=dman=
 
D

Dallman Ross

T. Valko said:
Think of it like this:

=A2
=A2+A3
=A2+A3+A4
=A2+A3+A4+A5
=A2+A3+A4+A5+A6
=A2+A3+A4+A5+A6+A7

Now, imagine what that looks like when you get to row 5000!
Click to expand...

Thanks, Biff!
When you get to row 5000 you will have calculated 12,492,501 cells. 4998
formulas, each one calculating more cells than the formula in the previous
row.

The best way to do this on big ranges is like this:

.....A.....B.....
2...5.............
3...1.....=A3+A2
4...6.....=A4+B3
5...1.....=A5+B4
6...2.....=A6+B5
7...5.....=A7+B6

This way you still have the same number of formulas but each formula is
calculating just 2 cells.
Click to expand...

Looks good. To keep the formula consistent in the past I have
done it this way and filled all data-rows (assume Row 1 is a header
row with text):

=A2+(IF(ISNUMBER(B1),B1,0))

and dragged that down from B2 to the bottom row. But I suppose there
is a trade-off with that, too, in that the IF-statement will require
a bit more "oomph" from Excel.

As far as your problem with the chart...

It sounds like what you need is an array of subtotals. I rarely use charts
so I'm not sure if that can be done using just a single formula. It can't be
done for a worksheet formula calculation but as the source for chart data,
??????
Click to expand...

Yeah, I'm having fun trying, though. :)

Thanks again.

=dman=
 
Top