The RANKCUMULATE function ranks values and then cumulates values in order of the ranking. It can perform ranking separately across different groups.

For example, you can use the RANKCUMULATE function to cumulatively sum employees sales revenue in order of their length of service. There is an example of this in the Examples section.

## Syntax

RANKCUMULATE(Cumulation values, Ranking values [, Direction] [, Include value] [, Ranking groups])

## Arguments

 Argument Data type Description Cumulation values (required) Number The number to cumulate, based on ranking criteria. Ranking values (required) Number, date, or time period Can be a line item, property, or expression. The ranking criteria to perform cumulation based on. Direction Keyword Determines the direction to rank in. The keywords are DESCENDING and ASCENDING. There's more information in the Direction argument keywords section below. Include value Boolean Determines if a value is ranked. The default value, TRUE, includes a value in the ranking. A value of FALSE omits a value from the ranking and returns a result of 0. Ranking groups Number, Boolean, date, time period, list, or text If provided, values are ranked independently for each value of the Ranking groups argument.

The RANKCUMULATE function returns a number.

### Direction argument keywords

 Keyword Description DESCENDING When used, the RANKCUMULATE function assigns the highest source value rank 1, the second highest source value rank 2, and so on. ASCENDING The default keyword if you omit the Direction argument. When used, the RANKCUMULATE function assigns the lowest source value rank 1, the second lowest source value rank 2, and so on.

## Syntax example

RANKCUMULATE(Revenue, Transaction Date, DESCENDING, Eligible transaction?, Region)

### Ranking behavior for different data types

The Ranking values argument for the RANKCUMULATE function can be a number, date, or time period type line item, property, or expression. However, the function always returns a number.

When the RANKCUMULATE function ranks values with the default ASCENDING keyword for the Direction argument, the function ranks the lowest value as 1, the second lowest as 2, and so on. If you use RANKCUMULATE with:

• Numbers, the function ranks the largest number the highest.
• Dates, the function ranks the date further in the future the highest.
• Time periods, the function ranks the time period furthest in the future the highest.

### Equal ranking value behavior

If two values of the Cumulation values argument share the same ranking for the Ranking values argument, ranking follows the order of any associated list items within General Lists.

### Use RANKCUMULATE with the Users list

You can reference the Users list with the RANKCUMULATE function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

## Constraints

### Cell Limit

A cell limit of 50 million cells is imposed to prevent ranking of large data sets that would slow down the server. If more than 50 million cells are used with the RANKCUMULATE function, the model is rolled back and a notification displays.

The 50 million cell limit does not account for summarized values or the Time and Versions lists. This means you can use the RANKCUMULATE function with a line item with a Cell Count of greater than 50 million cells if there are fewer than 50 million nonsummarized cells.

As the number of cells you use with the RANKCUMULATE function increases, so does the duration of the calculation.

### Positive Infinity, Negative Infinity, and NaN

If you use positive infinity, negative infinity, or NaN (Not a Number) for the Ranking values argument, the RANKCUMULATE function returns 0.

### Cumulation Source Constraints

If your cumulation source is a large data set, the addition of numbers with a large number of decimal places can result in floating point error for the least significant digits.

If the cumulation source contains an infinity, then the result from then on until the end of the cumulation is that infinity. However, if an opposite infinity follows it, the result becomes NaN (Not a Number).

## Calculation engine functionality differences

You cannot currently use the RANKCUMULATE function in Polaris.

In the Classic Engine, you can.

RANK

## Examples

### General examples

In this example, a module that contains the Salespersons list is on columns, and a number of line items is on rows.

The example uses RANKCUMULATE to cumulatively sum sales in the order of each salesperson's length of service. Further iterative formulas use the Include value and Ranking groups arguments to:

• Determine which salesperson's sales to include in the cumulative ranking.
• Further break down the cumulative ranking by region.

Two line items use the RANK function to help you identify the order that RANKCUMULATE cumulates values in.

 Ben Graham Rashid Laura Rita David Masaki Kieran Alisa Karen Martina Oswald Region North North North South South South East East East West West West Sales 258,796 235,884 190,750 228,315 171,494 234,276 230,213 222,777 201,855 271,162 267,401 209,368 Years of service 6 9 8 12 11 9 13 14 5 15 11 14 Rank by years of service RANK(Years of Service, DESCENDING) 11 8 10 5 6 8 4 2 12 1 6 2 Rank by years of service within region RANK(Years of Service, DESCENDING, MINIMUM, TRUE, Region) 3 1 2 1 2 3 2 1 3 1 3 2 Include in cumulation? Cumulated sales by longest tenure RANKCUMULATE(Sales, Years of Service, DESCENDING) 2,520,436 1,836,614 2,261,640 1,161,835 1,333,329 2,070,890 933,520 493,939 2,722,291 271,162 1,600,730 703,307 Cumulated sales by tenure for selected employees RANKCUMULATE(Sales, Years of Service, DESCENDING, Include in cumulation?) 685,430 235,884 426,634 228,315 399,809 0 452,990 222,777 654,845 271,162 0 480,530 Cumulative sales by tenure for each region RANKCUMULATE(Sales, Years of Service, DESCENDING, TRUE, Region) 685,430 235,884 426,634 228,315 399,809 634,085 452,990 222,777 654,845 271,162 747,931 480,530

Disclaimer

We update Anapedia regularly to provide the most up-to-date instructions.