Ignore Highest and Lowest Values

[DanUK]

Hi folks,

Anyone know how to go about creating a formula that can be used to ignore (ie not remove but 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

 

[jim may]

Does this help?

Excel Workbook
	A	B	C	D	E	F	G	H
1
2
3
4		Test
5		Scores
6		80.00		94.00	<< Max() For the Highest
7		66.00		63.00	<< Min() For the Lowest			Breaking things down as
8		75.00		13	<< Count - 2			you go through the steps
9		90.00						to get final answer
10		92.00		1,047.00	80.54
11		78.00
12		75.00
13		82.00
14		88.00		80.54	>>>>>>>>>>>>>>>>>>>>>>>			Combined formula per
15		94.00						J Waldenbach (Spelling?)
16		72.00
17		93.00
18		83.00
19		73.00
20		63.00
21
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D6	=MAX(Scores)
D7	=MIN(Scores)
D8	=COUNT(Scores)-2
D10	=AVERAGE(SUM(Scores)-(D6+D7))
D14	=(SUM(Scores)-MIN(Scores)-MAX(Scores))/(COUNT(Scores)-2)
E10	=D10/D8

Excel Workbook
Name	Refers To
Scores	=Sheet1!$B$6:$B$20
Workbook Defined Names

 

[Aladin Akyurek]

Hi folks,

Anyone know how to go about creating a formula that can be used to ignore (ie not remove but 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

1] Looks like...

=TRIMMEAN(A2:A7,2/COUNT(A2:A7))

2] Is this

=SUM(SUM(A2:A7)-MAX(A2:A7)-MIN(A2:A7),MAX(A2:A7)*0.5,MIN(A2:A7)*0.5)/COUNT(A2:A7)

what you re after?

 

[DanUK]

1] Looks like...

=TRIMMEAN(A2:A7,2/COUNT(A2:A7))

2] Is this

=SUM(SUM(A2:A7)-MAX(A2:A7)-MIN(A2:A7),MAX(A2:A7)*0.5,MIN(A2:A7)*0.5)/COUNT(A2:A7)

what you re after?

Yes!! Well, almost... As its a weighted average I need to divide by the sum of the weights.

=SUM(SUM(A2:A7)-MAX(A2:A7)-MIN(A2:A7),MAX(A2:A7)*0.5,MIN(A2:A7)*0.5)/SUM(A2:A7)

Thanks very much everyone who replied

 

[Aladin Akyurek]

Yes!! Well, almost... As its a weighted average I need to divide by the sum of the weights.

=SUM(SUM(A2:A7)-MAX(A2:A7)-MIN(A2:A7),MAX(A2:A7)*0.5,MIN(A2:A7)*0.5)/SUM(A2:A7)

Thanks very much everyone who replied

What would be the desired result: 4.9?