# Runing balance

Microsoft Excel 2003 (full product)
February 15, 2010 at 11:08:36
Specs: Windows XP
 I'm creating a budget/expense spreadsheet with Excel 2003. I want to create a column at the end of each account which will carry a running balance/accrual. I fill in all 12 months with the budgeted amounts when I create the sheet. I believe I need a macro that will calculate the running balance on a month by month basis as I enter the expenses. I have not been able to do this with a formula. I'm new at using this product and have a very basic understanding of it. I would appreciate any help you could offer.

See More: Runing balance

#1
February 15, 2010 at 11:18:57
 Hi,You should be able to have running totals using ordinary formulas.The SUM() function adds all the numbers in the cells in a range and ignores empty cells. If your monthly data is in columns B to M on row 2, then put this formula in cell N2:=SUM(B2:M2)As the cells B2 to M2 are filled with values, cell N2 will have the latest total.Is this what you were looking for ?Please feel free to provide more information or ask more questionsRegards

Report •

#2
February 15, 2010 at 12:17:51
 Thank you for your reply. Perhaps the following may help to clarify.This is what the account would look like at the end of the year. I found I could get this result using a formula if I waited until the end of the year. Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec AccuralEst 75.00 75.00 75.00 75.00 75.00 75.00 75.00 75.00 75.00 75.00 75.00 75.00 Expense 0.00 0.00 0.00 50.00 0.00 0.00 125.00 0.00 0.00 100.00 0.00 0.00 625.00What I'm trying to achieve is to get a running balance (Accrual) month by month. In other words I would like to see the following in the Accrual as each month goes by: Jan - 75, Feb - 150, Mar - 225, Apr - 250, May - 325, Jun - 400, Jul - 350, Aug - 425, Sep - 500, Oct - 475, Nov - 550, Dec - 625 I found I could get that result by formula IF I entered the Est value each month and not a full year in advance. Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec AccuralEst 75.00 75.00 75.00 75.00 75.00 75.00 75.00 75.00 75.00 75.00 75.00 75.00 Expense 0.00 0.00 0.00 50.00 0.00 0.00 125.00 350.00I'm entering the full year Est values at the beginning of the year, normal procedure for a budget sheet. Looking at the last example above with an Accrual of 350.00 after the July Expense is entered is what I'm trying to achieve. My interpretation of the mechanics to achieve this is as follows: Accrual for current month= previous month Accrual balance + current month Est - current month ExpenseThe only thing I could think of was to find a way to increment my 'month' value. I couldn't/can't see how to do that in a formula. That is why I am trying to determineif a macro is the solution. If it is then I would like to know where I might find one. I hope this helps to make it more clear.

Report •

#3
February 15, 2010 at 19:56:57
 re: The only thing I could think of was to find a way to increment my 'month' value.You actually want to increment the Column reference for each month.Assuming your data looks like this:``` A B C 1 Jan Feb Mar 2 75 75 75 3 50 0 0 ```Try either of these:=SUM(INDIRECT("A2:" &ADDRESS(2,MONTH(TODAY())))) - SUM(A3:L3)The INDIRECT function will build the range to sum by concatenating "A2:" with the cell reference returned by the ADDRESS function.Since it is February, MONTH(TODAY()) will return 2.ADDRESS(2,2) is \$B\$2 therefore you'll get:SUM(A2:\$B\$2)-SUM(A3:L3) in February.SUM(A2:\$B\$3)-SUM(A3:L3) in March.etc.Or:=SUM(INDIRECT("A2:" &CHOOSE(MONTH(TODAY()),"A","B","C","D","E","F","G","H","I","J","K","L")&"2")) - SUM(A3:L3)Similarly, the INDIRECT function will build the address by extracting the column letter via the CHOOSE function based on the current month.Both of these formulae can be adjusted if your table doesn't start in A1.

Report •

Related Solutions

#4
February 16, 2010 at 05:22:17
 Hi,There are several ways to get what you want without resorting to macros.Here is my suggestion:1. Monthly plan/estimate and year to date plan/estimateEnter planned full year expenditure in cell B3In cells C3 to N3 use this formula: =\$B\$3/12Note the \$ signs. Extend the formula by dragging it to cell N4Cell N4 will contain: =SUM(\$C\$3:N3)In cell B4 put this formula:=OFFSET(\$C\$4,0,MONTH(TODAY())-1)B4 will now show the planned or estimated expenditure for the year to date by using todays month number to find the current year to date value.2. Monthly actual and year to date actualEnter monthly actual expenditure in cells C5 to N5In cell C6 put this formula: =SUM(\$C\$5:C5) Note the \$ signs. Extend the formula by dragging it to cell N6In cell B6 put this formula:=OFFSET(\$C\$6,0,MONTH(TODAY())-1)B6 will now show the actual expenditure for the year to date by using todays month number to find the current year to date value.3. Estimated year-end outturnIn cell B7 enter this formula =B6+(B3-B4)It combines year to date actual expenditure with planned/estimated expenditure for the remainder of the yearHere is a sample outputI had actual expenditure for January and March only to show how this returns totals for year to February:``` A B C D E 1 Month Jan Feb Mar 2 Plan 900 75 75 75 3 Plan to date 150 75 150 225 4 Expenses 100 80 5 Expenses to date 180 100 180 180 6 Expected Outturn 930 ```Regards

