+ Reply to Thread
Results 1 to 4 of 4

Error in DeSeasonalized Sales Forumla Array

  1. #1
    Registered User
    Join Date
    01-16-2013
    Location
    Oklahoma City, OK
    MS-Off Ver
    Excel 2010
    Posts
    40

    Error in DeSeasonalized Sales Forumla Array

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Error in DeSeasonalized Sales Forumla Array

    SeasIndex
    =Control!$B$8

    When the named range SeasIndex posses only one cell as source then how you can extract 2nd row data from it?

    INDEX(SeasIndex,MONTH(01/02/2009))???


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    01-16-2013
    Location
    Oklahoma City, OK
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Error in DeSeasonalized Sales Forumla Array

    Ahh thank you, that actually gave me the clue I needed.

    I went in and did a Create Name from Selection for the Average Seasonal Index A13 to C25. Then entered the formula and it worked perfectly.

  4. #4
    Registered User
    Join Date
    07-23-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Error in DeSeasonalized Sales Forumla Array

    Hi,

    would you share the actual formula you added/corrected and maybe share the updated workbook. I would appreciate the help since I am reviewing the webpage you referenced.

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Adding a date range to an already complex index array forumla
    By jackifizzle in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-05-2013, 11:50 AM
  2. Replies: 6
    Last Post: 10-19-2013, 04:53 PM
  3. One cell in an array forumla not returning expected values
    By brharrii in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-08-2013, 03:29 PM
  4. Need help! Array Forumla to provide multiple occurences of same vlookup!?
    By reeserobs in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-22-2013, 10:43 PM
  5. [SOLVED] Array Forumla-Help #NUM!
    By Sanj151 in forum Excel General
    Replies: 11
    Last Post: 05-06-2012, 09:22 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1