Hello all and welcome back to the Excel Tip of the Week. This week, we have a General User post in which we are revamping TOTW #68, and taking a look at functions for finding the biggest and smallest numbers in a set - MAX, MIN, LARGE, and SMALL. MAX and MIN These are the simplest functions to find the extreme values in a range. They are simple to write: =MAX(range) =MIN(range) Range can be a single range of cells (e.g. A1:A500) or several such ranges separated by commas. Only numbers are considered, and the answer will output as a single number, being the largest or smallest value respectively. These functions care about actual value and not absolute value - so naturally +10 is larger than -10,000. You can also use MAX and MIN to do a simple floor / ceiling for a calculation i.e.: =MAX(calculation,0) will return the calculation if it is positive or 0 otherwise; and =MIN(calculation,0) will return the calculation if it is negative and 0 otherwise. LARGE and SMALL These functions extend the MAX/MIN reasoning, but also can be used to find the 2nd, 3rd, 4th largest / smallest values (and so on). The syntax is a little different: =LARGE(range, k) =SMALL(range, k) Range works like the example above - although you can't use multiple ranges like you can with MAX and MIN - and k determines how far from the extremity to look. So LARGE(range, 1) is exactly equivalent to MAX(range); LARGE(range, 2) would find the 2nd largest value, and so on. Note that LARGE and SMALL don't count separate values as one. If your range is: 100 5 62 100 -450 -66 100 ...then a LARGE with a k of one, two, or three would all return 100. As well as finding e.g. the top 3 items in a range, you can also use LARGE and SMALL to sort data into ascending or descending order using formulas. See the attached file for a demonstration. Previous post - Customising the Ribbon and the Quick Access Toolbar TOTW index Next post - Revisiting ADDRESS, INDIRECT This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.
Large (and Small) are very useful in template reports for picking up, say, the top five sales in a month. Used with Index and Match you can report the amount and, say, the customer or project.
One additional tip, where you have (sales) amounts that might be the same, is to add a column to your data that is the 'amount' plus the row number divided by '100,000'. Apply Large and Match to this column and Index from your original information columns. This should not affect the amounts returned, but the uniqueness of the result will stop Excel returning the same customer multiple times.
Large([Unique Value],3) = 1000.00005
Match(Large(...), [Unique Value]) = 4
Index([Value], Match(...)) = £1,000
Index([Customer], Match(...)) = Customer 4