Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore MAX and MIN values in a set to calculate average
I have a set of numbers, say in range A1:A10. I've determined that
the maximum and minimum values in this set are outliers and so want to ignore them when calculating the average. I know I can ignore either the max OR the min by using one of these array formulas: =AVERAGE(IF(A1:A10<MAX(A1:A10),A1:A10)) or =AVERAGE(IF(A1:A10MIN(A1:A10),A1:A10)) How could I combine exclusion of both MAX and MIN? Dave |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore MAX and MIN values in a set to calculate average
Try this:
=TRIMMEAN(A1:A10,2/COUNT(A1:A10)) -- Biff Microsoft Excel MVP "Dave F" wrote in message ups.com... I have a set of numbers, say in range A1:A10. I've determined that the maximum and minimum values in this set are outliers and so want to ignore them when calculating the average. I know I can ignore either the max OR the min by using one of these array formulas: =AVERAGE(IF(A1:A10<MAX(A1:A10),A1:A10)) or =AVERAGE(IF(A1:A10MIN(A1:A10),A1:A10)) How could I combine exclusion of both MAX and MIN? Dave |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore MAX and MIN values in a set to calculate average
See the TRIMMEAN function in Excel Help.
Then try something like this: =TRIMMEAN(A1:A10,2/COUNT(A1:A10)) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Dave F" wrote in message ups.com... I have a set of numbers, say in range A1:A10. I've determined that the maximum and minimum values in this set are outliers and so want to ignore them when calculating the average. I know I can ignore either the max OR the min by using one of these array formulas: =AVERAGE(IF(A1:A10<MAX(A1:A10),A1:A10)) or =AVERAGE(IF(A1:A10MIN(A1:A10),A1:A10)) How could I combine exclusion of both MAX and MIN? Dave |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore MAX and MIN values in a set to calculate average
One way:
=TRIMMEAN(A1:A10,0.2) In article . com, Dave F wrote: I have a set of numbers, say in range A1:A10. I've determined that the maximum and minimum values in this set are outliers and so want to ignore them when calculating the average. I know I can ignore either the max OR the min by using one of these array formulas: =AVERAGE(IF(A1:A10<MAX(A1:A10),A1:A10)) or =AVERAGE(IF(A1:A10MIN(A1:A10),A1:A10)) How could I combine exclusion of both MAX and MIN? Dave |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore MAX and MIN values in a set to calculate average
=TRIMMEAN(A1:A10,20%) will eliminate the outlying 20% from your set of 10
data points, so it will lose 1 point from each end of the range. -- David Biddulph "Dave F" wrote in message ups.com... I have a set of numbers, say in range A1:A10. I've determined that the maximum and minimum values in this set are outliers and so want to ignore them when calculating the average. I know I can ignore either the max OR the min by using one of these array formulas: =AVERAGE(IF(A1:A10<MAX(A1:A10),A1:A10)) or =AVERAGE(IF(A1:A10MIN(A1:A10),A1:A10)) How could I combine exclusion of both MAX and MIN? Dave |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore MAX and MIN values in a set to calculate average
And if you want to exclude multiple values of MAX and MIN
(array-entered): =AVERAGE(IF(A1:A10<MAX(A1:A10),IF(A1:A10<MIN(A1: A10),A1:A10))) In article , JE McGimpsey wrote: One way: =TRIMMEAN(A1:A10,0.2) In article . com, Dave F wrote: I have a set of numbers, say in range A1:A10. I've determined that the maximum and minimum values in this set are outliers and so want to ignore them when calculating the average. I know I can ignore either the max OR the min by using one of these array formulas: =AVERAGE(IF(A1:A10<MAX(A1:A10),A1:A10)) or =AVERAGE(IF(A1:A10MIN(A1:A10),A1:A10)) How could I combine exclusion of both MAX and MIN? Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to calculate average value in a row ignoring values below "x" | Excel Discussion (Misc queries) | |||
Calculate the average of numbers, ignoring multiple values | Excel Discussion (Misc queries) | |||
Calculate average and not include zero values | Excel Discussion (Misc queries) | |||
Looking-up Columns w/calc'd Values ONLY to Calculate Average | Excel Worksheet Functions | |||
calculate average in percentage coloumn with #DIV/! (ignore error | Excel Discussion (Misc queries) |