Running sum

[GregB]

I have three columns; A, B and C.

A is simply labels, B is a daily mileage figure and C is the running sum of
this daily mileage. Is there a formula (or some method) that will allow me
to keep this simple three column format and still accumulate mileage in
column C?

Thanks for any words of wisdom.

BondG

 

[Dave R.]

This approach should work:

A1: Time1
B1: 15
C1: 15 (running total)

A2: Time2
B2: 20
C2: =C1+B2 (running total adding most recent mileage [B2] to previous
running total, use this formula the rest of the way down)

Copy C2 down for as many times as you have.

 

[GregB]

Thanks much for your reply, but I'm afraid I wasn't very clear. Let me have
another go at it...

(If the layout gets whacked in the posting, then a copy/paste to Notepad
ought to fix it up)

A, B and C remain the same as before, but what I failed to mention is that
each row is a different department for which I need a cumulative total.
Comme ca:

+ A + B + C +
____________________________________________________________________
1| DeptName | Today's Miles | Monthly Miles |
2| Dept1 | 10 | 150 |
3| Dept2 | 23 | 120 |
4| Dept3 | 15 | 100 |
etc.........

I'd like to be able to type a new number into B2 tomorrow (say, 25) and have
it increment the value in C2 (to 175, in this case). Then on Thursday,
enter that day's mileage number and again, have C2 add that mileage to the
175 that now resides there.

In other words, as the monthly miles are accumulated, I don't care to keep
the daily figures. The one column to enter each day's mileage is fine.

I've a workaround that involves using another worksheet, two tiled windows
and a .xlw (workspace) file. I'd rather keep it simple, however.

BondG

This approach should work:

A1: Time1
B1: 15
C1: 15 (running total)

A2: Time2
B2: 20
C2: =C1+B2 (running total adding most recent mileage [B2] to previous
running total, use this formula the rest of the way down)

Copy C2 down for as many times as you have.

I have three columns; A, B and C.

A is simply labels, B is a daily mileage figure and C is the running sum of
this daily mileage. Is there a formula (or some method) that will allow me
to keep this simple three column format and still accumulate mileage in
column C?

Thanks for any words of wisdom.

BondG

 

[Dave R.]

Im afraid I can't be of much help here, but I think what you would need is
an "AfterUpdate" event (you can search for this). It would be setup so that
after cells B2, b3, or b4 are updated, whatever number is placed there is
added to the value that resides in column C for that row, and that value is
written over what currently resides in column C for that row (in other words
a variable is created, the value stored there, then column C is set to the
value of that variable).

If I didn't have work to do, I'd work on this further, but hopefully that
gets you started in the right direction. The other alternative would be to
rearrange the spreadsheet so you could continuously put daily mileage
numbers in there, with an associated date, then have a set of cells at the
top using an array or sumproduct formula that would add up miles for any
given month.

Dave

Thanks much for your reply, but I'm afraid I wasn't very clear. Let me have
another go at it...

(If the layout gets whacked in the posting, then a copy/paste to Notepad
ought to fix it up)

A, B and C remain the same as before, but what I failed to mention is that
each row is a different department for which I need a cumulative total.
Comme ca:

+ A + B + C +
____________________________________________________________________
1| DeptName | Today's Miles | Monthly Miles |
2| Dept1 | 10 | 150 |
3| Dept2 | 23 | 120 |
4| Dept3 | 15 | 100 |
etc.........

I'd like to be able to type a new number into B2 tomorrow (say, 25) and have
it increment the value in C2 (to 175, in this case). Then on Thursday,
enter that day's mileage number and again, have C2 add that mileage to the
175 that now resides there.

In other words, as the monthly miles are accumulated, I don't care to keep
the daily figures. The one column to enter each day's mileage is fine.

I've a workaround that involves using another worksheet, two tiled windows
and a .xlw (workspace) file. I'd rather keep it simple, however.

BondG

This approach should work:

A1: Time1
B1: 15
C1: 15 (running total)

A2: Time2
B2: 20
C2: =C1+B2 (running total adding most recent mileage [B2] to previous
running total, use this formula the rest of the way down)

Copy C2 down for as many times as you have.

I have three columns; A, B and C.

A is simply labels, B is a daily mileage figure and C is the running

sum
of

this daily mileage. Is there a formula (or some method) that will

allow
me

to keep this simple three column format and still accumulate mileage in
column C?

