Forum Discussion
Solved
Excel newbie
Hi. I’m trying to run a sports type league where competitors are awarded points at the end of every event. 1 point per position, lowest score wins the league. There are 10 events, which are added in the last column, but we can drop our two highest scoring events. So best 8 out of 10. Can anyone come up with a formula which will identify the two largest numbers in a row and subtract them from the column with the total in?? Many thanks.
Mike-
Try using this formula:
=SUM(C4:L4)-LARGE(C4:L4,1)-LARGE(C4:L4,2) Your data should look something like this....
Please see the attached file for further reference. Hope this helps.
5 Replies
Hi, to all!
You can use too (in Matt's File):
=SUMPRODUCT(SMALL(C4:L4,ROW($1:$8)))
or
=SUM(C4:L4,-LARGE(C4:L4,{1;2}))
Blessings!
Mike-
Try using this formula:
=SUM(C4:L4)-LARGE(C4:L4,1)-LARGE(C4:L4,2) Your data should look something like this....
Please see the attached file for further reference. Hope this helps.
- You Sir, are a saviour and a gentleman. The formula works perfectly. Many thanks for your contribution.
Glad you were able to get it working. You're welcome. Please feel free to post back to the community with any other inquiries!
You may consider the function SMALL.
SMALL(A:A,k) means the k-th smallest value in the column A.
