Running Total Question

[Sam]

I was hoping someone could help me with a running total question.

I need to keep a running total of hours that someone uses for
disability. Names will always be sorted and together but everyweek
another entry could be made and new names added. I just cannot for the
life of me figure out how to do this. I do not want to use subtotal
because I don't want to add extra rows nor will this work well since
new information is constantly addded.

Column A (Name) Column B (Week Of) Column AC (Hours) Column
AD (Running Total)
Sam 01/01/2012
40 40
Sam 01/08/2012
18 58
Sam 01/15/2012
20 78
Chris 01/15/2012
15 15
Chris 01/22/2012
35 50


Any help is greatly appreciated.

Thanks
Sam

 

[GS]

Sam was thinking very hard :

I was hoping someone could help me with a running total question.

I need to keep a running total of hours that someone uses for
disability. Names will always be sorted and together but everyweek
another entry could be made and new names added. I just cannot for the
life of me figure out how to do this. I do not want to use subtotal
because I don't want to add extra rows nor will this work well since
new information is constantly addded.

Column A (Name) Column B (Week Of) Column AC (Hours) Column
AD (Running Total)
Sam 01/01/2012
40 40
Sam 01/08/2012
18 58
Sam 01/15/2012
20 78
Chris 01/15/2012
15 15
Chris 01/22/2012
35 50


Any help is greatly appreciated.

Thanks
Sam

The logic to a running total is fairly simple once you figure it out.
The basic math is...

=PreviousRow+ThisRow

...where PreviousRow is a fully relative defined name that refs the same
column of the previous row, AND where ThisRow is a
column-absolute/row-relative defined name that refs Hours column of the
row using the name in a formula.

The formula will always start in the 2nd row of data for each name and
be copied down to each subsequent entry for that name. Using your
example...

Name <> Week of <> Hours <> Running Total
Sam <> 01/01/2012 <> 40 <> =ThisRow (returns 40)
Sam <> 01/08/2012 <> 18 <> =PreviousRow+ThisRow (returns 58)
Sam <> 01/15/2012 <> 20 <> =PreviousRow+ThisRow (returns 78)
Chris <> 01/15/2012 <> 15 <> =ThisRow (returns 15)
Chris <> 01/22/2012 <> 35 <> =PreviousRow+ThisRow (returns 50)


Defined Names defs:

PreviousRow:
Select AC2

In the Define Name dialog's namebox type 'sheetname'!PreviousRow
(Substitute the actual sheetname between the apostrophes)
Syntax convention used here is to wrap the sheetname in apostrophes
followed by the exclamation character and the Defined Name.

In the RefersTo box type =AC1 and click the 'Add' button.


ThisRow:
Back to the namebox and type 'sheetname'!ThisRow

In the RefersTo box type =$AC2 and click the 'Add' button.
Close the dialog.

Your defined names are now ready to be used as outlined above.

HTH

 

[joeu2004]

I need to keep a running total of hours that someone uses for
disability. Names will always be sorted and together but everyweek
another entry could be made and new names added.

[....]
I just cannot for the

Column A (Name) Column B (Week Of) Column AC (Hours) Column
AD (Running Total)
Sam 01/01/2012
40 40
Sam 01/08/2012
18 58
Sam 01/15/2012
20 78
Chris 01/15/2012
15 15
Chris 01/22/2012
35 50

It is easiest if you have one blank row above the first name. Thus, suppose
the names start in row 2. Put the following formula into AD2 and copy down
the column:

=IF(A2="","",IF(A2<>A1,AC2,AC2+AD1))

If you cannot have a blank row, then simply enter the correct data into
first row, and enter the above formula starting in the next row.