Google Sheets LARGE Function and Its Difference With MAX

0
59
Google Sheets LARGE Function and Difference With MAX

Google Sheets LARGE function is better than its MAX function. Both these functions are related to finding the Maximum value in a data range. You can say LARGE is just opposite to SMALL function.

Purpose of Large Function in Google Sheets:

LARGE Function Syntax:

LARGE(data, n)

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.

Example 1:

How to Use Large Function in Google Sheets - Example

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.

=large({100,200,300,400},2)

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.

Example 2

Now the use of Google Sheets Large function with Filter.

large function with filter in google sheets

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!

LEAVE A REPLY

Please enter your comment!
Please enter your name here