Results 1 to 12 of 12

20020911, 18:57 #1
 Join Date
 Mar 2002
 Location
 San Jose, California, USA
 Posts
 82
 Thanks
 4
 Thanked 0 Times in 0 Posts
Avoiding Circular References (Excel 98/2000)
I have to build a budgetary spreadsheet that will be updated throughout the year as actual spend becomes known.
Here's the problem. Lets say I have $12,000 available in a particular category that I account for on a monthly basis. Before the year starts I have $12,000/12 ($1,000) per month in the appropriate cells across the sheet.
After 3 months (lets say), I've spent $3,300. I still have to account for $12,000 at year end, so my revised monthly spend for the remaining 9 months is $12,000/12 (baseline) minus 1/9 the overspend in the first 3 months ($33.33 per month). Also, the over/under spend divisor has to decrease as time goes by  so after 6 months I am dividing the overspend by 6 to apportion to the remaining months.
If I try to do this with formulas, I wind up with a circular reference as each cell (in future months) references to itself in a Total amount that has to be subtracted from the baseline total to generate the incremenat portion for future months. (My brain hurts even trying to explain the circularity).
I can't be the first to want excel to do something like this, can I. Has anyone out there got any neat tricks (or macros etc.) that might take care of this.
Going round in circles
Keith

20020911, 21:06 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Avoiding Circular References (Excel 98/2000)
See if the attachment does what you want.
John ... I float in liquid gardens
UTC 7ąDS

20020911, 21:53 #3
 Join Date
 Mar 2002
 Location
 San Jose, California, USA
 Posts
 82
 Thanks
 4
 Thanked 0 Times in 0 Posts
Re: Avoiding Circular References (Excel 98/2000)
John
Close!! I'd like to be able to see the future months available however, and putting zero in the future spends doesn't add up. See my attachment as a purely graphical explanation.
Keith

20020911, 22:26 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Avoiding Circular References (Excel 98/2000)
[Edited, one day I'll learn to say what I mean.]
If you don't zero the future budgets, they incorporate future months as zero spends, which accelerates the remaining budget. I had thought that through before but didn't know what you were looking for. This is fixed in my earlier attachment by replacing the formula in cell D4 with this, copy right to end of year.
=IF(ISNUMBER(C5),$A$2/12+($A$2*MONTH(C3)/12SUM($C$5:C5))/(12MONTH(C3)),C4)John ... I float in liquid gardens
UTC 7ąDS

20020911, 22:30 #5
 Join Date
 Mar 2002
 Location
 San Jose, California, USA
 Posts
 82
 Thanks
 4
 Thanked 0 Times in 0 Posts
Re: Avoiding Circular References (Excel 98/2000)
John
Closer still  however, there's still an error in the math. The attachment is your modified sheet, with a yearly total added. I've underspent by $600 in the first 4 months and the annual budget total shows 12,666. This figure should remain at 12,000. I'd love to be able to create money this way but sadly it doesn't happen.

20020912, 00:39 #6
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,216
 Thanks
 2
 Thanked 465 Times in 382 Posts
Re: Avoiding Circular References (Excel 98/2000)
Hi Keith,
The problem is in your formula in O4. What your formula is doing is adding up the progressively adjusted monthly averages, which can't be expected to equal the annual budget. For example, if you spent the whole $12,000 budget in the 1st month, your formula would give you only $1,000.
To keep O4 the same as the annual budget you only need:
=A2
Alternatively, to show how much budget is remaining, you need:
=A2O5
CheersCheers,
Paul Edstein
[MS MVP  Word]

20020912, 03:59 #7
 Join Date
 Dec 2000
 Location
 Sault Ste. Marie, Michigan, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Avoiding Circular References (Excel 98/2000)
Keith,
Look at this method. I use it a lot in my job. It uses this formula:
[Budgeted Amount  Amount Spent To Date / Remaining number of months]
Just replace the formula each month with the actual spending and the subsequent months will
average out the remaining available balance.
HTH
Ken

20020912, 13:46 #8
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Avoiding Circular References (Excel 98/2000)
Keith, Macropod's explanation is correct, and I have added a row to show you how "my version" works.
I'm assuming that we think like accountants (I can't help it) so that we are only adjusting the budget months AFTER the most recent spending month.
Therefore, the proof that my formula works is that in the sum of actual expeditures to date plus the sum of the remaining future budget should always equal 12,000, which it does ... in my tests so far.
Let me know if that is not the way you want the logic to work.
KJToo's method is very nice and you should look at it, except that it leaves the remaining budget calculation in the spent row, which is odd to me (sorry, KJToo). I'm going to try to spend some time on his/her version, because I keep thinking there should be a way to simplify my formula.
Hope this is all helping you get where you need to be.John ... I float in liquid gardens
UTC 7ąDS

