Excel: Calculate a Rolling Average Based on Dynamic Range

Question

I am trying to calculate rolling average based on a dynamic range. To give you an example, my data-set looks like the one below. I am trying to calculate the rolling average of the parameter "No. of Users" based on the range "Box no." (12, 13, 14...). Usually, one can calculate rolling average by calculate average for first N rows and then drag it down. However, the issue here is no. of users for the Box no. column vary and therefore I can't use the traditional rolling average method.

Data-set (consider all the values in different row under the column Box No. and Users)

Box No. = 12, 12, 12, 13, 13, 14, 14, 14, 14, 14

Users = 5, 5, 8, 6, 8, 10, 8, 3, 5, 1

So, I am looking for a result like this.

Result (consider all the values in different row under the column Box No. and Rolling Average for No. Users)

Box No. = 12, 13, 14

Rolling Average for (No. of Users) = 6, 7, 5.4

Any help would be appreciated.

Answer 1

There are two parts to this.

PART 1

To address the dynamic range, I suggest that you use a Table. If your data set is this:

then click in the dataset and select Table from the Insert command tab. Make sure you have My table has headers selected.

The result should look like this:

Colors can vary based on your version and setup.

PART 2

To get your averages based on this table, click in the table and select Pivot Table from the Insert command tab. Accept the defaults and you will end up on new sheet with a blank Pivot Table.

Drag the Box No. field down to the Rows block and the Users field down to the Values block. Click the arrow to the right of the User name in the Values block and select Value Field Settings... Then select Average in Summarize Values By. You can also rename the displayed field name in Custom Name:.

Note: After adding data, right click in the Pivot Table and select Refresh to see the new data.