CUBERANKEDMEMBER function

This article describes the formula syntax and usage of the CUBERANKEDMEMBER function in Microsoft Excel.

Description

Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or the top 10 students.

Syntax

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

The CUBERANKEDMEMBER function syntax has the following arguments:

  • Connection    Required. A text string of the name of the connection to the cube.

  • Set_expression    Required. A text string of a set expression, such as "{[Item1].children}". Set_expression can also be the CUBESET function, or a reference to a cell that contains the CUBESET function.

  • Rank    Required. An integer value specifying the top value to return. If rank is a value of 1, it returns the top value, if rank is a value of 2, it returns the second most top value, and so on. To return the top 5 values, use CUBERANKEDMEMBER five times, specifying a different rank, 1 through 5, each time.

  • Caption    Optional. A text string displayed in the cell instead of the caption, if one is defined, from the cube.

Remarks

  • When the CUBERANKEDMEMBER function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.

  • If the connection name is not a valid workbook connection stored in the workbook, CUBERANKEDMEMBER returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBERANKEDMEMBER returns a #NAME? error value.

  • CUBERANKEDMEMBER returns a #N/A error value when the syntax of set_expression is incorrect or when the set contains at least one member with a different dimension than the other members.

Examples

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

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

Tip   To return the bottom n values, use the sort_order and sort_by arguments of the CUBESET function to reverse the order of the set so that the top values in the sorted set are the bottom values. For example, CUBERANKEDMEMBER ("Sales", $D$4,1) returns the last member, CUBERANKEDMEMBER ("Sales", $D$4, 2) returns the next to last member, and so on.

Applies To: Excel 2013, Excel 2007, Excel 2010, Excel Starter, Excel Online, Excel 2016 for Mac



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language