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.
Search JabSto ::




Custom Search