Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make average function ignore MIN and MAX
Is there a way to get Excel to ignore the max and min of a data range
when calculating the average? If so, is there any way to get it to ignore the two highest and lowest values? The second question is less important but I would really appreciate it if somebody has the answer to my first question- it would be really useful for what I'm trying to do. Thank you! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make average function ignore MIN and MAX
Larry, try this
1. =(SUM(A1:A14)-SMALL(A1:A14,1)-LARGE(A1:A14,1))/(COUNT(A1:A14)-2) 2. =(SUM(A1:A14)-SMALL(A1:A14,1)-SMALL(A1:A14,2)-LARGE(A1:A14,1)-LARGE(A1:A14,2 ))/(COUNT(A1:A14)-4) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Larry4500" wrote in message ups.com... Is there a way to get Excel to ignore the max and min of a data range when calculating the average? If so, is there any way to get it to ignore the two highest and lowest values? The second question is less important but I would really appreciate it if somebody has the answer to my first question- it would be really useful for what I'm trying to do. Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make average function ignore MIN and MAX
a) ignore Min and Max
=(SUM(myrange)-MIN(myrange)-MAX(myrange))/(COUNT(myrange)-2) b) ignore top 2 and bottom 2 =(SUM(myrange)-(LARGE(myrange,1)+LARGE(myrange,2)+SMALL(myrange,1 )+SMALL(myrange,2)))/(COUNT(myrange)-4) This uses a named range but you could replace "myrange" by A1:A100, for example. In all cases be careful with parentheses: (sum - things-to-ignore) / (count - N) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Larry4500" wrote in message ups.com... Is there a way to get Excel to ignore the max and min of a data range when calculating the average? If so, is there any way to get it to ignore the two highest and lowest values? The second question is less important but I would really appreciate it if somebody has the answer to my first question- it would be really useful for what I'm trying to do. Thank you! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make average function ignore MIN and MAX
Perhaps this would be a good application of the TRIMMEAN function.
For a list of numbers in A1:A10 Where you want to exclude the MAX and MIN values B1: =TRIMMEAN(A1:A10,2/10) or...for more flexibility B1: =TRIMMEAN(A1:A12,2/ROWS(A1:A12)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Larry4500" wrote: Is there a way to get Excel to ignore the max and min of a data range when calculating the average? If so, is there any way to get it to ignore the two highest and lowest values? The second question is less important but I would really appreciate it if somebody has the answer to my first question- it would be really useful for what I'm trying to do. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|