Take out judges scores

P

Pierre

Am totaling scores for each judge for a contest, and each entry is
judged in 3 categories. Need to sum the remaining scores, by tossing
out that judges scores.


We need to disregard all numbers from Judge 2 in the total because they
have the lowest total score.

Judge 1 Judge 2 Judge 3
Red 6 7 5
Blue 9 2 6
Green 7 1 4
TOTAL 22 10 15

Need to remove all of Judge 2's scores from the individual totals of
red, blue and green
because Judge 2 had the lowest total score. (There are actually 6
judges total.)

Answers: Red should be 11, Blue should be 15, and Green should be 11.

TIA for any thoughts.
Pierre
 
T

Toppers

Try (assuming your table is range A1:D5):

=SUM($B2:$D2)-OFFSET($B2,0,MATCH(MIN($B$5:$D$5),$B$5:$D$5,0)-1,1,1)

HTH
 
R

Ron Coderre

Try this:

With your sample data in A1:D5

E2: =SUMIF($B$5:$D$5,"<>"&MIN(B5:$D$5),B2:D2)
Copy that formula down thru E4

One question, though:
What are the rules if more than one judge ties for the minimum score?

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
R

Ron Coderre

Ooops! Typo!

The formula in E2 should be:
=SUMIF($B$5:$D$5,"<>"&MIN(B$5:$D5),B2:D2)

***********
Regards,
Ron

XL2002, WinXP
 
J

Jim Reed

What exactly should happen if 2 or more judges tie for the lowest score? Should all of the scores from the judges tied for the bottom score be thrown out? If not, how do you choose which one?

--

Am totaling scores for each judge for a contest, and each entry is
judged in 3 categories. Need to sum the remaining scores, by tossing
out that judges scores.


We need to disregard all numbers from Judge 2 in the total because they
have the lowest total score.

Judge 1 Judge 2 Judge 3
Red 6 7 5
Blue 9 2 6
Green 7 1 4
TOTAL 22 10 15

Need to remove all of Judge 2's scores from the individual totals of
red, blue and green
because Judge 2 had the lowest total score. (There are actually 6
judges total.)

Answers: Red should be 11, Blue should be 15, and Green should be 11.

TIA for any thoughts.
Pierre
 
R

Ron Coderre

This is almost comical!
I STILL put the dollar signs in the wrong place.

This is the latest in a series of final formulas (sheesh!)
=SUMIF($B$5:$D$5,"<>"&MIN($B$5:$D$5),B2:D2)

***********
Regards,
Ron

XL2002, WinXP
 
P

Pierre

Jim said:
What exactly should happen if 2 or more judges tie for the lowest score? Should all of the scores from the judges tied for the bottom score be thrown out? If not, how do you choose which one?


Jim, I will find out. Thanks for asking. Thanks all for the help with
these replies. I'll report back what works best for this.

Pierre
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top