Thanks for any words of wisdom.

BondG

 

[GregB]

Thanks for your input, Dave. Your second suggestion is what I'm doing now.
But I don't like it much. I'll look into your first suggestion.

BondG

Im afraid I can't be of much help here, but I think what you would need is
an "AfterUpdate" event (you can search for this). It would be setup so that
after cells B2, b3, or b4 are updated, whatever number is placed there is
added to the value that resides in column C for that row, and that value is
written over what currently resides in column C for that row (in other words
a variable is created, the value stored there, then column C is set to the
value of that variable).

If I didn't have work to do, I'd work on this further, but hopefully that
gets you started in the right direction. The other alternative would be to
rearrange the spreadsheet so you could continuously put daily mileage
numbers in there, with an associated date, then have a set of cells at the
top using an array or sumproduct formula that would add up miles for any
given month.

Dave

Thanks much for your reply, but I'm afraid I wasn't very clear. Let me have
another go at it...

(If the layout gets whacked in the posting, then a copy/paste to Notepad
ought to fix it up)

A, B and C remain the same as before, but what I failed to mention is that
each row is a different department for which I need a cumulative total.
Comme ca:

+ A + B + C +
____________________________________________________________________
1| DeptName | Today's Miles | Monthly Miles |
2| Dept1 | 10 | 150 |
3| Dept2 | 23 | 120 |
4| Dept3 | 15 | 100 |
etc.........

I'd like to be able to type a new number into B2 tomorrow (say, 25) and have
it increment the value in C2 (to 175, in this case). Then on Thursday,
enter that day's mileage number and again, have C2 add that mileage to the
175 that now resides there.

In other words, as the monthly miles are accumulated, I don't care to keep
the daily figures. The one column to enter each day's mileage is fine.

I've a workaround that involves using another worksheet, two tiled windows
and a .xlw (workspace) file. I'd rather keep it simple, however.

BondG

This approach should work:

A1: Time1
B1: 15
C1: 15 (running total)

A2: Time2
B2: 20
C2: =C1+B2 (running total adding most recent mileage [B2] to previous
running total, use this formula the rest of the way down)

Copy C2 down for as many times as you have.

I have three columns; A, B and C.

A is simply labels, B is a daily mileage figure and C is the running sum
of
this daily mileage. Is there a formula (or some method) that will allow
me
to keep this simple three column format and still accumulate mileage in
column C?

Thanks for any words of wisdom.

BondG

 

[NealUK]

I have just been messing about with this, and come up with:
A1=Dept, B1=daily mile C1=0 (to start)

Insert the first daily mile, and then record a macro along the lines of
this.
Goto D1, enter =B1+C1, Copy from D1, Paste Special, VALUE into C1, Delete
D1, and Delete B1,
stop recording macro

This leaves B1 free for the next days mileage, although D1 must also be left
free (this could be done off the page somewhere though)
This can easily be extended for a range of depts

I've stuck a copy of it on a webspace at


http://www.epoch2001.fsnet.co.uk/mileage/

There is a macro involved, but i do run a virus guard

regards
Neal

Thanks for your input, Dave. Your second suggestion is what I'm doing now.
But I don't like it much. I'll look into your first suggestion.

BondG

Im afraid I can't be of much help here, but I think what you would need is
an "AfterUpdate" event (you can search for this). It would be setup so that
after cells B2, b3, or b4 are updated, whatever number is placed there is
added to the value that resides in column C for that row, and that value is
written over what currently resides in column C for that row (in other words
a variable is created, the value stored there, then column C is set to the
value of that variable).

If I didn't have work to do, I'd work on this further, but hopefully that
gets you started in the right direction. The other alternative would be to
rearrange the spreadsheet so you could continuously put daily mileage
numbers in there, with an associated date, then have a set of cells at the
top using an array or sumproduct formula that would add up miles for any
given month.

Dave

Thanks much for your reply, but I'm afraid I wasn't very clear. Let

me
have

another go at it...

(If the layout gets whacked in the posting, then a copy/paste to Notepad
ought to fix it up)

A, B and C remain the same as before, but what I failed to mention is that
each row is a different department for which I need a cumulative total.
Comme ca:

+ A + B + C +
____________________________________________________________________
1| DeptName | Today's Miles | Monthly Miles |
2| Dept1 | 10 | 150 |
3| Dept2 | 23 | 120 |
4| Dept3 | 15 | 100 |
etc.........

