Create a running totaol

  • Thread starter Thread starter Jdog
  • Start date Start date
J

Jdog

CELL A1 has a number CELL B1 is the total (A1). As I add a number i
CELL A2 I want CELL B2 to be the running total. Then I add a number t
CELL A3 and I want B3 to be the total and etc. I don't want the cell
in B to have any number in them untill I add something in column A
Please help. THANKS!
 
Try:

=IF(A1="","",SUM(A1:OFFSET(A1,COUNT(A:A),0)))

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
Try this for a non-volatile suggestion:

=IF(A1,SUM($A$1:A1),"")

--

HTH,

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


CELL A1 has a number CELL B1 is the total (A1). As I add a number in
CELL A2 I want CELL B2 to be the running total. Then I add a number to
CELL A3 and I want B3 to be the total and etc. I don't want the cells
in B to have any number in them untill I add something in column A.
Please help. THANKS!!
 
RagDyeR said:
Try this for a non-volatile suggestion:

=IF(A1,SUM($A$1:A1),"")
Click to expand...

I assume that you mean to copy the formula down or you meant
=IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in
column A.

If you meant copy down and the OP had say, 4,000 rows of data, would that
not be more of a calculation load than a volatile function? Of course it
would depend on what the rest of the sheet was like.

--
Regards

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
It would have to be copied down in order to fulfill the OP's request of
being a *running* total.

And, of course ... I always follow OP directives.<g>

As far as volatility ... 4,000 rows being calculated every time *anything*
in the sheet changes, is really not even a point of discussion.
--

Regards,

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

RagDyeR said:
Try this for a non-volatile suggestion:

=IF(A1,SUM($A$1:A1),"")
Click to expand...

I assume that you mean to copy the formula down or you meant
=IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in
column A.

If you meant copy down and the OP had say, 4,000 rows of data, would that
not be more of a calculation load than a volatile function? Of course it
would depend on what the rest of the sheet was like.

--
Regards

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
The problem I have with this solution is that if you work with the
results of the running total you get #value because of the "" value.
 
The OP asked that "no number" be present until a value was entered in Column
A.

What are your specs?
Can you accept a zero?

=IF(A1,SUM($A$1:A1),0)

Also, exactly what formula are you using, and in what way are you using it,
that's giving you an error?

There might be viable "workarounds".
 
My apologies, I was actually reacting generally to the fact that Excel
does not handle the results of a formula which resolves to "" very well
and not to this specific question.
 
Well ... reacting "generally" ... If you attempted to add (Sum() ), the
running total column that contained those zero length strings ( "" ), you
would get an accurate total, with *no* error messages.
 
BTW, who's Torsorq?<g>

Anyway, very enlightening David.
When you come right down to it, it's really quite logical that
=Sum($A$1:A1)
Does really create a monster calculation job.

One doesn't think about it when referencing several hundred rows.

I don't think I'll suggest it again ... at least not without a caveat as to
the size of the calculation.

However, one might try this:

=INDEX($B$1:$B$20000,ROW()-1)+A2

In place of this:

=OFFSET(B2,-1,)+A2

As far as I can tell, they're about the same speed ... for calculation ...
as well as deletion.

But I must admit, that the Offset does "look" cleaner then the Index,
Just as the =Sum($A$1:A1)
Looks cleaner then the Offset.
--
Regards,

RD
 
Guess I picked the name from the wrong thread in a previous message.
Appears that one involved counting instead of a total.

Why would index work faster or make more sense than
offset of one row up from a cell on the current row.
 
Never said it would be faster or better, just an observation of an
alternative approach.
 
Back
Top