CUBERANKEDMEMBER

The CUBERANKEDMEMBER function is used in Excel for OLAP (Online Analytical Processing) data sources to rank members in a specified set based on a given measure.

Syntax

=CUBERANKEDMEMBER(Set, Measure, Rank[, Caption])

Set The set of members that you want to rank.
Measure The measure used to rank the members in the set.
Rank The position of the member in the ranked set to return.
Caption (Optional) A logical value that specifies whether to return the member's caption or unique name. FALSE returns the unique name TRUE returns the caption. Defaults to FALSE if omitted.

About CUBERANKEDMEMBER

When diving into multidimensional data analysis and seeking to establish the relative ranking of members within a set, the CUBERANKEDMEMBER function emerges as a valuable ally in your Excel toolkit. This function is specifically designed to navigate OLAP data sources and efficiently rank members based on a specified measure, offering insightful perspectives into data hierarchies and relationships. By leveraging CUBERANKEDMEMBER, users can swiftly ascertain the hierarchical positions of members and make data-driven decisions with confidence in a dynamic analytical environment, enhancing the clarity and interpretability of complex datasets.

Examples

Suppose you have a set of products and their sales figures. You want to rank the products based on their sales amounts and retrieve the product in the second position. The CUBERANKEDMEMBER formula would be: =CUBERANKEDMEMBER('ProductSet', 'Sales', 2)

Imagine you're analyzing regional sales data categorized by months. You aim to rank the months based on total sales and retrieve the month in the third position. The CUBERANKEDMEMBER formula for this scenario would look like: =CUBERANKEDMEMBER('MonthSet', 'TotalSales', 3)

Questions

How does the CUBERANKEDMEMBER function rank members within a specified set?

The CUBERANKEDMEMBER function ranks members in a specified set based on a given measure. It arranges the members from highest to lowest based on the measure provided and then returns the member located at the specified rank position.

Can the CUBERANKEDMEMBER function be used for non-OLAP data sources?

No, the CUBERANKEDMEMBER function is specifically designed for OLAP data sources. It leverages the multidimensional structures and hierarchies inherent in OLAP databases to rank members efficiently based on a designated measure.

How can the 'Caption' parameter be utilized in the CUBERANKEDMEMBER function?

The 'Caption' parameter in the CUBERANKEDMEMBER function allows users to choose whether to return the member's unique name or a caption. By setting this parameter to TRUE, you can obtain the caption of the ranked member instead of the unique name.

Related functions

CUBEVALUE
CUBEMEMBER