# How to Use Microsoft Excel to Calculate Seasonal Indexes

Written by marlon trotsky
• Share
• Tweet
• Share
• Pin
• Email

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:
Moderate

## Instructions

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
##### Sort:
• Most relevant
• Most popular
• Most recent