Nov 16 2008

Find the 2nd Largest Value in a Range

Excel provides an easy way to list the k-th (first, second, third, etc.) largest number in a range.  We’ll also take a look at the opposite situation where you want to find the 2nd smallest value in a range, for example.

Large & Small Formulas
So let’s go ahead and take a look at the formulas:
 
[ad#in-post-ad]

=Large(range, k)

where range is the list of number you want to find the values of, and k is the largest number you want to find. Similarly, the Small formula looks like this:

=Small(range, k)

where range is again the list of numbers you want to find the values up, and k is the smallest number you want to find.  Now’s let’s look at a few examples.

Examples

Large and Small Formula Example

Large and Small Formula Example

 

 

 

=LARGE(B2:B6,2) will return 86, since 86 is the 2nd largest number in that range of numbers.

=LARGE(B2:B6,5) will return 12, since it is the 5th largest number in the range (also the smallest in this example)

=SMALL(B2:B6,1) will return 12, since it is the smallest number in the range.

=SMALL(B2:B6,3) will return 45 for the 3rd largest number.

** Excel Hints provides Excel Tips and Excel Help for All Levels.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

11 Comments on this post

Trackbacks

  1. Kevin said:

    AHT (Sec) AHT Ranking Amt of Day At Work
    640 65 5
    699 85 4
    852 117 5
    774 100 5
    737 94 5

    How do i return a value in the first colum as the lowest number if the matching row value is 5 in the third column?

    Thanks,
    Kevin

    January 23rd, 2009 at 1:43 pm
  2. john said:

    This is a tricky formula. Use the small formula in a similar manner to this:

    =SMALL((B1:B10=5)*A1:A10,ROWS(B1:B10)-COUNTIF(B1:B10,5)+1)

    where Column B contains the 4′s and 5′s and column A is the column of data you are trying to get the min from. After you enter this formula, you will have to hit Ctrl+Shirt+Enter to get it to work. Brackets {} will show up surrounding the formula in the formula bar if you have done it correctly.

    Hope this helps,
    John

    January 23rd, 2009 at 3:07 pm
  3. Joe said:

    Hi,
    I have a similar problem,
    How can I find the largest number in a range of numbers in a single col, and delete that number from the cell leaving the rest of the numbers unchanged?
    I will need to put this in a macro.

    I’m having trouble using MATCH, LARGE, ADDRESS.

    Thanks for the help
    JOe

    June 2nd, 2009 at 11:13 am
  4. john said:

    Hi Joe. This doesn’t sound like it would be too difficult to handle with a macro. Would you want to shift up the deleted number or just clear the cell of that value?

    June 3rd, 2009 at 8:46 pm
  5. john Caulfield said:

    Large worked fine but is there a function to sum for example the three largest values in a range of six cells?

    June 11th, 2010 at 4:26 pm
  6. Prasanna said:

    Hi

    I have a problem with a formula. I need to find out the second largest number in a list. For eg. if the numbers are
    apple – 12
    banana – 12
    orange – 11
    I want to list Banana as it is the second largest number after 12.

    Please help which formula to use.
    regards
    Prasanna

    August 16th, 2011 at 10:57 am
  7. john said:

    I would break the data into multiple columns using the text to columns features and sort the multiple columns as you desire.

    August 19th, 2011 at 10:51 pm
  8. RAJEEV said:

    THANK YOU VERY MUCH FOR SOLVING MY PROBLEM..

    June 2nd, 2012 at 2:39 am
  9. Nancy said:

    I would like to find 10 largest customers in total_amount_EQ on the list below

    FULL_NAME TOTAL_AMOUNT_EQ
    CTY CP CHUNG KHOAN PHU HUNG 598,682,000
    BUI THI THU HA 119,736,400
    VU PHUONG THAO 239,472,800
    DANG TRAN HOAN 239,472,800
    LE PHUOC BON 18,037,642,000
    NGUYEN ANH LUONG 18,774,000,000
    NGUYEN THI THUY MINH 12,369,980,000
    NGUYEN VAN HONG 15,957,900,000
    TRAN THI NGOC DIEM 1,877,400,000
    TRAN THI ANH 13,559,000,000
    PHUONG XUAN THAO 6,258,000,000
    NGUYEN VAN TEO 18,774,000,000
    NGUYEN MINH HIEU 5,215,000,000
    PHAM QUANG CHANH 14,602,000,000
    DINH THANH UYEN 13,559,000,000
    VO DUC XUYEN 3,129,000,000
    LE TRUNG DUNG 3,129,000,000
    NGUYEN PHAT THANH 1,877,400,000
    LAM CHI DUNG 3,337,600,000
    CTY TNHH OTO SAI GON 7,509,600,000
    BANH CUONG 2,607,500,000
    DANG THUY NGA 147,921,598
    NGUYEN HOANG PHUC 103,257,000,000

    Thank you

    October 5th, 2012 at 10:20 pm
  10. marc said:

    In a range of cells where numbers can be duplicated and cells can be blank, I need to find the Min and Max values of only the largest 100 unique values. Thanks

    November 10th, 2012 at 2:40 am
  11. Mark said:

    I want to sort a top 10 with a number of columns B,C etc with various %. I want to sort each column with Header A
    A B C
    Ball 100% 100%
    Bat 98% 45%
    Doll 56% 100%
    Toy 100% 100%
    Car 66% 73%
    Train 23% 44%
    Van 100% 65%
    Pram 100% 81%
    Stick 36% 24%
    Chair 100% 100%
    table 88% 59%

    I’ve used LARGE on the numbers and then 1, 2, 3 and so on. I used a vlookup to find number and then give me the item. This is ok until a number is the same.
    How can I put the above in numerical order and then Alpha.
    My formula for some reason on Col B gives me
    Ball 100%
    Ball 100%
    Ball 100%
    Ball 100%
    Ball 100%
    Bat 98%
    Table 88%
    Car 66%
    Doll 56%
    Stick 36%

    I also need the same for Small which would include Train but not Ball

    October 29th, 2014 at 11:38 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives