I often compare sales data from the current year to the past year. When I do
this, I take the YTD figures and annualize using a constant. Each time I go
back I have to put in a new constant depending on the date.

Is there any kind of equation that will take a figure and annualize it based
on whatever day the file is open?
--
Julie

Yes, most likely: what is your formula? Please post it, and we can
figure out how to make it dynamic for your application.

Thank-you for your response. I received two responses, so I'll just repeat
to you what I did to him. I'm not sure how I'm supposed to do this. Anyway:

The data comes from our company accounting
system that changes by the minute. I run a query for Year to date parts
sales by customer and the data that is dumped into excel is every part that
has been sold to each customer YTD up to that moment. When running the
report today, I multiply every YTD sales figure by the constant 1.064 which I
calculate by dividing 365 by 343, which is the number of days of sales that
YTD represents. If I were to run the report on July 1, I would multiply by
2, etc.

--
Julie


"Dave O" wrote:

Yes, most likely: what is your formula? Please post it, and we can
figure out how to make it dynamic for your application.

Hi Julie

How is your previous year's data held?
If you have a collection of sales with all of their individual dates, then
it would be easy enough to use Sumproduct for the range of dates that
corresponds to this year's year to date. You would then have a like for like
period comparison of actual data.

If you are adjusting this year's figures to scale them up to a full year,
are you just multiplying this year's figures by a ratio of Year/Year to date?
If so, then the multiplication factor would just need to be set to

=1/((TODAY()-yearstart)/(EOMONTH(yearstart,11)-yearstart))
provided your yearstart is the first day of a month. If it isn't post back
and the formula can be amended.

EOMONTH is a feature from the Analysis Toolpack
ToolsAddinsAnalysis Toolpak

Regards

Roger Govier


Julie wrote:
I often compare sales data from the current year to the past year. When I do
this, I take the YTD figures and annualize using a constant. Each time I go
back I have to put in a new constant depending on the date.

Is there any kind of equation that will take a figure and annualize it based
on whatever day the file is open?

Thank-you fore your response. The data comes from our company accounting
system that changes by the minute. I run a query for Year to date parts
sales by customer and the data that is dumped into excel is every part that
has been sold to each customer YTD up to that moment. When running the
report today, I multiply every YTD sales figure by the constant 1.064 which I
calculate by dividing 365 by 343, which is the number of days of sales that
YTD represents. If I were to run the report on July 1, I would multiply by
2, etc.

I went to toolsaddinsAnalysis Toolpak, then tried your equation in place
of my constant. I got an error message that read #NAME?. I don't really
understand the equation - what does the 11 mean?
--
Julie


"Roger Govier" wrote:

Hi Julie

How is your previous year's data held?
If you have a collection of sales with all of their individual dates, then
it would be easy enough to use Sumproduct for the range of dates that
corresponds to this year's year to date. You would then have a like for like
period comparison of actual data.

If you are adjusting this year's figures to scale them up to a full year,
are you just multiplying this year's figures by a ratio of Year/Year to date?
If so, then the multiplication factor would just need to be set to

=1/((TODAY()-yearstart)/(EOMONTH(yearstart,11)-yearstart))
provided your yearstart is the first day of a month. If it isn't post back
and the formula can be amended.

EOMONTH is a feature from the Analysis Toolpack
ToolsAddinsAnalysis Toolpak

Regards

Roger Govier


Julie wrote:
I often compare sales data from the current year to the past year. When I do
this, I take the YTD figures and annualize using a constant. Each time I go
back I have to put in a new constant depending on the date.

Is there any kind of equation that will take a figure and annualize it based
on whatever day the file is open?