The `LARGE` function in Excel is used to return the k-th largest value from a data set. It’s particularly useful when you want to determine the top n values in a numeric data range. Here’s how you can use the `LARGE` function:
Syntax
LARGE(array, k)
- `array`: This is the range of cells or the array from which you want to find the k-th largest value.
- `k`: This is the position (from the largest) in the array or range of cells of the value to return.
Steps to Use the LARGE Function
- Identify Your Data Range: Select the range of cells that contains the data you are interested in. For example, if your data is in cells A1 through A10, your array will be `A1:A10`.
- Determine the Position k: Decide on the position k for which you want to find the value. For example, if you want the largest value, k would be 1. For the second largest, k would be 2, and so on.
- Enter the Function:
- Click on the cell where you want the result to appear.
- Enter the formula. For example: `=LARGE(A1:A10, 1)` to find the largest value in the range A1 through A10.
- Press Enter: After typing the formula, press Enter, and the k-th largest value will be displayed in the selected cell.
Example
Suppose you have the following values in cells A1 through A10:
- A1: 10
- A2: 34
- A3: 23
- A4: 67
- A5: 45
- A6: 78
- A7: 56
- A8: 89
- A9: 12
- A10: 41
If you want to find the 3rd largest number in this range, you would use the formula:
=LARGE(A1:A10, 3)
This will return `67`, as it is the third-largest value in the specified range.
Things to Note
- If `k` is greater than the number of values in the array, Excel will return a `#NUM!` error.
- The `LARGE` function works only with numeric values.
- If any of the values in the array are non-numeric, they will be ignored by the function.
Unlock Your Potential
Excel
Access
Power BI
