Results 1 to 5 of 5
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    What gives here - why isn't the cumulative sum of the weeks by month in Column E equal to the cumulative calculation in Column F? I can't see where I have made an error. I know it's not a big difference, but I can't see why there should be any difference, and it's throwing dependent annualizing calculations off.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I think when you are doing your monthly numbers you are missing days totaling 12 = 1 day / month . You are not including the days from the last day of the month to the first day of the next month. This gets included (somewhat) in the cum YearFrac.

    For example the difference between Jan1 to Jan 31 is only 30 days... You need to go monthly from Jan 1 to Feb1, to look at the correct difference.

    =YEARFRAC(B2,C2)*52
    and
    =YEARFRAC($B$2,C2)*52

    Should be:
    =YEARFRAC(B2,B3)*52
    =YEARFRAC($B$2,B3)*52

    to make them equivalent and so they total to 1 year.

    [You will Add in B14: 1/1/2010]

    or even:
    =YEARFRAC(B2,C2+1)*52
    =YEARFRAC($B$2,C2+1)*52


    Steve

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='sdckapr' post='777480' date='29-May-2009 11:53']You are not including the days from the last day of the month to the first day of the next month. ...Should be...or even:
    =YEARFRAC(B2,C2+1)*52
    =YEARFRAC($B$2,C2+1)*52[/quote]
    It makes sense that I was using the incorrect "to" date, Steve, but to my surprise all months calculate to 4.333 weeks - why would that be?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =(52 weeks/year ) / (12 months / Year) = 4.33 weeks / Month

    [The yearfrac you are using (with no basis listed) and the corrected calculation uses 30 days/month and 360 days/year so they are all equivalent, each month is exactly 1/12 of a year...]

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='sdckapr' post='777484' date='29-May-2009 12:23']The yearfrac you are using (with no basis listed) and the corrected calculation uses 30 days/month and 360 days/year so they are all equivalent, each month is exactly 1/12 of a year...[/quote]
    Got it, I need the "1' (actual/actual) argument, then they all line up. Thanks.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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