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.