Microsoft Office Tutorials and References
In Depth Information
Analyzing Data with Percentiles and Bins
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, a 2 is entered as the second argument
to LARGE. 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,
respectively, using 3, 4, and 5 as the second argument.
The bottom five sales are returned in the same fashion but by using 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 with 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, we’ve built in the ability to handle a growing list.
It’s interesting to see how the labels were created with regard to this.
The labels in cells E10:E14 (#1, #2, and so on) are just entered as is. Clearly,
any ranking that starts from the top would begin with #1, proceed to #2, and
However, the labels in cells E18:E22 (#56, #57, and so on) were created with
formulas. The COUNT function is used to count the total number of listings.
Even though the function looks down to row 1000, it only finds 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 =”# “ & COUNT(C$4:C$1000).
✓ The formula in cell E21 is =”# “ & COUNT(C$4:C$1000)-1.
✓ The formula in cell E20 is =”# “ & COUNT(C$4:C$1000)-2.
✓ The formula in cell E19 is =”# “ & COUNT(C$4:C$1000)-3.
✓ The formula in cell E18 is =”# “ & COUNT(C$4:C$1000)-4.