Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate mean without outliers
I'm trying to assist a client with this. He needs to find the mean of numbers
with the exception of those that are very different from the rest - outliers. Can you give us some ideas of how to do this? He is trying to avoid adding extra helper columns to do this. Thanks. -- maryj |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate mean without outliers
Look up the TRIMMEAN function in Excel Help.
Will that do what you want? -------------------------- Regards, Ron Microsoft MVP (Excel) "maryj" wrote in message ... I'm trying to assist a client with this. He needs to find the mean of numbers with the exception of those that are very different from the rest - outliers. Can you give us some ideas of how to do this? He is trying to avoid adding extra helper columns to do this. Thanks. -- maryj |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate mean without outliers
I'm don't know whether you're trying to impress your client, or avoid
confusing him, but this might work for you. This is array-entered, meaning you press Ctrl+Shift+Enter instead of just Enter, to confirm. =AVERAGE(IF(A1:A7*(ABS(A1:A7-MEDIAN(A1:A7))<5)<0,A1:A7*(ABS(A1:A7- MEDIAN(A1:A7))<5),"")) The idea is to only take the average of values that are within a certain range from the median. If you know what constitutes an outlier relative to the median, the formula above works. In the above, the average of values within 5 of the median, in A1:A7. In other words, if A1:A7 looks like this: 4 5 6 3 7 29 17 The above formula returns the average of the first five values, because 29 and 17 are more than 5 from the median (which is 6). The IF is necessary to avoid averaging in zeros, because AVERAGE ignores blanks. Therefore, replace all instances of A1:A7 with the range to average, and all instances of 5 with the distance from the median. Hope that makes sense. On Sep 6, 5:10 pm, maryj wrote: I'm trying to assist a client with this. He needs to find the mean of numbers with the exception of those that are very different from the rest - outliers. Can you give us some ideas of how to do this? He is trying to avoid adding extra helper columns to do this. Thanks. -- maryj |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate mean without outliers
Sorry! Should be:
=AVERAGE(IF(A1:A7*(ABS(A1:A7-MEDIAN(A1:A7))<5)<0,A1:A7,"")) Previous version works too, but it's redundant. On Sep 7, 10:38 am, iliace wrote: I'm don't know whether you're trying to impress your client, or avoid confusing him, but this might work for you. This is array-entered, meaning you press Ctrl+Shift+Enter instead of just Enter, to confirm. =AVERAGE(IF(A1:A7*(ABS(A1:A7-MEDIAN(A1:A7))<5)<0,A1:A7*(ABS(A1:A7- MEDIAN(A1:A7))<5),"")) The idea is to only take the average of values that are within a certain range from the median. If you know what constitutes an outlier relative to the median, the formula above works. In the above, the average of values within 5 of the median, in A1:A7. In other words, if A1:A7 looks like this: 4 5 6 3 7 29 17 The above formula returns the average of the first five values, because 29 and 17 are more than 5 from the median (which is 6). The IF is necessary to avoid averaging in zeros, because AVERAGE ignores blanks. Therefore, replace all instances of A1:A7 with the range to average, and all instances of 5 with the distance from the median. Hope that makes sense. On Sep 6, 5:10 pm, maryj wrote: I'm trying to assist a client with this. He needs to find the mean of numbers with the exception of those that are very different from the rest - outliers. Can you give us some ideas of how to do this? He is trying to avoid adding extra helper columns to do this. Thanks. -- maryj- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I identify statistical outliers with excel? | Excel Discussion (Misc queries) | |||
Identifying Outliers | Excel Discussion (Misc queries) | |||
Boxplots with outliers | Charts and Charting in Excel | |||
outliers/histograms | Excel Discussion (Misc queries) | |||
How do I calculate outliers in Excel? | Excel Discussion (Misc queries) |