Running Total Question
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 |
Running Total Question
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Running Total Question
"Sam" wrote:
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. |
All times are GMT +1. The time now is 10:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com