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=

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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dallman Ross" <dman@localhost. wrote in message
...

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=

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=
--------------------
In , Ragdyer
spake thusly:

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.

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

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

--
Biff
Microsoft Excel MVP


"Dallman Ross" <dman@localhost. wrote in message
...
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=
--------------------
In , Ragdyer
spake thusly:

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.

Check out this old post:

http://groups.google.com/group/micro...6f3958191dde02


--

Regards,

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

"Dallman Ross" <dman@localhost. wrote in message
...
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=
--------------------
In , Ragdyer
spake thusly:

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.

Typo:

It can't be done for a worksheet formula calculation ...

Should be:

It can be done for a worksheet formula calculation ...


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=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.

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

--
Biff
Microsoft Excel MVP


"Dallman Ross" <dman@localhost. wrote in message
...
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=
--------------------
In , Ragdyer
spake thusly:

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.

In , T. Valko
spake thusly:

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

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!

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.

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

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

Thanks again.

=dman=