What method of averaging would you use?

[rise206]

In excel, I have a column of numbers 1,2,3,4,5 and prices $9.50, 10.50, 11.50, 15.00, 27.00 respectively.

Instead of doing an ordinary weighted average (with sumproduct etc), I want to do a weighted average where both extreme numbers (1 and 5) are weighted less than the ones in the middle (2, 3, 4) with 3 being the most weighted. Is there a method and coding for this in excel?

Also, what is this method of averaging called?

Thanks

[Armando Montes]

TRIMMEAN ?
http://office.microsoft.com/en-gb/ex...005209322.aspx

[DonkeyOte]

I think you need to outline what the "weighting" rule is to be based on and the rate you wish to apply to the "outliers".

[Cheeky Charlie]

At school we used to use an inter-quartile mean (like trim-mean but the trim is set at 25%). I don't think what you are describing is a 'standard' mean (http://en.wikipedia.org/wiki/Mean#Examples_of_means)
If you want to define your own new version of mean we can definitely* create the appropriate formulae...

CC

*not really

[rise206]

@Cheeky--Correct, I am not looking for "standard" mean. I am looking for something like a mix between a weighted average and a trim-mean.

@donkey--Weighting rules:
Weighted average between the outer prices (1 and 5) vs. Weighted average between inside numbers (2,3,4). Would doing just that and then averaging those two weighted average make sense or be significant?
Also, what it the numbers skipped like so:
1 ...... $9.50
5 ...... $10.50
10 ...... $12.50
25 ...... $15.00
50 ...... $25.00
250 ...... $50.00

[GreatDanish]

This may not be the most efficient, but this is what I would do.
Add a column with the formula:
=1-ABS(0.5-PERCENTRANK(A:A,A1))
and make this your weight. Then simply sumproduct as normal.

Of course, you could change the magnitude by adding multipliers either in front of the ABS() function or in front of the entire formula.

[Cheeky Charlie]

I think you want to create a function, something like a bell curve, or perhaps a parabola, with zeroes at the points defined by the outer limits of your list - 1 and 5 in your first example, 1 and 250 in your second example. You want to multiply each of your function values ($9.50 etc.) by the value defined by your function, average those multiplied values, then divide by a figure which (somehow) accomodates the multiplication performed before averaging. I've implied my difficulty - how would we calculate the figure by which to divide the combined number to get an appropriate average?

hmm...