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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Result I am trying to get is 23.3

Hi, something like this maybe..:


Excel 2013/2016
ABCDE
1
2Jan10Jan23.33333
3Feb20Feb
4Mar30Mar
5Apr0Apr
6May10May
7Jun20Jun
8Jul30Jul
9Aug40Aug
10Sep50Sep
11Oct0
12Nov0
13Dec0
Sheet1
Cell Formulas
RangeFormula
E2{=AVERAGE(IF(ISNUMBER(MATCH(A2:A13,D2:D10,0)),B2:B13))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Hello FormR,

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

Forum statistics

Threads
1,212,927
Messages
6,110,726
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top