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

so on.

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.