imposing a limit on which values to use in averaging

[smacomb]

I have a data set, I want an average, but I want it to throw out any value that is greater than 30% different and average the rest

[mehmetcik]

30% less than the max?

30% More than the least

30% less than the next smallest

30% bigger than the next biggest

30% less than the median

30% greater than the median

[smacomb]

thanks, so I am looking for it to throw out any value that is 30% or more different than the average of the high and low value.

[mehmetcik]

I assume your Data set will be in a contiguous range.

Probbably in one column.

[smacomb]

yes and typically there are only 3 values sometimes 4

[mehmetcik]

Ok I have two solutions for you.

The following User Defined Function needs two inputs.

A range and a variance.

In my example the range is G3:G10 and the variance is 30%

Entering MyAverage(G3:G10,30) amywhere on the spreadsheet will return 8.75

I checked this using a helper column H3:H10 to check if the number in column G is in range

Then a Sum of the values in column H divided by a countif of the numbers in column H gave a result of 8.75

Function MyAverage(R As Range, M As Integer)
MyArray = Application.Transpose(R.Value)
Median = (Application.Min(R.Value) + Application.Max(R.Value)) / 2
Count = 0
Sum = 0

For Count = 1 To UBound(MyArray)

If MyArray(Count) >= Median * (100 - M) / 100 And MyArray(Count) <= Median * (100 + M) / 100 Then
Items = Items + 1
Sum = Sum + MyArray(Count)
End If

Next
MyAverage = Sum / Items

End Function