How do I remove the outlier from three numbers then average the remaining two?

[Craigart14]

I have to coordinate testing for freshman comp classes. Each essay is graded by two different professors. If the grades are close together, such as an 80 and an 85, I simply let Excel average the two grades. Sometimes, though, the grades diverge, with the same paper receiving, say, a 70 from one reader and an 85 from another. In these cases, we have a third professor read the paper. Suppose a paper receives a 70, an 85, and a 79. How do I get Excel to drop the 70, then average the 85 and the 79 for the final grade?

I'm hoping this is an easy question . . . .

Craig

 

[vllgv]

Assuming the lowest grade is always the one getting dropped:

Excel 2010
	A	B	C	D	E
1	1	80	85		82.5
2	2	70	85	79	82
Sheet3

Cell Formulas
Range	Formula
E1	=IF(COUNT(B1:D1)=2,AVERAGE(B1:D1),(SUM(B1:D1)-MIN(B1:D1))/2)

 

[Jonmo1]

Try

=AVERAGE(LARGE(B1:D1,{1,2}))

 

[Craigart14]

Assuming the lowest grade is always the one getting dropped:

Excel 2010

	A	B	C	D	E
1	1	80	85		82.5
2	2	70	85	79	82

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E1 =IF(COUNT(B1:D1)=2,AVERAGE(B1:D1),(SUM(B1:D1)-MIN(B1:D1))/2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

It's not always the lowest grade being dropped. If we have two grades in the 70s and one in the 90s, we would drop the one in the 90s.

 

[vllgv]

We need to know how you draw the line.

 

[Craigart14]

Try

=AVERAGE(LARGE(B1:D1,{1,2}))

I think this is it. Thank you!

 

[Jonmo1]

Mine does the same thing vllgv's does. Drops the lowest value.

We need a clearer definition of how you decide which score to drop.

Would it be feasable for you to enter the value to drop in a 4th cell?

Say A1 B1 C1 are the 3 scores.
In D1, put the value you want to drop (if any).

Then you can do
=(SUM(A1:C1)-D1)/2

 

[jamcall]

Building on what vllgv provided, the following determines the mid-point of your range then decides if the middle value is closer to max or min and calulates accordingly (tie will go to the higher score):

=IF(COUNT(A1:C1)=2,AVERAGE(A1:C1),IF((MAX(A1:C1)-MIN(A1:C1))/2>SUM(A1:C1)-2*MIN(A1:C1)-MAX(A1:C1),(SUM(A1:C1)-MAX(A1:C1))/2,(SUM(A1:C1)-MIN(A1:C1))/2))

Cheers,
~ Jim

 

[Jonmo1]

Here's another one..

=AVERAGE(IF(ABS(AVERAGE(A1:C1)-A1:C1)<>MAX(ABS(AVERAGE(A1:C12)-A1:C1)),A1:C1))

Takes the 2 scores that are closest to the average of all 3.
Works automatically if only 2 scores are included.