The CUBERANKEDMEMBER function in Excel is a powerful tool designed for working with OLAP (Online Analytical Processing) cubes. It enables users to retrieve the nth ranked member from a specified set based on a defined measure. This functionality is particularly useful in data analysis scenarios where ranking is essential, such as identifying top sales figures or highest-scoring students.

Syntax

CUBERANKEDMEMBER(cube_reference, member_expression, rank, [order])

• cube_reference: A reference to the cube from which to retrieve the member, typically defined as a connected data source.
• member_expression: The expression that identifies the member to evaluate for ranking.
• rank: A numeric value indicating the rank of the member to return.
• order: (Optional) A parameter to specify whether to rank in ascending or descending order.

Example #1

CUBERANKEDMEMBER("SalesCube", "[Sales].[Employee].Members", 1)

This function returns the top-ranked employee in a sales cube. For example, if “John Doe” is the highest seller, the result would be “John Doe”.

Example #2

CUBERANKEDMEMBER("SalesCube", "[Sales].[Product].Members", 3)

This retrieves the third-ranked product based on sales. If the top products are “A”, “B”, and “C”, the result would be “C”.

Example #3

CUBERANKEDMEMBER("SalesCube", "[Sales].[Region].Members", 5, 1)

This function finds the fifth-ranked region in ascending order. If the ranks in ascending order are “West”, “South”, “North”, “East”, and “Central”, the result would be “Central”.

Error handling

• VALUE!: This error occurs if one of the arguments is of the wrong type, such as if the rank is not a number.
• N/A: Indicates that the specified rank exceeds the number of available members in the set.
• REF!: This error may appear if the cube reference is invalid or if the data source is disconnected.

Conclusion

In summary, the CUBERANKEDMEMBER function is a valuable asset for Excel users who need to analyze data from OLAP cubes. By allowing the extraction of ranked members based on various criteria, it aids in decision-making and performance assessment across a broad range of applications, from business analytics to academic performance.