Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Take the two largest values out of a row of values

0

I have a spread sheet of race results and need to take the two largest values out of each row.  The largest is easy but how do I get the second largest?  

Results could look like this:

competitor 1: 1,3,2,6,4,8,5,2,8

I want to be able to subtract the two largest numbers from the overall total.

Thanks!

Answer
Discuss

Answers

0

You're gonna love how simple this is...

Largest Value:

=LARGE(A1:I1,1)

2nd Largest Value:

=LARGE(A1:I1,2)

3rd Largest Value:

=LARGE(A1:I1,3)

Etc.

Discuss

Discussion

Thanks, that's way to simple.  I've been using Excel for over 25 years and they keep slipping these new functions in without telling me.  I've been doing these results for that long too and at one point I had an array set up but I stopped keeping that close a track for awhile and did the second value manually last year but I knew there had to be an easier way.
baddog1016 (rep: 12) Jul 1, '18 at 7:51 pm
Honestly, it's soo easy to forget the simplest of things, especially if you don't use it often enough.

Also, If this answer solved your problem, please select it as the Answer by clicking the blue Select Answer button under it :)
don (rep: 1989) Jul 2, '18 at 9:43 am
Add to Discussion
0

The formula below will return the sum of all items in the range excluding the top 2.

=SUMIF($F$2:$F$8,"<" &LARGE($F$2:$F$8, 2),$F$2:$F$8)

When ranking, make sure that your data don't permit duplicates. LARGE($F$2:$F$8, 2) will return the second ranking value even if it is equal to the first and/or equal to the third. Therefore the above formula will return the wrong result if 2nd rank is not unique. You wouldn't have that problem with the formula below and you may prefer it for that reason.

=SUM($F$2:$F$8)-LARGE($F$2:$F$8, 1)-LARGE($F$2:$F$8, 2)

To break ties in ranking use the ROW() function in a helper column. Presume the values 1, 3, 2 and 3 in A1:A4 (all integers). In the helper column's row 1 write this formula = A1+ROW()/100 and copy down. The divisor must be a power of 10 which is larger than the number of rows in column A so that the result of ROW()/100 is always < 1. Now the helper columns shows

1.01
3.02
2.03 and
3.04

The two values of 3 in column A are no longer identical in the helper column. Second place is now decided by the location of the competitor in the list. You should use this system if the column to sum is not the column used for ranking.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login