Find the largest or smallest numbers in a range of data using LARGE and SMALL functions
You have a new report assignment that requires you to do it on a daily basis. You need to identify the top three salespersons and show their individual sales figures. In addition, your boss wants to see the bottom three salespersons and their sales figures too. Oops ! You start figuring out what is the best way to do the report and you come up with the following ways to do it.
Solution 1
You can sort the data by values to find the largest or the smallest numbers. But it gets inconvenience if you need to do the report frequently. In the above example, you need to do the reporting on a daily basis and you know the sales figures can be changed dramatically from day to day. So you need to manually sort the data every day. It will get tedious quickly and be subject to errors if you forget to sort the data, or your backup person incorrectly does it when you are on vacation or sick leave. In addition, if you sort the data every day, your input section will need to be changed every time. That will be double trouble if you try to link the data to certain accounting system to pull the data out automatically. So it’s not an ideal way to do so.
Solution 2
You can write a Visual Basic Module to automate the sorting and find the top and bottom three salespersons. That’s not a bad way, especially if you want to impress your boss and co-workers, but be careful that things may be backfired. If a formula can do the same job, why bother using a VB Module ? Even better, you don’t even need to press a button.
Solution 3
You can use MAX and MIN functions to find the largest or smallest numbers, but the two functions will not return the second and third largest or smallest numbers for you. The MAX and MIN formula will have the syntax as below:
=MAX(range of data)
=MIN(range of data)
Solution 4
This is the recommended solutions. When the data are populated, the report is done. No manual sorting or VB Module is required.
The LARGE and SMALL formula will have the syntax as below:
=LARGE(range of data,1) ← The second argument “1” returns the largest number
=LARGE(range of data,2) ← The second argument “2” returns the second largest number
=LARGE(range of data,3) ← The second argument “3” returns the third largest number
=SMALL(range of data,1) ← The second argument “1” returns the smallest number
=SMALL(range of data,2) ← The second argument “2” returns the second smallest number
=SMALL(range of data,3) ← The second argument “3” returns the third smallest number
You may need to add the INDEX and MATCH functions to identify the names of the salespersons that have the top and bottom sales figures.
