Purpose of Large Function in Google Sheets:
LARGE Function Syntax:
When you want to find the nth largest value from a range or array, you can use the LARGE function. Below example can shed light into the usage of large formula in Google Doc Spreadsheets.
How to Use Large Function in Google Sheets
Examples to the use of LARGE formula in Google Sheets.
In cell D2 the formula returns the first largest number in the range B2:B5. In D3 and D4 the formula returns the second and third largest values in the range.
But in D5, the formula range is A2:A5 and it returns error. This’s because, the selected range in the formula only contains text values. But when you come down to cell D6, there you can see the range as A2:B5. This range contains both numeric as well as text values. Here large function returns the largest element correctly.
One more thing. In D4, you can see a different use of Large function. When you want to find the nth largest number in non consecutive cells, you can adopt the method used in that cell. Here is another example.
This formula would return the number 300 as it’s the second largest number. Don’t forget to use Curly Brackets to separate the elements with the user defined nth number.
Now the use of Google Sheets Large function with Filter.
When using LARGE with Filter function as above, the formula not only returns the top value in the range but also it returns the other values in the row.
Google Sheets LARGE Function and How It’s Different From MAX
1. Max function can return only the first largest element in the range. But in Large, you can specify the position of the value like first highest, second highest and so on.
2. When you use Large function in a range that contain text values only, it returns “#NUM!” error. But you can use MAX function regardless of the values in the range. If the range in Max contains text values only, it would return 0.
Find N Number of Largest Values (Not Nth)
As I detailed above in example 2, you can use LARGE function with filter in a range to return nth largest value or element. But what about returning largest N number of values. I’m not talking about nth.
In this case, I suggest you to use Query function. See the above example 2 where I’ve filtered the row containing the largest value in C2:C5, that means the maximum qty. sold item.
=query(A1:D5,“Select * order by C desc limit 2”)
This Query formula simply returns two rows containing the first largest sold item and the second largest sold item. That’s all for now. Enjoy!