MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Removing min, max & calculating average


Posted by Ron on December 06, 2001 4:59 PM

I have rows of numbers and need to remove only one min. and one max. and then calculate the average of the numbers that remain. The hitch is that the min/max could be anywhere in the row and some cells may be blank or have a zero.
All numbers will be between 0 and 9, but one row may have seven numbers while another may only have five or six.
For example:
One row could be 8 3 4 3 7 5 6
Another could be 4 2 9 5 6
If there is more than one min or max, only one can be removed.
Thank you for any help you can offer.


Posted by Paul on December 06, 2001 7:24 PM

This assumes the row is 4 and the column is A - Z
=(SUM(A4:Z4)-MAX(A4:Z4)-MIN(A4:Z4))/(COUNT(A4:Z4)-2)

Posted by Mark W. on December 07, 2001 7:06 AM

Just for fun...

{=AVERAGE(LARGE(A4:Z4,ROW(INDIRECT("2:"&COUNT(4:4)-1))))}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

Posted by Mark W. on December 07, 2001 8:29 AM

More fun...

Average just the odd numbers after removing the
min and max values...

{=AVERAGE(IF(MOD(LARGE(A4:Z4,ROW(INDIRECT("2:"&COUNT(4:4)-1))),2)<>0,LARGE(A4:Z4,ROW(INDIRECT("2:"&COUNT(4:4)-1)))))}

Posted by Ron on December 07, 2001 5:20 PM

Re: Thank you

Thank you for your replies. I appreciate it.