Removing highest and lowest values when averaging 1

[drm184]

Is it possible to remove the highest and lowest value when you are averaging a group of cells in a row? If so, how? I've tried several different formulas, but most didn't work cause I had too many arguements in them. Thanks for the help!

 

[drm184]

I figured it out, but in case anyone is interested here is how I did it: As it is I had an average column where it only averaged the cells in the row that had a value other than zero (so that took a SUM (cells)/COUNTIF (cells, ">0&quotstatement). In order to average them without the highest and lowest I used the following equation: =SUM(S47:AJ47)-MAX(S47:AJ47)-(SMALL(S47:AJ47,(COUNTIF(S47:AJ47,"=0&quot+1))))/(COUNTIF(S47:AJ47,">0&quot-2)

I had to take the smallest integer besides the zeros, hence the small function, and the countif determines the k-th value it will find (notice I added +1 so i wouldn't just take the last zero it found). On the last countif I had to subtract it by two since I was subtracting two integers up top.

Just thought I would let anyone know how I solved this in case they needed this for future reference. I'm sure there is an easier way but this is how I was able to get it to work.

 

[DaveStL]

Based on your original question, "Is it possible to remove the highest and lowest value when you are averaging a group of cells in a row", I came up with:

(SUM(A17:I17)- (MAX(A17:I17)) - (MIN(A17:I17))) / 7

This assumes that:
1. The number of cells in the calculation will remain constant (7).
2. The maximum and minimum values will be "removed" only once regardless of how many cells contain that value.

 

[drm184]

That is what I tried originally but then I realized that the MIN would subtract the zero values that were there. Each row could have no or many zero values, the number of zero's is different for every row. That's why instead of just using the MIN function, I had to use a small function and a countif to determine which small value to subtract. I apoligize for not being clearer in my original post.