Formulas and Functions Help
- Welcome
- Intro to formulas and functions
-
- ACCRINT
- ACCRINTM
- BONDDURATION
- BONDMDURATION
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNUM
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENCYCODE
- CURRENCYCONVERT
- CURRENCYH
- DB
- DDB
- DISC
- EFFECT
- FV
- INTRATE
- IPMT
- IRR
- ISPMT
- MIRR
- NOMINAL
- NPER
- NPV
- PMT
- PPMT
- PRICE
- PRICEDISC
- PRICEMAT
- PV
- RATE
- RECEIVED
- SLN
- STOCK
- STOCKH
- SYD
- VDB
- XIRR
- XNPV
- YIELD
- YIELDDISC
- YIELDMAT
-
- AVEDEV
- AVERAGE
- AVERAGEA
- AVERAGEIF
- AVERAGEIFS
- BETADIST
- BETAINV
- BINOMDIST
- CHIDIST
- CHIINV
- CHITEST
- CONFIDENCE
- CORREL
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- COVAR
- CRITBINOM
- DEVSQ
- EXPONDIST
- FDIST
- FINV
- FORECAST
- FREQUENCY
- GAMMADIST
- GAMMAINV
- GAMMALN
- GEOMEAN
- HARMEAN
- INTERCEPT
- LARGE
- LINEST
- LOGINV
- LOGNORMDIST
- MAX
- MAXA
- MAXIFS
- MEDIAN
- MIN
- MINA
- MINIFS
- MODE
- NEGBINOMDIST
- NORMDIST
- NORMINV
- NORMSDIST
- NORMSINV
- PERCENTILE
- PERCENTRANK
- PERMUT
- POISSON
- PROB
- QUARTILE
- RANK
- SLOPE
- SMALL
- STANDARDIZE
- STDEV
- STDEVA
- STDEVP
- STDEVPA
- TDIST
- TINV
- TTEST
- VAR
- VARA
- VARP
- VARPA
- WEIBULL
- ZTEST
RANK
The RANK function returns the rank of a value within a set of numeric values.
RANK(value, value-set, largest-is-high)
value: The number value, date/time value or duration value to rank.
value-set: A collection of number values, date/time values or duration values. All values must be of the same value type and also the same value type as value.
largest-is-high: An optional modal value specifying whether the smallest or the largest value in the collection is ranked 1.
largest is low (0, FALSE or omitted): Assign the largest value in the collection the rank of 1.
largest is high (1, or TRUE): Assign the smallest value in the collection the rank of 1.
Notes
Values in the collection that are the same are ranked together, but impact the outcome.
If the specified value does not match any of the values in the collection, an error is returned.
Examples |
|---|
Suppose the following table contains the cumulative test scores for this term for your 20 students. (The data is organised this way for the example; it would probably originally have been in 20 separate rows.) |
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | 30 | 75 | 92 | 86 | 51 |
2 | 83 | 100 | 92 | 68 | 70 |
3 | 77 | 91 | 86 | 85 | 83 |
4 | 77 | 90 | 83 | 75 | 80 |
=RANK(30, A1:E4, 1) returns 1 because 30 is the smallest cumulative test score and largest-is-high is 1 (rank the smallest value as the first value). =RANK(92, A1:E4, 0) returns 2 because 92 is the second-largest cumulative test score and largest-is-high is 0 (rank the largest value as the first value). =RANK(91, A1:E4, 0) returns 4, because there is a "tie" for second place. The order is 100, 92, 92, then 91 and the rank is 1, 2, 2, and then 4. |