LinkBack URL
About LinkBacks
Lets say.. I've got 4 numbers..
1000
1200
1300
2400
The average of these four numbers is $1,475. Since 2400 is more than twice the average, I would now like to just find the average of the 3 numbers.. 1000, 1200 and 1300.
Lets say.. I've got 4 numbers..
1000
1200
1300
2400
The average of these four numbers is $1,475. Since 2400 is more than twice the average, I would now like to just find the average of the 3 numbers.. 1000, 1200 and 1300.
Last edited by Tommy1005; 01-25-2013 at 12:17 PM.
If all your numbers are in Column A, try this formula..
=(SUM(A:A )- LARGE(A:A,1))/(COUNT(A:A)-1)
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Or:
=AVERAGEIF(A:A,"<" & MAX(A:A))
However, a more flexible solution is probably:
=AVERAGEIF(A:A,"<" & QUARTILE(A:A,3))
Which will exclude numbers in the 4th quartile, even if they are less than the maximum value.
However, this gets tricky, because what you're trying to do is exclude outliers, so you're asking for a formula which can calculate an average based on numbers greater than twice the average you're trying to calculate.
How important is it for the averaging function to decide for you which values to exclude from the average? The way I like to approach this kind of problem is to add a column of hand entered "weights" to tell Excel which values to include. This might look something like:I prefer to hand enter column B, so that the decision to exclude a data point is mine alone to make. As Andrew-R says, programming a robust algorithm to exclude outliers (before it has been decided that they are outliers) requires some real finesse.
Please Login or Register to view this content.
Originally Posted by shg
Yes, I'd agree - the decision of what constitutes an outlier is often a very subjective one, and highly contextualised. It's easy for Excel to exclude the top (or bottom) x numbers from an average, but fiendishly tricky to actually have it make a rational and supportable decision on the value of x
Did you mean that 2400 was twice the next highest number in the set? In order to get double the average of the group the 2400 would have to be 4000. I have not found a way to do this in one formula as averageif is quite frustrating to work with.
The attached spreadsheet adds another column checking if the cell to the left is less than twice the average. The weigheted average cell than uses that column in an averageif function.
weighted_average(solution).xlsx
You can get an average excluding any number that is more than twice the 2nd highest number with:
=AVERAGEIF(A2:A5,"<" & LARGE(A2:A5,2)*2)
But this only works if there's a single outlier (or multiple outliers with the same value).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks