Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find lowest total of cells in range without sorting | Excel Worksheet Functions | |||
Trying to subtract the two lowest values from a row of numbers | Excel Worksheet Functions | |||
summing a row, remove lowest values from the total | Excel Discussion (Misc queries) | |||
Find lowest 3 values and sum | Excel Worksheet Functions | |||
How do I find the two lowest values in a range? | Excel Worksheet Functions |