LARGE(), SMALL() in Advanced Excel

The LARGE() and SMALL() functions in Excel are used to find the largest or smallest values in a data set. They are especially useful when you want to retrieve specific ranks of data, like the top 1, 2, or 3 largest or smallest values in a list. These functions help in data analysis where the focus is on finding extreme values (either high or low).

1. LARGE() Function

The LARGE() function returns the nth largest value in a data set, where n is the position from the largest value. For example, the 1st largest value is the maximum value, the 2nd largest is the second-highest value, and so on.

Syntax:

LARGE(array, n)
  • array: The range of cells or array of numbers from which you want to find the largest value.
  • n: The position from the largest value in the array. For example, 1 for the largest, 2 for the second largest, and so on.

Real-Time Example of LARGE()

Scenario: You are a teacher and want to find the top 3 scores from a list of students' test scores.

        =LARGE(A1:A10, 1)    Result: 95
        =LARGE(A1:A10, 2)    Result: 90
        =LARGE(A1:A10, 3)    Result: 88

Explanation: In this example, the function finds the highest (95), second-highest (90), and third-highest (88) scores from the range A1:A10.

2. SMALL() Function

The SMALL() function returns the nth smallest value in a data set, where n is the position from the smallest value. For example, the 1st smallest value is the minimum value, the 2nd smallest is the second-lowest value, and so on.

Syntax:

SMALL(array, n)
  • array: The range of cells or array of numbers from which you want to find the smallest value.
  • n: The position from the smallest value in the array. For example, 1 for the smallest, 2 for the second smallest, and so on.

Real-Time Example of SMALL()

Scenario: You are analyzing sales data and want to find the 3 lowest sales amounts.

        =SMALL(B1:B10, 1)    Result: 1200
        =SMALL(B1:B10, 2)    Result: 1300
        =SMALL(B1:B10, 3)    Result: 1400

Explanation: The function finds the three lowest sales amounts in the range B1:B10, with 1200 being the lowest, followed by 1300, and then 1400.

Key Differences Between LARGE() and SMALL()

  • LARGE(): Returns the nth largest value in a range.
  • SMALL(): Returns the nth smallest value in a range.

Real-Time Examples with More Context

1. Performance Analysis

In a performance analysis, you might want to identify the top performers and bottom performers based on sales figures. For example, you can use LARGE() to find the top 3 sales performers and SMALL() to find the bottom 3 sales performers. 

        =LARGE(C1:C10, 1)    Result: 10000
        =LARGE(C1:C10, 2)    Result: 9500
        =SMALL(C1:C10, 1)    Result: 2000
        =SMALL(C1:C10, 2)    Result: 2500

This helps you quickly identify the highest and lowest sales figures within the given range of data.

2. Investment Portfolio

If you manage an investment portfolio, you may want to find the best and worst performing stocks in terms of returns. You can use LARGE() to find the top 3 performing stocks and SMALL() to find the 3 worst-performing stocks. 

        =LARGE(D1:D10, 1)    Result: 20%
        =LARGE(D1:D10, 2)    Result: 18%
        =SMALL(D1:D10, 1)    Result: -5%
        =SMALL(D1:D10, 2)    Result: -3%

By using the LARGE() and SMALL() functions, you can efficiently analyze the best and worst performing investments.

Conclusion

The LARGE() and SMALL() functions are extremely useful when you need to extract specific values from a data set based on their relative ranking. Whether you're analyzing sales data, test scores, or financial investments, these functions allow you to quickly identify the highest and lowest values in a list, making your data analysis more efficient.




Advertisement