Running total in Excel w/2 columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok, I received an answer to my earlier question re: If I have 2 columns, one
"Current" and one "MTD" and daily I am changing the "Current" column, can I
have the "MTD" column keep a running total. The answer was
=IF(B2="","",SUM($B$2:B2)). This will put what I enter in the "Current"
column in the "MTD" column, but it doesn't add the new entries to the "MTD"
column, it will just be the number I entered in the "Current" column. How
can I get the "MTD" column to add the new entries to itself each time I enter
a new number in "Current"?
 
Copy that formula down the column for as many rows as you will likely have
entries in the Current column
 
Duke: Thank you for your response. My problem is this: When I used this
formula and I typed in a number, that number was shown in the MTD column.
Perfect. Except when I enter a new number in the Current column, it just put
that same number in the MTD column.

Say like this:

Current MTD
Shingle loads 5 10
Job set ups 4 8
Shingle complete 6 15

So, the next day I would just put a new number in the current column and
then I am manually changing the MTD column. I would like it to automatically
add the new number itself in the MTD column.
 
Anna
See my response to the posting you put in the other thread you started.
It is best to keep posting in the same thread on the same subject, rather
than starting a new one.
 
In other words, you are overwriting yesterday's Current numbers with today's
Cuurent numbers, as opposed to yesterday's being at the top of the column,
today's being below yesterday's and tomorrow's being below today's, is that
right?

Excel can only add (or otherwise operate on) what exists in a spreadsheet.
If you replace a number in the Current column, the first number ceases to
exist and Excel can't include it in the total.

You can write VBA code that will run either on closing or opening the
workbook and that will convert the running total to a constant and enter it
into a cell. From there it can be referenced in the new running total.

*HOWEVER*, you lose any audit trail if you do something like that. If you
want to go down that path, I'd recommend using VBA to copy each day's Current
values into a blank area in another range or another spreadsheet from which
you can maintain both the audit trail and the running total.
 
Back
Top