How to calculate a rolling year-to-date percentage by quarter as the year progresses
I need to calculate a rolling year-to-date percentage by quarter. I’m not sure how to correctly write the formula, but I think the logic would go something like this.
The first quarter (US federal fiscal year) is easy:
YTD=((SUM(October!F2,November!F2,December!F2)/_
SUM(October!D2,November!D2,December!D2)))
The second quarter should be something like:
If (system date > March 31),
(SUM(October!F2,November!F2,December!F2, January!F2,February!F2,March!F2)/_
SUM(October!D2,November!D2,December!D2, January!F2,February!F2,March!F2)))
If (system date > June 30 etc. (October through July)
If (system date > October 31 etc. (October through September)
This is pretty rough, but I will appreciate any help you can give me.
|