Using =YEARFRAC for week math

[JohnBF]

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.

[sdckapr]

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

[JohnBF]

[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?

[sdckapr]

=(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

[JohnBF]

[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.