Find the two lowest values and subtract them from a total
 

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
--
Message posted from http://www.ExcelForum.com

Find the two lowest values and subtract them from a total
 

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)

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

Thank You
speedy

Attachment filename: standingsdecafterdrops0304.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=451631
---
Message posted from http://www.ExcelForum.com/

Find the two lowest values and subtract them from a total
 

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





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

Thank You
speedy

Attachment filename: standingsdecafterdrops0304.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=451631
---
Message posted from http://www.ExcelForum.com/

Find the two lowest values and subtract them from a total
 

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

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

Thank You
speedy

Attachment filename: standingsdecafterdrops0304.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=451631
---
Message posted from http://www.ExcelForum.com/