Average without Max and Min Value 4

[genuineblonde]

I would like to calculate the average of a variable length group of numbers eliminating the maximum and mininum values.

 

[ETID]

If your data is in col a then the formula B1 is....


=(SUM(A:A)-MIN(A:A)-MAX(A:A))/(COUNT(A:A)-2)

 

[DaleWatson123321]

This should do it...

=(SUM(data)-MAX(data)-MIN(data))/COUNT(data)

This formula requires that you assign a Range Name (called "data) to the range of cells containing your numbers.

To assign a Range Name.. 1) Highlight the range, 2) Hold down <Contro> and hit <F3>, 3) Enter the name (data), 4) <Enter>.

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

In this case, ETID is RIGHT, and deserves a &quot;STAR&quot;, which I'll gladly supply.

I forgot to deduct the MAX and MIN numbers from the COUNT.

Sooooo, my formula should have read...

=(SUM(data)-MAX(data)-MIN(data))/COUNT(data)-2

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ETID]

Thanks Dale

 

[euskadi]

What if your min and/or max occur more than once, and you want to exclude all occurances of the min and the max?? Then do this:

=(SUM(myrange)-COUNTIF(myrange,MAX(myrange))*MAX(myrange)-COUNTIF(myrange,MIN(myrange))*MIN(myrange))/(COUNT(myrange)-COUNTIF(myrange,MAX(myrange))-COUNTIF(myrange,MIN(myrange)))