Sum of middle three figures

[Punxatawny]

Can this be done?

I will have a column consisting of 3, 5, or 7 entries. Regardless of the number entries I want a sum of the middle three values.

EG.
1, 2, 3 = 6 (1+2+3)
1, 2, 3, 4, 5 = 8 (2+3+4)
1, 2, 3, 4, 5, 6, 7 = 12 (3+4+5)

Is there a formula that can do this?

[63falcondude]

Here's one option:

=SUM(IF(COUNT(A1:A7)=3,A1:A3,IF(COUNT(A1:A7)=5,A2:A4,IF(COUNT(A1:A7)=7,A3:A5))))

[63falcondude]

A little shorter:

=SUM(CHOOSE((COUNT(A1:A7)-3)/2+1,A1:A3,A2:A4,A3:A5))

[Punxatawny]

That option would work if the numbers were always in order from least to greatest, but that won't be case. For reference what I have is the results of judges scores for different athletes. The rows are set to a specific judge and columns set to an athlete. Where there are 5 judges I drop the high and low and sum the middle 3. For 7 judges, drop the 2 highest and 2 lowest and add the middle three. Also the middle 3 scores might be exactly the same or all different. I should have explained that in the beginning.

[mjo1983]

I'm sure there's a better way to code this but this will work for you.

=IF(COUNT(A1:A7)=5,
SUM(A1:A7)-SMALL(A1:A7,1)-LARGE(A1:A7,1),
SUM(A1:A7)-SMALL(A1:A7,1)-SMALL(A1:A7,2)-LARGE(A1:A7,1)-LARGE(A1:A7,2))

This will return an incorrect amount if you only register 6 scores, you can avoid that by adding a second if statement to create a custom return for that.

[Punxatawny]

Thank you mjo1983. That's what I needed.

[63falcondude]

Does the formula from post #5 work for instances when there are only 3 numbers?

This works for 3, 5, and 7:

=SUM(A1:A7,CHOOSE((COUNT(A1:A7)-3)/2+1,0,-MIN(A1:A7)-MAX(A1:A7),-SMALL(A1:A7,{1,2})-LARGE(A1:A7,{1,2})))

[Punxatawny]

I took the basic form from #5 and added more IF statements. What I really needed to learn from the question was the existence of the max/min, small/large functions. Once I had that I could make it fit my exact needs. My code is clumsy and cumbersome but it works. The short sweet code you posted would have been better.

[jason.b75]

How about

=SUM(SMALL(A1:A7,INT(COUNT(A1:A7)/2)+{0,1,2}))

Which will work with 3, 5, 7, or more numbers if you want a bigger range.