Create a running totaol

[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!

 

[Sandy Mann]

Try:

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

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[RagDyeR]

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!!

 

[Sandy Mann]

Try this for a non-volatile suggestion:

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

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

 

[Don Guillett]

Meant to copy down. Look again
$a$1:a1

 

[RagDyeR]

Thank you Don.

You can be my lawyer any time you wish!<bg>
--

Regards,

RD

 

[RagDyeR]

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 !
----------------------------------------------------------------------------
-------------------

Try this for a non-volatile suggestion:

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

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

 

[Sandy Mann]

Meant to copy down. Look
again

$a$1:a1

So it does. My apologies to RagDyeR

 

[Sandy Mann]

Meant to copy down. Look
again
So it does. My apologies to RagDyeR

Or should I say my apologies to your client <g>

 

[Don Guillett]

OK. One billable second. If I was a lawyer I would have to charge for an
hour.

 

[Ragdyer]

Billable ? ... Charge ? ... JA nie rozumiem! ! !

 

[noyb]

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.

 

[Ragdyer]

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

 

[noyb]

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.

 

[RagDyeR]

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.

 

[David McRitchie]

Hi /Torsorq,
You might want to reconsider your requirements after reading
http://groups.google.com/groups?as_umsgid=ugDxrXayCHA.2592@TK2MSFTNGP10

Slow Response, Memory Problems, and Speeding up Excel
http://www.mvps.org/dmcritchie/excel/slowresp.htm

 

[RagDyer]

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

 

[David McRitchie]

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.

 

[RagDyer]

Never said it would be faster or better, just an observation of an
alternative approach.

 

[Jdog]

Many great ideas!! I want to thank everyone for thier comments and
suggestions.