Ignore Highest and Lowest Values

[DanRandles]

Hi folks,

Anyone know how to go about creating a formula that can be used to ignore (ie not remove bu simply set to zero) the highest and lowest values from an array of values..?

Value 1 - 9 (ignore)
Value 2 - 3
Value 3 - 4
Value 4 - 5
Value 5 - 7
Value 6 - 2 (ignore)

Using the above data I would like to calculate an average but I'd like to strip-out the highest and lowest value from the calculation....

Another consideration is calculation of a weighted average value where the highest and lowest value are given a much lower weighting that the other values.

Value 1 - 9 (weight = 0.5)
Value 2 - 3 (weight = 1.0)
Value 3 - 4 (weight = 1.0)
Value 4 - 5 (weight = 1.0)
Value 5 - 7 (weight = 1.0)
Value 6 - 2 (weight = 0.5)

Again - I'd like to determine a weighted average but values 1 and 6 are given differing weights...

Any thought..?

Regards,

Dan

[NBVC]

Try:

=AVERAGE(IF(A1:A6<>MAX(A1:A6),IF(A1:A6<>MIN(A1:A6),A1:A6)))

confirmed with CTRL+SHIFT+ENTER not just ENTER.

NOTE:
This will remove all occurances of the max and/or min value from the list....

[NBVC]

If you meant to average the middle cells excluding the topmost and the bottommost.. then try:

=AVERAGE(INDEX(A1:A6,2):INDEX(A1:A6,COUNT(A1:A6)-1))

[ron2k_1]

Formula 1, could be with:
=(SUM(A1:A100)-MIN(A1:A100)-MAX(A1:A100))/(COUNTA(A1:A100)-2)

What do you consider weighted average. Value x weight factor? If so, do that calculation on another column, and then use that column in the SUM Range (that is, instead of SUM(A1:A100), it can be SUM(C1:C100), this is the column that has the results to your multiplication)

EDIT: Or an array as given by NBVC. With my example you can continue adding values to column A without changing the formula thou

[daddylonglegs]

There is a specific function in Excel to average without "outliers" - TRIMMEAN

You can set it to ignore differing numbers of max/min values, this version will ignore just the largest and smallest

=TRIMMEAN(range,2/COUNT(range))

where range is where your values reside

[NBVC]

Forgot about that one

[Dem0nEyesKy0]

Sounds like just what I was looking for. Will give it a try, thanks everyone.

[FDibbins]

Nice 1 NBVC, that is a really handy function to remember