# Help with monthly average

#### edgarsrom

##### New Member
Hello all,

after hours spent trying to find a solution online, which was unsuccessful, I decided to post a new thread and see if anyone could help me out. I have multiple pivot tables connected to timeline slicer where user can select time period to view data set. I found online how to get selected MIN/MAX date from timeline selection and put them into individual cells. Then I found another formula to list out all months between MIN/MAX dates and input them into individual cells. Now my problem is to average numbers based on selected months from pivot table. I am looking to average range based on range basically(if month appears in list of cells then take it into account).

For example:

User adjusts timeline slicer for Jan 2019 - Sep 2019, so pivot updates data set just for this time period:
Pivot table example:
 Jan 10 Feb 20 Mar 30 Apr 0 May 10 Jun 20 Jul 30 Aug 40 Sep 50 Oct 0 Nov 0 Dec 0

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Timeline MIN/MAX date range(formula):
MIN = 01/01/2019
MAX = 30/09/2019

Selected months example(formula):
 Jan Feb Mar Apr May Jun Jul Aug Sep

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

Now, as I mentioned above - I am able to grab each month from timeline selection into individual cells, so I know that based on user selection I need to average Jan, Feb, March...Sep. I have put some zeroes in example above for a reason - those figures need to be averaged as well, but Oct, Nov, Dec needs to be ignored as they do not fall into MIN/MAX selection.

If I just to an average function on entire range(eg. B2:B13) it would return 17.5 - because it is also taking into account last 3 months which are not selected.

Result I am trying to get is 23.3 - which would be range B2:B10. Or if user would select Jan - Oct then result should have been 21(including 0 value in Oct as well).

Hope it does make sense.

Any help much appreciated!

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### FormR

##### MrExcel MVP
Result I am trying to get is 23.3

Hi, something like this maybe..:

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### edgarsrom

##### New Member
Hello FormR,

this is exactly what I am looking for! Thank you very much for this perfect solution, much appreciated!

Replies
1
Views
85
Replies
5
Views
77
Replies
22
Views
146
Replies
1
Views
36
Replies
10
Views
167