I'd like to be able to type a new number into B2 tomorrow (say, 25)

and
have

it increment the value in C2 (to 175, in this case). Then on Thursday,
enter that day's mileage number and again, have C2 add that mileage to the
175 that now resides there.

In other words, as the monthly miles are accumulated, I don't care to keep
the daily figures. The one column to enter each day's mileage is fine.

I've a workaround that involves using another worksheet, two tiled windows
and a .xlw (workspace) file. I'd rather keep it simple, however.

BondG



This approach should work:

A1: Time1
B1: 15
C1: 15 (running total)

A2: Time2
B2: 20
C2: =C1+B2 (running total adding most recent mileage [B2] to previous
running total, use this formula the rest of the way down)

Copy C2 down for as many times as you have.

I have three columns; A, B and C.

A is simply labels, B is a daily mileage figure and C is the

running
sum

of
this daily mileage. Is there a formula (or some method) that will allow
me
to keep this simple three column format and still accumulate

mileage
in

column C?

Thanks for any words of wisdom.

BondG

 

[Anders S]

Hi,

As I understand it, you want whatever value entered in Col B to be added to the same row in Col C. That can be done quite easily with the Worksheet_Change event.

However, with that setup you have no record of the entries that make up the values in Col C and no way to trace an erroneous entry in Col B.

I recommend you rethink your strategy and record all entries, then make a summary that extracts the data you want, as also Dave R. suggests.

Regards
Anders Silvén

Thanks much for your reply, but I'm afraid I wasn't very clear. Let me have
another go at it...

(If the layout gets whacked in the posting, then a copy/paste to Notepad
ought to fix it up)

A, B and C remain the same as before, but what I failed to mention is that
each row is a different department for which I need a cumulative total.
Comme ca:

+ A + B + C +
____________________________________________________________________
1| DeptName | Today's Miles | Monthly Miles |
2| Dept1 | 10 | 150 |
3| Dept2 | 23 | 120 |
4| Dept3 | 15 | 100 |
etc.........

I'd like to be able to type a new number into B2 tomorrow (say, 25) and have
it increment the value in C2 (to 175, in this case). Then on Thursday,
enter that day's mileage number and again, have C2 add that mileage to the
175 that now resides there.

In other words, as the monthly miles are accumulated, I don't care to keep
the daily figures. The one column to enter each day's mileage is fine.

I've a workaround that involves using another worksheet, two tiled windows
and a .xlw (workspace) file. I'd rather keep it simple, however.

BondG

This approach should work:

A1: Time1
B1: 15
C1: 15 (running total)

A2: Time2
B2: 20
C2: =C1+B2 (running total adding most recent mileage [B2] to previous
running total, use this formula the rest of the way down)

Copy C2 down for as many times as you have.

I have three columns; A, B and C.

A is simply labels, B is a daily mileage figure and C is the running sum of
this daily mileage. Is there a formula (or some method) that will allow me
to keep this simple three column format and still accumulate mileage in
column C?

Thanks for any words of wisdom.

BondG

 

[Dave R.]

Yes I think "Worksheet_Change" was what I actually meant by "AfterUpdate" --
I may be thinking in Access terminology.. :|

Dave


Hi,

As I understand it, you want whatever value entered in Col B to be added to
the same row in Col C. That can be done quite easily with the
Worksheet_Change event.

However, with that setup you have no record of the entries that make up the
values in Col C and no way to trace an erroneous entry in Col B.

I recommend you rethink your strategy and record all entries, then make a
summary that extracts the data you want, as also Dave R. suggests.

Regards
Anders Silvén

Thanks much for your reply, but I'm afraid I wasn't very clear. Let me have
another go at it...

(If the layout gets whacked in the posting, then a copy/paste to Notepad
ought to fix it up)

A, B and C remain the same as before, but what I failed to mention is that
each row is a different department for which I need a cumulative total.
Comme ca:

+ A + B + C +
____________________________________________________________________
1| DeptName | Today's Miles | Monthly Miles |
2| Dept1 | 10 | 150 |
3| Dept2 | 23 | 120 |
4| Dept3 | 15 | 100 |
etc.........

I'd like to be able to type a new number into B2 tomorrow (say, 25) and have
it increment the value in C2 (to 175, in this case). Then on Thursday,
enter that day's mileage number and again, have C2 add that mileage to the
175 that now resides there.

