Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Annualizing data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Annualizing data
Yes, most likely: what is your formula? Please post it, and we can
figure out how to make it dynamic for your application. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Annualizing data
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Annualizing data
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Annualizing data
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Annualizing data
Roger,
Thanks again - I'll work on that awhile. In the mean time, I wonder if you wouldn't mind answering another date question for me. I have a column of dates in excel in the following format: 20051209 The normal way of changing date formats (format, cell, date) does not change the format. Is there a way to display these dates in a more traditional format such as: 12/09/2005, or 12/09/05?? Thanks in advance for your help! Julie Young -- Julie "Roger Govier" wrote: Hi Julie With 1/12/05 in A1, =EOMONTH(A1,0) would return 31/12/05, i.e. the end of month December, held in A1. For Yearstart, I was expecting you to either substitute the cell holding the date of your yearstart, or alternatively InsertNameDefineYearstart Refers to 01/01/05. You are getting the #Name error, because yearstart isn't defined as a name ( I should have been more explicit in my first post). So, if EOMONTH(A1,0) returns the last day of January, EOMONTH(A1,11) will return the last date of December. Substitute whatever months you like, but basically it will give you the end of the period 12 months from the start. The formula, when used with TODAY() and whatever your Yearstart is, is automatically defining your multiplication factor. Regards Roger Govier Julie wrote: 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Annualizing data
Hi Julie
Possible the easiest way is to highlight the range of dates DataText to ColumnsFixed WidthNextDate then select YMD from the dropdownFinish In the UK, that gives me 09/12/2005 Repeat the exercise again, on the converted data, but select MDY from the dropdown, and then I get 12/09/2005 If your regional settings are mm/dd/yyyy you may get the answer after the first conversion. Alternatively, the formula way would be =--MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4) Regards Roger Govier Julie wrote: Roger, Thanks again - I'll work on that awhile. In the mean time, I wonder if you wouldn't mind answering another date question for me. I have a column of dates in excel in the following format: 20051209 The normal way of changing date formats (format, cell, date) does not change the format. Is there a way to display these dates in a more traditional format such as: 12/09/2005, or 12/09/05?? Thanks in advance for your help! Julie Young |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
Automatically pasting worksheet data to new worksheet with formulas | Excel Worksheet Functions | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |