Use the RANKCUMULATE function to cumulate line item values in the order of a rank from a separate line item

## Syntax

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

Argument | Data type | Description |

Cumulation values (required) | Number | The number to cumulate, based on ranking criteria. |

Ranking values (required) | Number, date, or time period | The ranking criteria to base cumulation on. |

Direction (optional) | Keyword | Determines the direction to rank in. |

Include value (optional) | Boolean | Determines if a value is ranked. |

Ranking groups (optional) | Number, Boolean, date, or time period | If this argument is provided, the cumulation values are cumulated separately for each value of this argument. |

## Returns

The RANKCUMULATE function returns a number-formatted result.

## Arguments

The Source values argument can refer to a number-formatted line item, property, or expression.

The Ranking values argument can refer to a number-, date-, or time period-formatted line item, property, or expression.

The keywords for the Direction argument are:

- DESCENDING (default): the highest source value is ranked 1, the second-highest source value is ranked 2, and so on
- ASCENDING: the lowest source value is ranked 1, the second-lowest source value is ranked 2, and so on

The Include value argument is a Boolean value that determines if the RANKCUMULATE function cumulates the corresponding value. The default behavior is TRUE, which cumulates all values. If FALSE is used, then the cumulation omits the corresponding value, and the cell displays a hyphen.

The Ranking groups argument can be used to split the target values of the RANK function into categories. Each set of values with the same value for the Ranking groups argument is cumulated separately.

## Additional information

### Ranking behavior across dimensions

**Time** and **Versions** are excluded from the ranking. If the Cumulation values argument has a **Time** or **Versions** dimension, RANKCUMULATE cumulates values independently for each time period or version.

If the target structure has more than one other dimension (excluding the **Time** and **Versions** dimensions), the Cumulation values argument cumulates across all combinations where the Ranking values argument is present. If you include the Ranking groups argument, values cumulate across all combinations, but within the specified groups.

### 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

The RANKCUMULATE function has the following constraints:

### Formatting Constraints

The Cumulation values argument is required, and must be a number-formatted line item, property, or expression.

The Ranking values argument is required, and must be a number-, date-, or time period-formatted line item, property, or expression.

The result of the function must be number-formatted.

### 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).

### Cell Limit

An artificial limit is imposed to prevent ranking of large data sets that would slow down the server. This limit is set at 50 million cells. 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 less 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

Infinities and NaN (Not a Number) are automatically excluded from the Ranking values argument and give a result of NaN.

## Excel equivalent

## Examples

In this example, RANKCUMULATE is used to cumulate sales by representatives. The module is named *Sales Rep Annual Costs & Sales*. It has the *Sales Rep* list on the columns dimension, and line items on the rows dimension.

A number-formatted *Sales* line item is given for the Cumulation values argument, and a number-formatted *Years of Service* line item as the Ranking values argument. The result, in the default ascending order, is a cumulative aggregation of sales rep annual *Sales* by years of service:

RANKCUMULATE(Sales, Years of Service)

### RANKCUMULATE with Include value argument

A second example modifies the first example to include the optional Include value argument. A Boolean-formatted *Include for Ranked Cumulation* line item is used for the argument. The result of the addition of this argument is that if the line item does not have a check mark, it is not included in the cumulation.

RANKCUMULATE(Sales, Years of Service, ASCENDING, Include for Ranked Cumulation)

### RANKCUMULATE with Ranking groups argument

A third example modifies the second example. The Boolean-formatted Include values argument includes all cumulation values, and the list-formatted *Region* line item is used for the optional Ranking groups argument. This means the rankings for the cumulation of the *Sales* values are further separated into the four regions in the list:

RANKCUMULATE(Sales, Years of Service, ASCENDING, Include for Ranked Cumulation, Region)

### RANKCUMULATE with multiple dimensions for Cumulation value argument

This example illustrates ranking for cumulative summing where the source line item has two dimensions other than **Time** and **Versions** applied. The Ranking groups argument is provided, so the ranking occurs within separate groups across the source dimensions. The *Quarter Profit* source line item displays figures for *Product* type by *Region*. Products are split into two *Product Sales Groups* across regions that are the Ranking groups argument in a RANKCUMULATE calculation.

RANKCUMULATE(Quarter Profit, Quarter Profit, ASCENDING, TRUE, Product Sales Group)

Note that, in this example, the *Group Rank* line item uses the RANK function to display group rankings to aid understanding. The rankings displayed in the *Group Rank* line item are independent of RANKCUMULATE.