0

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.

James11
  • 13
  • 4
  • 1
    Please give us some code that you've tried so we have something to work with, and let us know how it didn't work for you. Also a small set of sample data *in the question* will help. – miken32 Aug 22 '18 at 21:18
  • Thanks @miken32. Just realized that image was not working. So I have manually put the values from my data-set table. – James11 Aug 22 '18 at 21:25
  • Possible duplicate of [conditional average in excel](https://stackoverflow.com/questions/40034961/conditional-average-in-excel) – girlvsdata Aug 22 '18 at 23:06

2 Answers2

0

When you say moving average, do you mean a sort of "average if" that is rolling? Your example simply shows a conditional average, not a rolling one.

It seems like your problem could be solved using averageif(). For more complex cases, you can use the formula average() with an if() formula inside that specifies the conditions you want to match so that the average is computed. This becomes an array formula and must be entered as Ctrl+Shift+Enter or equivalent.

Let's say you have your Box data in B1:K14 and your Users data in B2:K14. You'll get your results for Box 12 with both:

{=AVERAGE(IF(B1:K1=12,B2:K2,""))}

and

=AVERAGEIF(B1:K1,12,B2:K2)

Of course, the conditions can be in adjacent cells so that it can easily be extended to different Boxes or even Users.

AVLZ
  • 66
  • 10
0

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:

Dataset

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:

Table

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.

enter image description here

Rey Juna
  • 347
  • 2
  • 12
  • Thanks for the reply. I am able to calculate the average for the different boxes with the help of Pivot table. Actual table has more than 100,000 rows, so this approach works. – James11 Aug 22 '18 at 23:08
  • If this works for you, please mark as answered to clear it out. Thanks. – Rey Juna Aug 22 '18 at 23:12