Calculating “Trimmed” Weighted Average

0
votes

Anyone have any ideas on how to create a formula that will calculated a "trimmed" weighted average?

For example: I have 10 different numbers with their own weights. How do I go about creating a formula that calculates a weighted average of only the 8 middle numbers (highest and lowest value removed).

Thanks in advance for everyone's input - and happy holidays.

Example: Jen: $5 with a weight of 20% Harrison: $6 with a weight of 25% Ford: $1 with a weight of 30% Bill: $10 with a weight of 25%

Expected output = I would want the Weighted average of Jen & Harrison since Ford and Bill have the highest/lowest value.

1
excelexcel-formulaaverageweighted-average
Please mock come data and expected output. Then edit your post to include that mock up. – Scott Craner
Do you calculate "middle" based on weights or just the numbers? – L. Scott Johnson

1 Answers

0
votes

Use this array formula:

=AVERAGE(IF((A1:A4>MIN(A1:A4))*(A1:A4<MAX(A1:A4)),A1:A4*B1:B4))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here