Help with monthly average

edgarsrom

New Member
Joined
May 7, 2014
Messages
34
Office Version
  1. 2013
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:
Jan10
Feb20
Mar30
Apr0
May10
Jun20
Jul30
Aug40
Sep50
Oct0
Nov0
Dec0

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

Some videos you may like

Excel Facts

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

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,435
Office Version
  1. 365
Platform
  1. Windows
Result I am trying to get is 23.3

Hi, something like this maybe..:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Jan</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style=";">Jan</td><td style="text-align: right;background-color: #FFFF00;;">23.33333</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Feb</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style=";">Feb</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Mar</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style=";">Mar</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Apr</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">Apr</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">May</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style=";">May</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Jun</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style=";">Jun</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Jul</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style=";">Jul</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Aug</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style=";">Aug</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Sep</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style=";">Sep</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Oct</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Nov</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Dec</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">{=AVERAGE(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">MATCH(<font color="Purple">A2:A13,D2:D10,0</font>)</font>),B2:B13</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

edgarsrom

New Member
Joined
May 7, 2014
Messages
34
Office Version
  1. 2013
Hello FormR,

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

Watch MrExcel Video

Forum statistics

Threads
1,114,535
Messages
5,548,623
Members
410,858
Latest member
RamIndia
Top