How to Use Microsoft Excel to Calculate Seasonal Indexes

Written by marlon trotsky
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Use Microsoft Excel to Calculate Seasonal Indexes
Seasonal index numbers should always total 12. (chart background image by Stasys Eidiejus from

A seasonal index is a measure of seasonal variation--a change to a variable, such as profits or sales volume, that occurs on a regular basis. For example, many toy manufacturers notice a bump in sales around holidays like Christmas and Thanksgiving. The index allows the researcher to observe what a data set would look like if you control for seasonal variation. Seasonal indexes can be represented in a graph. Microsoft Excel allows you to determine your seasonal index. After you've entered in all the data in a worksheet, you can complete the following steps to come up with the data set's index.

Skill level:


  1. 1

    Open a new worksheet in your workbook and enter a title in the first row. Label A2 "Month" and B2 "Average." Enter your data into column A.

  2. 2

    Enter the array formula ="AVERAGE(IF((MONTH(Date)=$A3)*(SlsRatio>0),SlsRatio))" into the formula bar for B3. Press "Enter" while holding down "Ctrl" and "Shift" to enter the formula.

  3. 3

    Copy the formula to the range B4:B14 to determine the averages for the rest of the months of the year, by entering =SUM(B3:B14) in cell B15. If the total in B15 isn't exactly 12 you must enter =(12/$B$15)*B3 in cell C3. Then, copy the preceding formula to the range C4:C14.

  4. 4

    Copy the SUM formula {=SUM(B3:B14)} from cell B15 to cell C15. The C column's total should now be 12. Label the C column by selecting the range C2:C14 then simultaneously holding down "Ctrl," "Shift" and "F3." Double-check that only C2 is checked, then press "OK."

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.