Average excluding x highest outlying values

[JugglerJAF]

I'm producing a report on sickness absence and have been asked for an average (mean) figure by department.

Some areas are skewed by having a small number of absences over an extremely long term (i.e. one department has a total of 400 days absence across 7 employees, but over 180 days of this is down to one individual absence due to a serious car accident).

What I want to do is to produce the mean average of all the data, but excluding the x highest (and/or lowest) values which may otherwise skew the data.

Ideally, I'd like the average of the middle 50% of the data range. I've tried using the quartile function but that doesn't do what I need.

Example, if the data set was {0, 2, 4, 4, 4, 4, 7, 10, 10, 10, 11, 45} I would want the average of the middle 50% of values {4, 4, 4, 7, 10,10}

Data Inc/Exc
0 exclude
2 exclude
4 exclude
4 include
4 include
4 include
7 include
10 include
10 include
10 exclude
11 exclude
45 exclude

Thanks.

 

[Jon von der Heyden]

I don't suppose that MODE or MEDIAN would yield an acceptable result?

Otherwise perhaps;

Excel Workbook
	A
2	7.14286
3
4	0
5	2
6	4
7	4
8	4
9	4
10	7
11	10
12	10
13	11
14	45
15	30
16	25
Index

Excel 2003

Cell Formulas
Range	Formula
A2	=AVERAGE(OFFSET($A$4,ROUND(COUNTA($A$4:$A$16)/2/2,0),,ROUND(COUNTA($A$4:$A$16)/2,0)))

EDIT: takes average of middle half of range only...

 

[pgc01]

Hi JugglerJAF

Is your table sorted by the number of sickness absence days?

 

[JugglerJAF]

Hi JugglerJAF

Is your table sorted by the number of sickness absence days?

It isn't at the moment, but there's no reason why it can't sorted in that way.

 

[barry houdini]

Ideally, I'd like the average of the middle 50% of the data range.

Use TRIMMEAN function. This formula will average the middle 50% of a range of values in A2:A21

=TRIMMEAN(A2:A21,0.5)

 

[Jon von der Heyden]

Use TRIMMEAN function. This formula will average the middle 50% of a range of values in A2:A21

Blimey I didn't know it even existed! Blows mine right out!

 

[JugglerJAF]

Use TRIMMEAN function. This formula will average the middle 50% of a range of values in A2:A21

=TRIMMEAN(A2:A21,0.5)

Many thanks for this. Exactly what I needed. Like Jon, I never knew this existed. A trawl through Excel's lesser known functions is clearly in order!