In other words, as the monthly miles are accumulated, I don't care to keep
the daily figures. The one column to enter each day's mileage is fine.

I've a workaround that involves using another worksheet, two tiled windows
and a .xlw (workspace) file. I'd rather keep it simple, however.

BondG

This approach should work:

A1: Time1
B1: 15
C1: 15 (running total)

A2: Time2
B2: 20
C2: =C1+B2 (running total adding most recent mileage [B2] to previous
running total, use this formula the rest of the way down)

Copy C2 down for as many times as you have.

I have three columns; A, B and C.

A is simply labels, B is a daily mileage figure and C is the running

sum
of

this daily mileage. Is there a formula (or some method) that will

allow
me

to keep this simple three column format and still accumulate mileage in
column C?

Thanks for any words of wisdom.

BondG

 

[Anders S]

Didn't notice that AfterUpdate is not in Excel's vocabulary, I did not try to correct you.

Have a nice day/night (1 AM).

Anders

Yes I think "Worksheet_Change" was what I actually meant by "AfterUpdate" --
I may be thinking in Access terminology.. :|

Dave


Hi,

As I understand it, you want whatever value entered in Col B to be added to
the same row in Col C. That can be done quite easily with the
Worksheet_Change event.

However, with that setup you have no record of the entries that make up the
values in Col C and no way to trace an erroneous entry in Col B.

I recommend you rethink your strategy and record all entries, then make a
summary that extracts the data you want, as also Dave R. suggests.

Regards
Anders Silvén

Thanks much for your reply, but I'm afraid I wasn't very clear. Let me have
another go at it...

(If the layout gets whacked in the posting, then a copy/paste to Notepad
ought to fix it up)

A, B and C remain the same as before, but what I failed to mention is that
each row is a different department for which I need a cumulative total.
Comme ca:

+ A + B + C +
____________________________________________________________________
1| DeptName | Today's Miles | Monthly Miles |
2| Dept1 | 10 | 150 |
3| Dept2 | 23 | 120 |
4| Dept3 | 15 | 100 |
etc.........

I'd like to be able to type a new number into B2 tomorrow (say, 25) and have
it increment the value in C2 (to 175, in this case). Then on Thursday,
enter that day's mileage number and again, have C2 add that mileage to the
175 that now resides there.

In other words, as the monthly miles are accumulated, I don't care to keep
the daily figures. The one column to enter each day's mileage is fine.

I've a workaround that involves using another worksheet, two tiled windows
and a .xlw (workspace) file. I'd rather keep it simple, however.

BondG

This approach should work:

A1: Time1
B1: 15
C1: 15 (running total)

A2: Time2
B2: 20
C2: =C1+B2 (running total adding most recent mileage [B2] to previous
running total, use this formula the rest of the way down)

Copy C2 down for as many times as you have.

I have three columns; A, B and C.

A is simply labels, B is a daily mileage figure and C is the running sum
of
this daily mileage. Is there a formula (or some method) that will allow
me
to keep this simple three column format and still accumulate mileage in
column C?

Thanks for any words of wisdom.

BondG

 

[E. Cheker]

Hi GregB,

I think I've got a very simple solution for you:
Say you make use of another column D. Assign the formulas
=B3
=B4
=B5
....etc to D3, D4, D5 respectively.
Now change the formulas in Monthly column to
=B3+D3
=B4+D4
=B5+D5
..... etc to C3, C4,C5 respectively and so on....

Now if you enter any new milage to the todays mile column it will be added
to the Montly column and milage today figure will be copied to the D column.
Hope this method solves your problem
Cheers
___Zoom

Thanks for your input, Dave. Your second suggestion is what I'm doing now.
But I don't like it much. I'll look into your first suggestion.

BondG

Im afraid I can't be of much help here, but I think what you would need is
an "AfterUpdate" event (you can search for this). It would be setup so that
after cells B2, b3, or b4 are updated, whatever number is placed there is
added to the value that resides in column C for that row, and that value is
written over what currently resides in column C for that row (in other words
a variable is created, the value stored there, then column C is set to the
value of that variable).

If I didn't have work to do, I'd work on this further, but hopefully that
gets you started in the right direction. The other alternative would be to
rearrange the spreadsheet so you could continuously put daily mileage
numbers in there, with an associated date, then have a set of cells at the
top using an array or sumproduct formula that would add up miles for any
given month.

Dave

