Quick Navigation

CUBERANKEDMEMBER Function

Excel 2007+

Summary

The CUBERANKEDMEMBER function retrieves the nth ranked member from a specified set within an OLAP cube connection. Perfect for extracting top performers, leaders, or specific ranked items from multidimensional data analysis.

Syntax

CUBERANKEDMEMBER(connection, set_expression, rank, [caption])

Parameters

Parameter	Type	Required	Description
connection	`text`	Yes	Name of the workbook connection to the OLAP cube
set_expression	`text`	Yes	MDX set expression, CUBESET function, or cell reference containing CUBESET
rank	`number`	Yes	Position to return (1 for top value, 2 for second, etc.)
caption	`text`	No	Custom text to display instead of cube caption

Using the CUBERANKEDMEMBER Function

CUBERANKEDMEMBER enables precise extraction of ranked data from OLAP cubes, making it invaluable for leadership reports, performance rankings, and analytical dashboards that require specific positioned results from multidimensional datasets.

Common CUBERANKEDMEMBER Examples

Retrieve Top Sales Month

=CUBERANKEDMEMBER("Sales",$D$4,1,"Top Month")

Returns the highest performing month from the Sales cube set with custom 'Top Month' label

Third Best Summer Month

=CUBERANKEDMEMBER("Sales",CUBESET("Sales","Summer","[2004].[June]","[2004].[July]","[2004].[August]"),3,"Top Month")

Gets the third-ranked summer month (August) from a predefined CUBESET

Frequently Asked Questions

Use CUBESET with sort_order -1 to reverse the set order, then CUBERANKEDMEMBER(...,1) returns the lowest value.

The function is actively retrieving data from the OLAP cube - this is normal behavior.

Common Errors and Solutions

#NAME? error

Cause: Invalid connection name or OLAP server unavailable

Solution: Verify connection exists in workbook and OLAP server is running

#N/A error

Cause: Invalid set_expression syntax or mixed dimensions in set

Solution: Check MDX syntax and ensure all set members belong to same dimension

#VALUE! error

Cause: Rank parameter not a valid integer

Solution: Use integer values for rank parameter

Notes

Displays #GETTING_DATA… temporarily while fetching cube data
Use multiple CUBERANKEDMEMBER calls with ranks 1-5 for top 5 results
For bottom rankings, reverse set order in CUBESET using sort_order -1
Set_expression accepts direct MDX, CUBESET functions, or cell references

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in: Excel 2003 and earlier

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+