Median/Average based on a range within a filtered list

coloray13

New Member
Joined
Apr 20, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
I am having an issue finding the right formula to calculate the median/average correctly. I have a number of sales that I filter and need to calculate the median and average for each month, which also changes. As of now, I have tried numerous nested formulas and the Median IF Array formula is the closest one that works with the blank cells, the #NA cells, and/or the #VALUE cells in the filtered list, but it still is not correct.

Here is what I am trying to accomplish:
1. count the number of sales in month 0 (in this case, there is only 1, not 3 - see attached photo)
2. count the number of sales in months 1 thru 24 (aka months back)
3. count the rest of the sales in months 25+
4. get the median and averages for those sales (i.e. if 5 sales sold last month, what is the median of those 5 sales)

The green column in the photo is known to be correct, which is how I know my computations are not.

I have tried AGGREGATE, MEDIANIF, etc.

Any help is greatly appreciated!
 

Attachments

  • excel formula issues I.jpg
    excel formula issues I.jpg
    161.4 KB · Views: 12

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
288
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I made up what I guess your input data looks like. And instead of going to 25, I only went to 9 or more months back.

MrExcelPlayground2.xlsx
ABCDEFG
1Date of SaleAmountMonths backcountmedianaverage
26/1/2020$ 3,7170513431657
36/6/2020$ 4,5911725132585.429
46/8/2020$ 4,3412831842974.125
56/15/2020$ 2,9433719242603
66/22/2020$ 4,4274923872751.667
76/29/2020$ 2,008582401.52743
87/1/2020$ 1,8296103666.53390.3
97/7/2020$ 1,1417737103676.571
107/10/2020$ 3,0448832863133.25
117/15/2020$ 1,5939 or more122993.52944.5
127/21/2020$ 2,118
137/25/2020$ 3,582
148/1/2020$ 1,426
158/4/2020$ 3,576
168/9/2020$ 2,363
178/9/2020$ 2,848
188/11/2020$ 3,200
198/18/2020$ 3,372
208/24/2020$ 4,270
218/29/2020$ 4,011
229/4/2020$ 4,809
239/10/2020$ 2,416
249/12/2020$ 3,710
259/15/2020$ 3,570
269/22/2020$ 3,798
279/27/2020$ 4,130
289/28/2020$ 3,303
2910/2/2020$ 2,543
3010/3/2020$ 4,340
3110/7/2020$ 4,855
3210/11/2020$ 2,119
3310/16/2020$ 2,605
3410/22/2020$ 3,511
3510/22/2020$ 1,149
3610/22/2020$ 4,698
3710/28/2020$ 3,822
3810/29/2020$ 4,261
3911/1/2020$ 2,272
4011/3/2020$ 4,538
4111/10/2020$ 2,035
4211/14/2020$ 2,916
4311/17/2020$ 2,531
4411/21/2020$ 3,563
4511/27/2020$ 1,908
4611/28/2020$ 2,181
4712/2/2020$ 4,809
4812/2/2020$ 1,001
4912/3/2020$ 1,893
5012/9/2020$ 3,601
5112/15/2020$ 1,138
5212/15/2020$ 3,310
5312/21/2020$ 4,431
5412/25/2020$ 2,387
5512/29/2020$ 2,195
561/1/2021$ 1,376
571/5/2021$ 1,260
581/7/2021$ 3,856
591/14/2021$ 1,924
601/21/2021$ 3,545
611/26/2021$ 1,460
621/26/2021$ 4,800
632/1/2021$ 3,663
642/8/2021$ 1,604
652/9/2021$ 3,302
662/12/2021$ 4,313
672/15/2021$ 4,150
682/18/2021$ 1,253
692/22/2021$ 2,442
702/28/2021$ 3,066
713/6/2021$ 3,291
723/7/2021$ 2,513
733/13/2021$ 2,365
743/16/2021$ 3,497
753/21/2021$ 2,292
763/24/2021$ 2,976
773/29/2021$ 1,164
784/3/2021$ 1,159
794/4/2021$ 1,479
804/9/2021$ 1,342
814/10/2021$ 2,962
824/17/2021$ 1,343
Sheet2
Cell Formulas
RangeFormula
E2:E10E2=SUMPRODUCT(--(YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))=D2))
F2:F10F2=MEDIAN(IF((YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))=D2),$B$2:$B$82,""))
G2:G10G2=AVERAGE(IF((YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))=D2),$B$2:$B$82,""))
E11E11=SUMPRODUCT(--(YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))>8))
F11F11=MEDIAN(IF((YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))>8),$B$2:$B$82,""))
G11G11=AVERAGE(IF((YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))>8),$B$2:$B$82,""))
 

Forum statistics

Threads
1,140,925
Messages
5,703,205
Members
421,280
Latest member
Jaycee01

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
Top