Average Data without any peaks or outliers

[NNOG28]

I want to do formula for an average of L12W Sales without any outliers or peaks or bottom. What would the formula be?

For example, here is last 12 weeks sales.
I want it to average the sales in between 45-75 not taking 2,3,150,250 into the average.

50

2

150

75

45

48

65

52

250

55

3

60

<tbody>
</tbody>

thank you,

 

[AhoyNC]

Assuming the standard definition of an outlier - value that is more than 1.5 times the IQR smaller than Q1 and greater than Q3.

If you don't have the AVERAGEIFS function in your ver. of Excel, use the formula in D10 that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Data
2	50		Quartile 1	47.25
3	2		Quartile 3	67.50
4	150		IQR	20.25
5	75
6	45		Lower Range	16.875
7	48		Upper Ramge	97.875
8	65
9	52		Average	56.25
10	250			56.25
11	55
12	3
13	60
Sheet