Help with monthly average

[edgarsrom]

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:
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Jan[/TD]
[TD="width: 64, align: right"]10[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

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

Selected months example(formula):
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Jan[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]May[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[FormR]

Result I am trying to get is 23.3

Hi, something like this maybe..:

Excel 2013/2016
	A	B	C	D	E
1
2	Jan	10		Jan	23.33333
3	Feb	20		Feb
4	Mar	30		Mar
5	Apr	0		Apr
6	May	10		May
7	Jun	20		Jun
8	Jul	30		Jul
9	Aug	40		Aug
10	Sep	50		Sep
11	Oct	0
12	Nov	0
13	Dec	0
Sheet1

Cell Formulas
Range	Formula
E2	{=AVERAGE(IF(ISNUMBER(MATCH(A2:A13,D2:D10,0)),B2:B13))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[edgarsrom]

Hello FormR,

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