Home > Sample chapters > Microsoft Office > Excel

Cube Functions in Microsoft Excel 2010


Syntax CUBERANKEDMEMBER(connection,set_expression,rank,caption)

Definition This function returns the n-th member in a set.


  • connection (required) A string with the name of the workbook connection to the cube. After you enter the first quotation mark, the existing context-sensitive data connections are displayed (see Figure 14-4, shown earlier).

  • set_expression (required) Defines the number of members in the cube based on an MDX. The expression can be entered directly or can be in a cell that is referenced. You can also use tuples in expressions.

  • rank (required) An integer indicating the position of a member in the set.

  • caption (required) A string displayed in the cell instead of the caption of the member in the cube. If a tuple is used, the function returns the caption of the last member in the tuple.


Error values and messages provide information about incorrect or missing entries:

  • If the connection name is not a valid workbook connection, the CUBERANKEDMEMBER() function returns the #NAME? error.

  • If the OLAP server (or the offline cube) is not available, you get an error message. The content of the affected cell doesn’t change.

  • If set_expression is longer than 255 characters, the CUBERANKEDMEMBER() function returns the #VALUE! error.

  • CUBERANKEDMEMBER() returns the #N/A error when:

    • The set_expression syntax is incorrect.

    • The set specified in the MDX query doesn’t exist in the cube.

  • CUBERANKEDMEMBER() might return the #N/A error when the connection to the data source is interrupted and cannot be re-established.

Example If you reference a cell in the formula


that returns the store set with

=CUBESET("offline","[Stores].[Store].Children", "all store

the result is NorthEast. This store has the most sales for all products and in all years. The nested formula

   [Years].Children)","all sales",2;"[Measures].[Sales]"),3)

calculates the year with the least sales for this store (position 3): 2011.