Find the two lowest values and subtract them from a total

[speedy18]

I am the secretary of a small bass fishing club in south Mississippi. I
our standings the two lowest weights of fish caught each year ar
dropped from the end of the year total. I have been going through the
manually and finding the two lowest weights for each member an
subtracting them from their total at the end of the year. I would lik
to know if excel can do this for me. All I need it to do is locate th
two lowest weights and subtract their sum from the persons total. Th
way I have it set up is that each member is assigned a row in ou
standings excel sheet. Each tournament that is held is assigned
column. We hold 12 tournaments a year, one for each month. Only 10 o
the tournaments will count in the end of the year standings. I hav
attatched a copy of our standings to this post so that it would b
easier for someone to understand what I am needing excel to do.

Thank You
speed

Attachment filename: standingsdecafterdrops0304.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=45163

 

[Bob Phillips]

Hi Speedy,

If the separate entries are in A1:A100, you want

=SUM(A1:A100,-SMALL(A1:A100,{1,2}))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

Try a function like the following:

=SUMPRODUCT(LARGE(A1:A12,ROW(INDIRECT("1:10"))))

where A1:A12 is the list of values.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tushar Mehta]

Typically, I don't open files. That said, suppose you have the 12
tournament scores in columns B:M and the '10 best' score in column N.
Also suppose that row 2 is the first row with information about a
member.

Then, in N2, enter the formula =SUM(B2:M2)-MIN(B2:M2)-SMALL(B2:M2,2)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions