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.
