Results 1 to 12 of 12
  1. #1
    Star Lounger
    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

  2. #2
    Uranium Lounger
    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.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Star Lounger
    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
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    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)/12-SUM($C$5:C5))/(12-MONTH(C3)),C4)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    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.
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 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:
    =A2-O5

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    2 Star Lounger
    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
    Attached Files Attached Files

  8. #8
    Uranium Lounger
    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.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Star Lounger
    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>

  10. #10
    Star Lounger
    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)/12-SUM(month1:C4))/(12-MONTH(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.

  11. #11
    Uranium Lounger
    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

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Avoiding Circular References (Excel 98/2000)

    Hi Keith,

    For a one-line solution on row 5, in which you overwrite the formula as each month's results are known, you could use:
    =Budget/12
    in C5,
    =(Budget-SUM($C$5:C$5))/(12-COUNT($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
    =(Budget-SUM($B$5:B$5))/(12-COUNT($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 month-based, 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 pro-rata monthly budgets on the number of days in each month. To do this, you could use:
    =(Budget-SUM($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 weeks-based 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.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •