Excel 2007, Help with Running Totals

Excel Excel 2007
May 20, 2010 at 14:57:55
Specs: Windows Vista
Hello All:

I am tracking my finances through a spread sheet. In this spread sheet I have 24 tables. The format is broken out into a table for credits and expenses for each month plus one cell for the previous year balance for credits. The tables are identical WRT formatting with one exception, one column in each table labeled Credit and Expenses. Table 1 is formatted (headers) as such C7 (Date) D7 (Expense) E7 (Amount) F7 (Running Total), the data begins on row 8. Table 2 is formatted (headers) I7 (Date) J7 (Credit) K7 (Amount) L7 (Running Total), the data begins on row 8.

I am trying to see if it is possible to automate the calculations in the “Running Total” Cells for both of the expense and credit tables. That way if I need to make any additions or subtractions to the tables, I can use the sort function on the “date” column without having to manually repopulate the logic.

See More: Excel 2007, Help with Running Totals

May 20, 2010 at 18:51:00
What do you mean by "automate the calculations in the “Running Total” Cells"?

Obviously formulas "automate the calculations" so you must means something different.

Please explain further, maybe with some examples of "before" and "after...i.e. inputs and expected outputs.

Report •

May 21, 2010 at 09:05:38
My apologies, more details below:
General explanation of table lay out (All tables are the same with different names)
All Tables are 5 Columns wide by 21 rows long.
- The first row are header cells for the columns (Blank (allows for a three color conditional format for each row), Date, Expense, Amount, Running Total)
- Data is populate within the next 20 cells
Table 1 (JAN_Expense)
C8, 1/1/2010 D8, Mortgage E8, -100.00 F8, Manually entered logic
C9, 1/1/2010 D9, HOA E9, -10.00 F9, Manually entered Logic
C10, 1/4/2010 D10, Credit Card E10, -50.00 F10, Manually entered Logic
C11, 1/14/2010 D11, Loan E11, -75.00 F11, Manually entered logic
Table 2 (JAN_Credit)
I8, 1/1/2010 J8, Dividend K8, 10.00 L8, Manually Entered logic
I9, 1/1/2010 J9, Pay Check K9, 10.00 L9, Manually entered Logic
I10, 1/12/2010 J10, Expense Report K10, 10.00 L10, Manually entered Logic
I11, 1/24/2010 j11, Pay Check K11, 10.00 L11, Manually entered logic

What I mean by automate the functions (for the running total) is this:
Cell L4 = 1000.00 (This is a static entry (Manual) and is the my balance form the previous year)
Currently, I have to manually add cells together based on the date of either the expense or credit. Below is how the cell is currently:
L8 = L4+K8
L9 = L8+K9
F8= L9+E8

Now if I want to say add an expense dated 1/6/2010, I would have to manually redo all of the additions for the “Running Total” columns. I was hoping that I could have some sort of logic that would do the date comparison and add the specific “values” according to that date and output it to the “Running Total” column.

I like to visually see the difference between the expense and credits. I have put them all in one table before with conditional formatting to highlight an expense verse a credit, but it wasn’t as easy to read as two separate tables. If this is not possible in excel, then I can live with either one table for both or having to manual redo the logic…

Either way, thanks for all your help!

Report •

May 21, 2010 at 11:23:21
I must be missing something.

Having 4 different running totals for the same date doesn't make sense to me.

If I understand your explanation, when January 1 is over, you have $910. I don't see that value anywhere in your tables.

Maybe I've got a formula misplaced or something.

I see these values for January 1:

Expense table:


Credit table:


What am I missing?

Report •

Related Solutions

May 21, 2010 at 14:49:48

Thanks for all of you help. I believe that what I was trying to accomplish is not possible in excel.

So I have consolidated the "expense" and "credit" table into a single table.

Report •

May 21, 2010 at 16:30:18
I'm not sure it not possible because I still don't know what you are trying to accomplish.

I think you would be surprised what can be done with Excel, especially with nested formulas, lookup tables and VBA.

Report •

Ask Question