The Large and Small Functions in Microsoft Excel

Using the Large and Small Statistical Functions

It’s pretty straight-forward using the =Max() function to get the highest value in a range of cells or the =Min() function for the lowest value in a range of cells. But what if you need to get the 3rd largest or the 2nd lowest values in a range of cells?

This is where the functions =Large() and =Small() can be of help. Take for example the following table:

Item 1                   23.02
Item 2                     9.51
Item 3                   21.63
Item 4                   12.22
Item 5                    11.7

Max                       23.02
Min                           9.51
2nd Largest         21.63
2nd Smallest        11.7

Now look at another table that displays the formulas for the Max, Min, Large and Small functions:

Item 1                     23.02
Item 2                       9.51
Item 3                     21.63
Item 4                     12.22
Item 5                      11.7

Max                       =MAX(B1:B5)
Min                        =MIN(B1:B5)
2nd Largest       =LARGE(B1:B5,2)
2nd Smallest     =SMALL(B1:B5,2)

Notice the Max and Min functions display the B1:B5 range of cells. Notice the Large and Small functions have the same B1:B5 range of cells, followed by a comma, and the number 2 which displays the second largest and second smallest values within the cell range of B1 through B5 to be shown.

Try using these functions with sample data and see if this brings another look into your collected data.

Keep this article and more Microsoft Excel tips at your fingertips. Bookmark Your MS Excel Trainer or make Your MS Excel Trainer a Favorite.

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

Originally posted 2009-08-04 06:02:47. Republished by Blog Post Promoter