20020912, 14:18 #9
 Join Date
 Mar 2002
 Location
 San Jose, California, USA
 Posts
 82
 Thanks
 4
 Thanked 0 Times in 0 Posts
Re: Avoiding Circular References (Excel 98/2000)
Thanks to all for helping untangle my tangled brain! <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

20020912, 15:25 #10
 Join Date
 Mar 2002
 Location
 San Jose, California, USA
 Posts
 82
 Thanks
 4
 Thanked 0 Times in 0 Posts
Re: Avoiding Circular References (Excel 98/2000)
Having looked closely at the revised sheet, I find that I can now get the formula to work in a single line, which was my original goal. I named $A$2 as "budget" and $C$4 as "month1" just for clarification and modified the formula in all but the first cell to be
=IF(ISNUMBER(C4),Budget/12+(Budget*MONTH(C3)/12SUM(month1:C4))/(12MONTH(C3)),C4)
This avoids the circular reference by building the sum spent each month throughout the chart. As real $$ are known, I just overwrite the formula with the hard number. Incidentally, the final sum in O5 now works and always shows $12,000.
I guess I could also make this work on a weekly budget, I'd just have to have a hidden row with week numbers in it as there's no Excel formula for getting week numbers like the "MONTH" formula we use here. Easy enough to do once you work out the logic.
Again  many thanks to all loungers for the assistance.

20020912, 17:36 #11
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Avoiding Circular References (Excel 98/2000)
Week numbers are easily derived using formula, see also http://www.cpearson.com/excel/weeknum.htm.
John ... I float in liquid gardens
UTC 7ąDS

20020913, 03:16 #12
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,216
 Thanks
 2
 Thanked 465 Times in 382 Posts
Re: Avoiding Circular References (Excel 98/2000)
Hi Keith,
For a oneline solution on row 5, in which you overwrite the formula as each month's results are known, you could use:
=Budget/12
in C5,
=(BudgetSUM($C$5:C$5))/(12COUNT($C$5:C$5))
in D5 and copy this formula across to N5.
Alternatively, if you could be sure that B5 won't have a number in it, you could
=(BudgetSUM($B$5:B$5))/(12COUNT($B$5:B$5))
in C5 and copy this formula across to N5.
Regarding your musing about the use of week numbers, if your report is going to remain monthbased, you'll have some issues to work through where the month end doesn't correspond with a week end. For example, do you round up/down, and what about the 365th/366th day?
Another approach might be to base your prorata monthly budgets on the number of days in each month. To do this, you could use:
=(BudgetSUM($B$5:B$5))/(DATE(YEAR(C3)+1,1,1)C3)*(DATE(YEAR(C3),MONTH(C3)+1,1)C3)
in C5 and copy this formula across to N5. Also, to make the month dates in row 3 work correctly with this or a weeksbased formula in leap years, you could put the required 1 January date in C3 (as you have now), and the formula:
=DATE(YEAR(C3),MONTH(C3)+1,1)
into D3 and copy this across to N3.
CheersCheers,
Paul Edstein
[MS MVP  Word]