LARGE Function in Microsoft Excel
Part 1: Introduction
Definition
The LARGE function in Microsoft Excel returns the k-th most significant value in a data set. This function can select a discount based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score.
Purpose
The LARGE function aims to find the k-th most significant value in a data set or range. This can be useful in various business scenarios where you must rank data or find specific positions within a data set.
Syntax & Arguments
The syntax for the LARGE function is as follows:
LARGE(array, k) The LARGE function has two arguments:
- Array: This is the array or range of data for which you want to determine the k-th most significant value. This is a required argument.
- k: This is the position (from the largest) in the array or cell range of data to return. This is also a required argument.
Return Value
The LARGE function returns the k-th most significant value in a data set.
Remarks
- If the array is empty, LARGE returns the #NUM! Error value.
- If k ≤ 0 or if k is greater than the number of data points, LARGE returns the #NUM! Error value.
- If n is the number of data points in a range, LARGE(array,1) returns the most significant value, and LARGE(array,n) returns the smallest value.
Part 2: Examples
Let’s look at some examples of how to use the LARGE function in a business context. We’ll provide a purpose, data tables and formulas, and an explanation for each model.
Example 1
Purpose: To find the third-highest sales figure from a sales data list.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales | |
| 2 | $5000 | |
| 3 | $7000 | |
| 4 | $6000 | |
| 5 | ||
| 6 | Formula | Result |
| 7 | =LARGE(A2:A4, 3) | $5000 |
Explanation: In this example, we have a list of sales figures in cells A2 through A4. We use the LARGE function to find the third-highest sales figure, $5000.
Example 2
Purpose: To find the second highest number of units sold from a list of units sold data.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Units Sold | |
| 2 | 150 | |
| 3 | 200 | |
| 4 | 175 | |
| 5 | ||
| 6 | Formula | Result |
| 7 | =LARGE(A2:A4, 2) | 175 |
Explanation: In this example, we have a list of units sold in cells A2 through A4. We use the LARGE function to find the second-highest number of units sold, 175.
Example 3
Purpose: To find the highest profit from a list of profit data.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Profit | |
| 2 | $3000 | |
| 3 | $4000 | |
| 4 | $3500 | |
| 5 | ||
| 6 | Formula | Result |
| 7 | =LARGE(A2:A4, 1) | $4000 |
Explanation: In this example, we have a list of profits in cells A2 through A4. We use the LARGE function to find the highest yield, $4000.
Example 4
Purpose: To find the fourth highest expense from a list of expense data.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Expenses | |
| 2 | $800 | |
| 3 | $1000 | |
| 4 | $900 | |
| 5 | $850 | |
| 6 | Formula | Result |
| 7 | =LARGE(A2:A5, 4) | $800 |
Explanation: In this example, we list expenses in cells A2 through A5. We use the LARGE function to find the fourth highest cost, $800.
Example 5
Purpose: To find the second-highest revenue from a list of revenue data.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Revenue | |
| 2 | $12000 | |
| 3 | $15000 | |
| 4 | $13000 | |
| 5 | ||
| 6 | Formula | Result |
| 7 | =LARGE(A2:A4, 2) | $13000 |
Explanation: In this example, we have a list of revenues in cells A2 through A4. We use the LARGE function to find the second highest revenue, $13000.
Example 6: LARGE with IF
Purpose: To find the highest sales figure for a specific product from a sales data list.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | |
| 2 | A | $5000 | |
| 3 | B | $7000 | |
| 4 | A | $6000 | |
| 5 | |||
| 6 | Formula | Result | |
| 7 | =LARGE(IF(A2:A4=”A”, B2:B4), 1) | $6000 |
Explanation: In this example, we have a list of sales figures for different products in cells A2 through B4. We use the LARGE function nested with the IF function to find the highest sales figure for product A, which is $6000.
Example 7: LARGE with SUM
Purpose: To find the sum of the two highest sales figures from a sales data list.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales | |
| 2 | $5000 | |
| 3 | $7000 | |
| 4 | $6000 | |
| 5 | ||
| 6 | Formula | Result |
| 7 | =SUM(LARGE(A2:A4, {1,2})) | $13000 |
Explanation: In this example, we have a list of sales figures in cells A2 through A4. We use the LARGE function nested with the SUM function to find the sum of the two highest sales figures, which is $13000.
Example 8: LARGE with VLOOKUP
Purpose: To find the product name of the second-highest sales figure from a sales data list.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | |
| 2 | A | $5000 | |
| 3 | B | $7000 | |
| 4 | C | $6000 | |
| 5 | |||
| 6 | Formula | Result | |
| 7 | =VLOOKUP(LARGE(B2:B4, 2), B2:C4, 1, FALSE) | C |
Explanation: In this example, we have a list of sales figures for different products in cells A2 through B4. We use the LARGE function nested with the VLOOKUP function to find the product name of the second-highest sales figure, product C.
Example 9: LARGE with AVERAGE
Purpose: To find the average of the three highest sales figures from a sales data list.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales | |
| 2 | $5000 | |
| 3 | $7000 | |
| 4 | $6000 | |
| 5 | $5500 | |
| 6 | Formula | Result |
| 7 | =AVERAGE(LARGE(A2:A5, {1,2,3})) | $6166.67 |
Explanation: In this example, we have a list of sales figures in cells A2 through A5. We use the LARGE function nested with the AVERAGE function to find the average of the three highest sales figures, approximately $6166.67.
Example 10: LARGE with COUNTIF
Purpose: To count how many sales figures exceed the third-highest sales figure from a sales data list.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales | |
| 2 | $5000 | |
| 3 | $7000 | |
| 4 | $6000 | |
| 5 | $5500 | |
| 6 | Formula | Result |
| 7 | =COUNTIF(A2:A5, “>”&LARGE(A2:A5, 3)) | 2 |
Explanation: In this example, we have a list of sales figures in cells A2 through A5. We use the LARGE function nested with the COUNTIF function to count how many sales figures are more significant than the third-highest sales figure, which is 2.
Example 11: LARGE with MAX
Purpose: To find the difference between the highest and the second highest sales figure from a list of sales data.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales | |
| 2 | $5000 | |
| 3 | $7000 | |
| 4 | $6000 | |
| 5 | ||
| 6 | Formula | Result |
| 7 | =MAX(A2:A4) – LARGE(A2:A4, 2) | $1000 |
Explanation: In this example, we have a list of sales figures in cells A2 through A4. We use the LARGE function nested with the MAX function to find the difference between the highest and the second-highest sales figure, which is $1000.
Example 12: LARGE with MIN
Purpose: To find the difference between the smallest and the third largest sales figure from a list of sales data.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales | |
| 2 | $5000 | |
| 3 | $7000 | |
| 4 | $6000 | |
| 5 | $5500 | |
| 6 | Formula | Result |
| 7 | =MIN(A2:A5) – LARGE(A2:A5, 3) | -$500 |
Explanation: In this example, we have a list of sales figures in cells A2 through A5. We use the LARGE function nested with the MIN function to find the difference between the smallest and the third largest sales figure -$500.
Example 13: LARGE with ROUND
Purpose: To find the third highest sales figure from a sales data list and round it to the nearest hundred.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales | |
| 2 | $5000 | |
| 3 | $7000 | |
| 4 | $6000 | |
| 5 | $5500 | |
| 6 | Formula | Result |
| 7 | =ROUND(LARGE(A2:A5, 3), -2) | $5500 |
Explanation: In this example, we have a list of sales figures in cells A2 through A5. We use the LARGE function nested with the ROUND function to find the third-highest sales figure and round it to the nearest hundred, $5500.
Part 3: Tips and Tricks
- Remember that the LARGE function will return the #NUM! Error value if the array is empty or k is less than or equal to 0 or greater than the number of data points.
- You can perform more complex calculations using the LARGE function and other functions.
- Be careful when specifying the k value. Remember that k represents the position from the most significant value, not the smallest.
- If you want to find the smallest value in a data set, you can use the SMALL function, which works similarly to the LARGE function but returns minor matters.