Report •

#5
February 16, 2010 at 15:01:43
 Thank you for the input and your time and effort for putting it together. It appears I have more than one option for a solution. Since I am trying to keep my worksheet to a maximum of two lines per item for easy reading, I believe I will have to work towards having a sheet with work detail and another as the summary. As I mentioned, I'm a novice with Excel so I will spend some time trying various approaches towards that goal. I may be back to get more assistance before this is all done. Again, thank you.

Report •

#6
February 16, 2010 at 16:16:22
 re: "a maximum of two lines per item for easy reading"Did you see the suggestions I offered in Response # 3?Both of them allow you to leave your data laid out exactly as I believe you have it. No extra lines are needed.Did I miss something in the example you gave in Response # 2?

Report •

#7
February 16, 2010 at 17:39:25
 Hi DerbyDad03. I tested the Response #3 code. I was unsuccessful in getting the first formula to give me the correct answer. The second formula using CHOOSE seemed to work OK. My worksheet entries do not start in column A, they start in column B. I made the reference changes in the first formula but did not get the correct answer. I made the same changes in the second formula. I'm not familiar with either form of the formula you gave. Can it be 'faked out' as far as the MONTH goes? Is there a way I can test the code for other months of the year?

Report •

#8
February 16, 2010 at 18:36:35

Report •

#9
February 16, 2010 at 19:32:23
 I performed the tests on the formulae again. The results are as I stated, the first formula fails and the second one with CHOOSE succeeds. I may have mislead you when I said I started in column B. I should have also said my row numbers are 3 and 4 for Est and EXP. Here are the formulae as I used them, Please try them out and see what you find. When I did the evaluation, I could see where the first one went wrong but don't understand why.=SUM(INDIRECT("B3:" &ADDRESS(2,MONTH(TODAY())))) - SUM(B4:M4)=SUM(INDIRECT("B3:" &CHOOSE(MONTH(TODAY()),"B","C","D","E","F","G","H","I","J","K","L","M")&"2")) - SUM(B4:M4)Thanks for the hint on date change methods. In order to make things simple, I will opt for the formula change as opposed to a system change.

Report •

#10
February 16, 2010 at 20:46:13
 Correct me if I'm wrong here - I'm guessing that you do not have numerical values in Row 2.I'm making that guess because both of your formulae are incorrect, but since Row 2 does not contain numerical values, it's not causing a problem. My guess is that if you enter a number in B2 or C2, the formula that you think is working (CHOOSE) will give you a different result.Let me explain..We'll start with the CHOOSE formula. Let's look at this section:"B3:" &CHOOSE(MONTH(TODAY()),"B","C","D","E","F","G","H","I","J","K","L","M")&"2"In February, the CHOOSE function will choose C. So far so good.However, the concatenation will give you "B3:C2" so you are actually SUMming B3, C3, C2 & B2. As long as B2 and C2 don't contain numerical values, you'll get the same results as SUM(B3:C3) which is what you really want. Just because you are getting the correct answer doesn't mean the formula is correct.Now, as for the formula that uses ADDRESS, you have 2 things that are incorrect:1 - You are using the wrong value for the row_num argument. You are trying to SUM Row 3, right? I see a 2 being used as the row_num argument. Once again, this won't cause you a problem as long as there are no numerical values in Row 2 - but, just as above, it should be corrected.2 - In Response # 8, I explained how to increment the column_num argument to shift it one column to the right since you start your table in Column B. I do not see the "+ 1" in the formula you most recently posted.

Report •

#11
February 17, 2010 at 08:24:45
 What I sent you were the formulae as I had used them before making your suggested changes. I wanted you to see what I was up to. I waded through the online help, looking for the various parts of the formula, to try and get a better understanding. I tested using the current month and also used other months, per your suggestion. I can now say they finally APPEAR to be successful.I'm including them again for your review. It's apparent to me I will have to get some manuals for Excel and probably take a course. This seems to be a very powerful and complex tool. There is one more thing I want to do with this accrual. At the end of the year I want to carry the balance forward into the next year. Is there a SIMPLE way to achieve this? =SUM(INDIRECT("B3:" &ADDRESS(3,MONTH(TODAY())+1))) - SUM(B4:M4)=SUM(INDIRECT("B3:" &CHOOSE(MONTH(TODAY()),"B","C","D","E","F","G","H","I","J","K","L","M")&"3")) - SUM(B4:M4)

Report •

#12
February 17, 2010 at 13:45:23
 I like the look of those 2 forumulae.

Report •

#13
February 17, 2010 at 22:18:14
 re: At the end of the year I want to carry the balance forward into the next year.Assuming the Accural formula is in N3, the simplest way is =N3.Other than that, you'll need to provide more information as to what you are trying to do.

Report •

#14
February 22, 2010 at 14:03:26
 Thank you for your time, help and patience. It appears I have what I need to create the worksheets I need. Thanks again.

Report •