Hello All,
I'm having an issue with getting a formula to work right. I've tried to change several things, but nothing seems to help. I've attached my file so you all can view the data and other formulas.
First let me say, I have been following the guide here, http://www.exceluser.com/excel_dashb...lity-sales.htm, using his headers, references and formulas, but my data.
Almost all the way through and everything was going fine. I'm down to the area for entering the formula for the DesSales column. On the page it's under Set Up the Deseasonalized Sales Formulas. I've entered it as directed, and I'm getting the dreaded #REF! error for the months of Feb-Dec and every Jan, it just returns #N/A.
I've tried changing the names of the months, such as typing in Jan instead of 1, and it throws errors everywhere.
Here's what it says about this formula:Set Up the Deseasonalized Sales Formulas
With the Seasonal Index calculated in the table above, we now can deseasonalize our sales as shown in column E of the figure below and as charted near the top of this page.
Enter the formula for the cell shown:
E31: =IF($A31>NumSalesPds,NA(),C31/INDEX(SeasIndex,MONTH(B31)))
The data that supports a rolling forecast of seasonal sales.Copy the formula down the column to cell E103.
The formula in cell E31 deseasonalizes the sales for January, 2009, by dividing those sales by the Seasonal Index for January. The formula in cell E32 divides the sales for February by the Seasonal Index for February, and so on.
The key to this formula is the INDEX function, which uses this syntax:
=INDEX(array, row_or_column_num)
Here, the "array" is the SeasIndex range name. And the row number calculated by the MONTH function. When this function references a date serial number, it returns 1 for January, 2 for February, and so on. We can use MONTH this way in the formula because the first row of SeasIndex has January's data; the second row has February's data, and so on.
Thank you all for your help, I really appreciate it.
Bookmarks