Summing numbers dropping lowest and highest

[SmartCookie]

Hello,

I'm trying to help a friend who is keeping scores for races. He wants
to drop the lowest and highest scores and sum the rest. This would be
a worksheet with about 15 rows (names) and 10 columns of numbers (10
race scores). Not too big. I'm sure I could do some complicated
nesting function, but is there something more simple?

Thanks!
Michelle Boggs
(e-mail address removed)

 

[Michael]

=SUM(C25:C32)-MAX(C25:C32)-MIN(C25:C32)

Change it to suit your needs

 

[Ron Rosenfeld]

Hello,

I'm trying to help a friend who is keeping scores for races. He wants
to drop the lowest and highest scores and sum the rest. This would be
a worksheet with about 15 rows (names) and 10 columns of numbers (10
race scores). Not too big. I'm sure I could do some complicated
nesting function, but is there something more simple?

Thanks!
Michelle Boggs
(e-mail address removed)

=SUMPRODUCT(LARGE(B2:B17,ROW(INDIRECT("2:"&COUNT(B2:B17)-1))))

will throw out a single highest and lowest score. So 1,1,5,5,9,9 will result
in 1+5+5+9 = 20


=SUMPRODUCT(B2:B17*(B2:B17<>MAX(B2:B17))*(B2:B17<>MIN(B2:B17)))

will throw out ALL of the lowest and highest scores, so 1,1,5,5,9,9 will give a
result of 10.

You might also want to look at TRIMMEAN.
--ron