Thanks much for your reply, but I'm afraid I wasn't very clear. Let

me
have

another go at it...

(If the layout gets whacked in the posting, then a copy/paste to Notepad
ought to fix it up)

A, B and C remain the same as before, but what I failed to mention is that
each row is a different department for which I need a cumulative total.
Comme ca:

+ A + B + C +
____________________________________________________________________
1| DeptName | Today's Miles | Monthly Miles |
2| Dept1 | 10 | 150 |
3| Dept2 | 23 | 120 |
4| Dept3 | 15 | 100 |
etc.........

I'd like to be able to type a new number into B2 tomorrow (say, 25)

and
have

it increment the value in C2 (to 175, in this case). Then on Thursday,
enter that day's mileage number and again, have C2 add that mileage to the
175 that now resides there.

In other words, as the monthly miles are accumulated, I don't care to keep
the daily figures. The one column to enter each day's mileage is fine.

I've a workaround that involves using another worksheet, two tiled windows
and a .xlw (workspace) file. I'd rather keep it simple, however.

BondG



This approach should work:

A1: Time1
B1: 15
C1: 15 (running total)

A2: Time2
B2: 20
C2: =C1+B2 (running total adding most recent mileage [B2] to previous
running total, use this formula the rest of the way down)

Copy C2 down for as many times as you have.

I have three columns; A, B and C.

A is simply labels, B is a daily mileage figure and C is the

running
sum

of
this daily mileage. Is there a formula (or some method) that will allow
me
to keep this simple three column format and still accumulate

mileage
in

column C?

Thanks for any words of wisdom.

BondG

 

[E. Cheker]

Sorry GregB,

How stupid of me to think it can be that simle...
Well I punished myself and work on a simple macro solution
Here it is:
------------------------------
Sub DonguGoTo()
Dim i As Integer
i = InputBox("Input daily milage")
GoTo topla
topla:
i = i + Range("B2").Value
Range("B2").Value = i
End Sub
-------------------------------------
I assume that the total milage figure is in cell B2
Best wishes

Thanks for your input, Dave. Your second suggestion is what I'm doing now.
But I don't like it much. I'll look into your first suggestion.

BondG

Im afraid I can't be of much help here, but I think what you would need is
an "AfterUpdate" event (you can search for this). It would be setup so that
after cells B2, b3, or b4 are updated, whatever number is placed there is
added to the value that resides in column C for that row, and that value is
written over what currently resides in column C for that row (in other words
a variable is created, the value stored there, then column C is set to the
value of that variable).

If I didn't have work to do, I'd work on this further, but hopefully that
gets you started in the right direction. The other alternative would be to
rearrange the spreadsheet so you could continuously put daily mileage
numbers in there, with an associated date, then have a set of cells at the
top using an array or sumproduct formula that would add up miles for any
given month.

Dave

Thanks much for your reply, but I'm afraid I wasn't very clear. Let

me
have

another go at it...

(If the layout gets whacked in the posting, then a copy/paste to Notepad
ought to fix it up)

A, B and C remain the same as before, but what I failed to mention is that
each row is a different department for which I need a cumulative total.
Comme ca:

+ A + B + C +
____________________________________________________________________
1| DeptName | Today's Miles | Monthly Miles |
2| Dept1 | 10 | 150 |
3| Dept2 | 23 | 120 |
4| Dept3 | 15 | 100 |
etc.........

I'd like to be able to type a new number into B2 tomorrow (say, 25)

and
have

it increment the value in C2 (to 175, in this case). Then on Thursday,
enter that day's mileage number and again, have C2 add that mileage to the
175 that now resides there.

In other words, as the monthly miles are accumulated, I don't care to keep
the daily figures. The one column to enter each day's mileage is fine.

I've a workaround that involves using another worksheet, two tiled windows
and a .xlw (workspace) file. I'd rather keep it simple, however.

BondG



This approach should work:

A1: Time1
B1: 15
C1: 15 (running total)

A2: Time2
B2: 20
C2: =C1+B2 (running total adding most recent mileage [B2] to previous
running total, use this formula the rest of the way down)

Copy C2 down for as many times as you have.

I have three columns; A, B and C.

A is simply labels, B is a daily mileage figure and C is the

running
sum

of
this daily mileage. Is there a formula (or some method) that will allow
me
to keep this simple three column format and still accumulate

mileage
in

column C?

Thanks for any words of wisdom.

BondG