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

Report •

#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 questions

Regards


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 Accural
Est 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.00


What 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 Accural
Est 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.00


I'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 Expense

The 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 determine
if 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/estimate
Enter planned full year expenditure in cell B3
In cells C3 to N3 use this formula: =$B$3/12
Note the $ signs. Extend the formula by dragging it to cell N4
Cell 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 actual
Enter monthly actual expenditure in cells C5 to N5
In cell C6 put this formula: =SUM($C$5:C5)
Note the $ signs. Extend the formula by dragging it to cell N6
In 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 outturn
In cell B7 enter this formula =B6+(B3-B4)
It combines year to date actual expenditure with planned/estimated expenditure for the remainder of the year

Here is a sample output
I 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
re: Can it be 'faked out' as far as the MONTH goes?

Just change the date on your system to any month you like then force a calculation in the worksheet so that the TODAY() function updates. Once you are confident it works, change the system date back to the current month.

Another option is to put a date in a cell, e.g. A5. Then (for testing purposes only) replace MONTH(TODAY()) with MONTH(A5). As you change the date in A5, the formula will evaluate to use the column associated with the month of that date.

re: they start in column B

One of the fun things about Excel is the ability to combine functions and operations to get it to do the things we want. Using functions to determine arguments for other functions is very powerful.

Follow this logic:

ADDRESS(row_num, column_num)

ADDRESS(2, 2) evaluates to Row 2, Column 2, or $B$2

ADDRESS(2, 2+1) evaluates to Row 2, Column 3, or $B$3

ADDRESS(2,MONTH(TODAY())) evaluates to Row 2, Column 2, or $B$2 in February

ADDRESS(2,MONTH(TODAY())+1) evaluates to Row 2, Column 3, or $C$3 in February.

In other words, by letting Today's date determine part of the address, you can perform an operation on that result to adjust for whatever Column (or Row) you want the formula to refer to.

So if your table starts in B1, instead of A1, let's shift everything by one column:

=SUM(INDIRECT("B2:" &ADDRESS(2,MONTH(TODAY()) + 1))) - SUM(B3:M3)

That said, I don't see how the CHOSE function (as written) works if the table starts in Column B. Since it is choosing the Column letter based on the Month, wouldn't it be off by one Column also?

Don't we have to drop the "A" choice since you have any data in Column A?

=SUM(INDIRECT("B2:" &CHOOSE(MONTH(TODAY()),"B","C","D","E",
"F","G","H","I","J","K","L","M")&"2")) - SUM(B3:M3)


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 •

Ask Question