 |
| Entering Excel's LARGE function |
Most people know about the MIN and MAX worksheet functions to calculate the highest and lowest values in a range.
But what about the ninth highest or the third lowest? That's where you need the functions LARGE and SMALL.
But what about the ninth highest or the third lowest? That's where you need the functions LARGE and SMALL.
The LARGE function
In the illustration above we have a list of countries in column B and a corresponding list of numbers in column C. The formula to calculate the ninth highest number in column C is:
If you then wanted to identify which country in column B that number referred to you would firstly have to find out where the number was in column C and then use that information to refer back to column B. Use the MATCH function to locate the ninth highest number in column C and then the INDEX function to look up the country from column B.
The formula for the match is as follows (presuming the LARGE formula is entered into cell E4):
=MATCH(E4,C:C,0)
The match formula gives a number that locates the value in column C and then you use that number to index column B (presuming that the MATCH formula is entered into cell E6):
=INDEX(B:B,E6)
Of course, you don't have to do three separate formulas, you can combine LARGE and MATCH into one formula like this:
=MATCH(LARGE(C:C,9),C:C,0)
Or do the entire calculation in one like this:
=INDEX(B:B,MATCH(LARGE(C:C,9),C:C,0))
The SMALL function
The SMALL function is much the same as LARGE, for example =SMALL(C:C,3) gives you the third smallest value in column C:C.
The MIN and MAX functions
=SMALL(C:C,1) will give you the lowest value in column C but it's much easier to use the MIN function to calculate the lowest value in a range. =MIN(C:C) gives the lowest value in the column C, similarly, =MAX(C:C) gives the highest value.
Related Posts
Training Courses
If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.
No comments:
Post a Comment