Monthly Archives: August 2023

Excel, Graphics

Moving average in Excel

In this blog, I demonstrate four ways to calculate moving averages in Excel: with AVERAGE function, using data analysis, with OFFSET function, and adding moving average as trendline in a graph.

You can count manually the moving average. In the cell B6 we count average A2:A6. In the following the cell B7 the average is counted from A3:A7 and so on.

Another way is to install analysis toolpak in Excel option.

Then select data – data analysis – moving average.

Toolpak include different kinds of tools, but we are concentrating only on moving average.

Input range is the data range. Interval is five, we are taking average for five values. Output range is in B-column next to the values.

Press ok.

Excel created the averages automatically. As interval is five, first four values cannot have averages.

The moving average can also be done with OFFSET function.

In B6, we have an OFFSET function which has the reference in A2, then we do not move anywhere from A2 but just take five steps downwards. Then we activated area A2:A6. The nested function is taking AVERAGE from A2:A6. In the cell, we have a reference in A3 and function works fine.

This way is somewhat complicated but returns the same values as other options.

You can also add moving average into a graph.

Activate the data range and take insert – the graph.

Activate the line and take right mouse button, then select add trendline.

Select moving average and period 5.

As default the average line is quite thin, you can add value in width parameter.

Moving average in the graph.

Excel

Dynamic SUM last five values

You have sales values per day. You need to calculate the sum from last five days. When new values are added, the Excel needs to count only the five last values.

For example, if data is this:

2

3

4

5

6

2

the result is 20 (=3+4+5+6+2).

For the data

2

3

4

5

6

2

1

The result is 18 (=4+5+6+2+1).

First, we need ROW function.

The function returns the row number of the chosen value. A5 is in the fifth row.

We need to find the highest value for ROW function to find the last value in the array.

That we can do with the following statement:

=MAX(IF(A:A=””;;ROW(A:A)))

We are looking for the highest value, we need MAX function. If value in A-column is empty, the MAX should ignore that, if the value is not empty, then MAX is looking for the highest value in A-column. As we did not specify range, but stated just A:A, is the range whole A-column.

Now we should count the sum from rows 5 to row 9. For the step, we need OFFSET function, which I have covered in my earlier blog postings.

Offset-function consists of following arguments:

  1. Reference. Where is the starting point of the formula ? That is always A1 as there is the first value of the array.
  2. Rows. How many steps the formula takes downwards from starting point. We have nine values in the array and last five should be counted, the starting point is the row 5. OFFSET is taking four steps downwards.  
  3. Columns. How many steps the formula takes to the right from A5. We have all the values in one column, the value is therefore zero.
  4. Height: How high is the area starting from A5. Five means the area of A5:A9.

The OFFSET is activating the range A5:A9, nested function SUM is summing the range.

The next step is to change static values into dynamic values.

The arguments are:

  1. Reference is always A1.
  2. Rows. Number of rows is counted as the highest row number in the range minus C4, which is five. Maximum row number is nine, that minus five equals to four. If you would like to sum for example four last values, change C4 to four.
  3. Columns. No need to have other columns, value is always zero in our case.
  4. Height: The range is five last numbers from C3.

Let’s do a test to see that the sentence is working.

=SUM(OFFSET(A1;MAX(IF(A:A=””;;ROW(A:A)))-C4;0;C4))

If you want to calculate eg. average for the last five values, just change SUM to AVG.