Microsoft Office Tutorials and References
In Depth Information
Number Function
Description
15
Returns the k -th smallest value in a data set; k is specified
using the last function argument. If k is left blank, Excel
returns the smallest value.
SMALL
16
PERCENTILE.INC Returns the k -th percentile of values in a range, where k is a
value from 0 to 1, inclusive.
17
Returns the quartile value of a data set, based on a
percentage from 0 to 1, inclusive.
QUARTILE.INC
18
PERCENTILE.EXC Returns the k -th percentile of values in a range, where k is a
value from 0 to 1, exclusive.
19
Returns the quartile value of a data set, based on a
percentage from 0 to 1, exclusive.
QUARTILE.EXC
The second argument, options , enables you to select which items the AGGREGATE
function should ignore. These items can include hidden rows, errors, and SUBTOTAL
and AGGREGATE functions. The following table summarizes the values available for the
options argument and the effect they have on the functionâ€™s results.
Number
Description
0
Ignore nested SUBTOTAL and AGGREGATE functions
1
Ignore hidden rows and nested SUBTOTAL and AGGREGATE functions
2
Ignore error values and nested SUBTOTAL and AGGREGATE functions
3
Ignore hidden rows, error values, and nested SUBTOTAL and AGGREGATE
functions
4
Ignore nothing
5
Ignore hidden rows
6
Ignore error values
7
Ignore hidden rows and error values
Finding Unique Values Within a Data Set
Summarizing numerical values can provide valuable information that helps you run your
business. It can also be helpful to know how many different values appear within a
column. For example, you might want to display all of the countries in which Consolidated
Messenger has customers. If you want to display a list of the unique values in a column,
click any cell in the data set, display the Data tab and then, in the Sort & Filter group,