Microsoft Office Tutorials and References

In Depth Information

**Using functions that analyze rank and percentile**

COUNT
Tells you how many cells in a given range contain numbers, including dates

and formulas that evaluate to numbers.

●

The A functions

Excel includes a set of functions that give you more flexibility when you’re calculating

data sets that include text or logical values. These functions are AVERAGEA, COUNTA,

MAXA, MINA, STDEVA, STDEVPA, VARA, and VARPA, all of which accept a series of up

to 255 arguments as (
value1, value2, …
).

Ordinarily, the non-A versions of these functions ignore cells containing text values. For

example, if a range of 10 cells contains one text value, AVERAGE ignores that cell and

divides by 9 to arrive at the average, whereas AVERAGEA considers the text value part

of the range and divides by 10. This is helpful if you want to include all referenced cells

in your calculations, especially if you use formulas that return text lags, such as “none,”

if a certain condition is met. For more information about STDEVA, STDEVPA, VARA, and

VARPA, see “Using sample and population statistical functions” later in this chapter.

You can also use the AGGREGATE function to apply these functions with options to ignore

hidden rows and error values. For more information, see “The AGGREGATE function” in

Chapter 14.

Using functions that analyze rank and percentile

Excel includes several sets of functions that extract rank and percentile information from a

set of input values: PERCENTRANK, PERCENTILE, QUARTILE, SMALL, LARGE, and RANK.

You can also use the AGGREGATE function to apply these functions with options to ignore

hidden rows and error values. For more information, see “The AGGREGATE function” in

Chapter 14.

The PERCENTRANK functions

The PERCENTRANK.INC and PERCENTRANK.EXC functions return a ranking for any item

(aka
member
) of a data set as a percentage. The .INC (inclusive) form of this function

includes the entire data set, and the .EXC (exclusive) form eliminates rankings of 0% and

100%. We used PERCENTRANK.INC to create the percentile ranking in column E in

Fig ure 17-1. These functions are meant to replace the old PERCENTRANK function, which you

can still use and is equivalent to the .INC form.