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:
<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):
<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!
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!