Excel Formulas & Functions For Dummies by Ken Bluttman

Author:Ken Bluttman
Language: eng
Format: epub, pdf
ISBN: 9781119518266
Publisher: Wiley
Published: 2018-11-06T00:00:00+00:00

MIN and MAX return the upper and lower values of the data. What if you need to know the value of the second-highest price? Or the third-highest price?

LARGE and SMALL

The LARGE and SMALL functions let you find out a value that is positioned at a certain point in the data. LARGE is used to find the value at a position that is offset from the highest value. SMALL is used to find the value at a position that is offset from the lowest value.

Figure 9-22 earlier in this chapter displays the top five home sales, as well as the bottom five. Both the LARGE and SMALL functions take two arguments: the range of the data in which to find the value, and the position relative to the top or bottom.

The top five home sales are found with LARGE. The highest sale, in cell F10, is returned with this formula: =LARGE(C$4:C$1000,1). Because the function used here is LARGE, and the second argument is 1, the function returns the value at the first position. By no coincidence, this value is also returned by the MAX function.

To find the second-highest home sales, the second argument to LARGE is 2. Cell F11 has this formula: =LARGE(C$4:C$1000,2). The third-, fourth-, and fifth-largest home sales are returned in the same fashion when 3, 4, and 5, respectively, are used as the second argument.

The bottom five sales are returned in the same fashion by the SMALL function. For example, cell F22 has this formula: =SMALL(C$4:C$1000,1). The returned value, $143,339, matches the value returned by the MIN function. The cell just above it, F21, has this formula: =SMALL(C$4:C$1000,2).

Hey, wait! You may have noticed that the functions are looking down to row 1000 for values, but the bottom listing is numbered as 60. An interesting thing to note in this example is that all the functions use row 1000 as the bottom row to look in, but this doesn't mean there are that many listings. This is intentional. There are only 60 listings for now. What happens when new sales are added to the bottom of the list? By giving the functions a considerably larger range than needed, you’ve built in the ability to handle a growing list.

The labels in cells E10:E14 (#1, #2, and so on) are entered as is. Clearly, any ranking that starts from the top would begin with # 1, proceed to # 2, and so on.

However, the labels in cells E18:E22 (#56, #57, and so on) were created with formulas. The COUNTA function is used to count the total number of listings. Even though the function looks down to row 1000, it finds only 60 listings, so that is the returned count. The #60 label is based on this count. The other labels (#59, #58, #57, and #56) are created by reducing the count by 1, 2, 3, and 4, respectively:

The formula in cell E22 is ="# " & COUNTA(B$4:B$1000).

The formula in cell E21 is ="# " & COUNTA(B$4:B$1000)-1.

The formula in cell E20 is ="# " & COUNTA(B$4:B$1000)-2.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.