Microsoft Office Forums - View Single Post - Average minus outliers?

Thread: Average minus outliers?

View Single Post

Old

01-07-2012, 09:14 AM

Colin Legg Colin Legg is offline

Colin Legg is offline

Colin Legg uses Windows 7 32bit

Windows 7 32bit Colin Legg uses Office 2010 32bit

Colin Legg uses Office 2010 32bit

Office 2010 32bit

Expert

Join Date: Jan 2011

Location: UK

Posts: 369

Colin Legg will become famous soon enough

Default

Hi Mark,

TRIMMEAN() should work.

To get the average from A:A but excluding the largest and smallest outlier:
=TRIMMEAN(A:A,(COUNT(A:A)-2)/COUNT(A:A))

To get the average from A:A but excluding the 2 largest and 2 smallest outliers:
=TRIMMEAN(A:A,(COUNT(A:A)-4)/COUNT(A:A))

Reply With Quote