Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-pro· GoogleHugging Face LMArena Human Preference 140K

How do you find the second largest value in a list of numbers in column D? Use the…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-sonnet-4-20250514-thinking-32k’s answer.
  1. User

    How do you find the second largest value in a list of numbers in column D? Use the fastest method.

  2. Gemini Assistant

    Excellent question. The fastest and most direct method to find the second largest value in a list of numbers is by using the LARGE function.

    The Fastest Method: Using the LARGE Function

    This function is built specifically for this task, making it highly efficient for both the user to write and the software to calculate.

    The formula is:

    =LARGE(D:D, 2)
    

    How it Works:

    • LARGE: This is the name of the function.
    • D:D: This is the range you want to search. Using D:D selects the entire column D. If your numbers are in a specific range like D2:D100, you can use that instead for slightly better performance on very large files.
    • 2: This is the "k" value, which specifies you want the 2nd largest number. If you wanted the third largest, you would use 3.

    Step-by-Step Instructions

    1. Click on the cell where you want the result to appear.
    2. Type the formula: =LARGE(D:D, 2)
    3. Press Enter.

    The cell will now display the second largest value from column D.


    Why is this the "fastest" method?

    • Computational Speed: LARGE is a native, highly optimized function in both Excel and Google Sheets. It's designed to find the k-th largest value in a set without needing to sort the entire list first, which makes it very fast even on large datasets.
    • User Speed: It is the simplest and most readable formula for this task. You don't need complex logic, helper columns, or multi-step calculations. It's a single, straightforward function.

    Advanced Scenarios (If your data is not simple)

    Case 1: Handling Duplicates (Finding the 2nd Largest Unique Value)

    If the largest value appears more than once (e.g., 100, 100, 95, 80), the LARGE(D:D, 2) formula will correctly return 100. If you want to find the largest unique value first (100) and then the next largest unique value (95), you need a different formula.

    For modern Excel (365) and Google Sheets:

    =LARGE(UNIQUE(D:D), 2)
    

    This formula first creates a list of unique values from column D and then finds the 2nd largest from that new list.

    Case 2: Handling Errors in the Data

    If column D might contain error values like #N/A or #DIV/0!, the LARGE function will return an error. To ignore errors, use the AGGREGATE function (available in Excel 2010 and newer).

    =AGGREGATE(14, 6, D:D, 2)
    
    • 14: This tells AGGREGATE to behave like the LARGE function.
    • 6: This is the option to ignore error values.
    • D:D: The range to search.
    • 2: The k-th value to find (the 2nd largest).

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.