[ad_1]
Calculating Moving Averages in Excel

Using moving averages can help you identify trends in your data. Excel makes it easy to calculate the moving average for any number of months, including the last 3 or 6.

The Simple Moving Average

The simple moving average (SMA) is the most basic type of trend calculation. It’s also one of the most popular. Regardless of the length of the moving average you’re tracking, the basic calculations stay the same.

An Example:

Let’s say you want to find the average sales for your Beanie Hats over the last 3 months, no matter when new monthly sales data is added to your spreadsheet. You can enter the number of months you want to use in cell G6. For example, you might want to track 6 or 9 months at some point. The formula in G5 will then read:

=AVERAGE(OFFSET(C7,COUNT(C:C)-G6,0,G6))

How It Works

Your data is in Columns B and C, with the values starting in row 7. Excel uses the OFFSET function to identify and calculate the moving average. This function returns a range and we want it to always use the last 3 months (or the number specified in G6). To do this, we use the following arguments:

reference, rows, cols, height, width

We tell the OFFSET function to create a range that starts 7 cells below C4 (the first volume cell), and continues for 3 cells down. Excel knows where to start by subtracting the number of months we want to track (3) from the total number of cells with numbers in Column C (which is 10 in this example).

Finally, wrap it all in the AVERAGE function. Excel will now automatically calculate your moving average. To change the number of months, simply update the value in cell G6.

[ad_2]

Ihab Salloum