The CUBERANKEDMEMBER function 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.

  • This function was introduced in Excel 2007 and so is not available in earlier versions.

Syntax

=CUBERANKEDMEMBER(connection,set_expression,rank,[Caption])

Arguments

Argument Description
connection A text string of the name of the connection to the cube
set_expression 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 An integer value specifying the top value to return, e.g. if rank is:

  1 returns the top value
  2 returns the second most top value
  3 etcetera

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

Examples

  A B
1 Formula  
2 =CUBERANKEDMEMBER(“Sales”,$D$4,1,”Top Month”)  
3 =CUBERANKEDMEMBER(“Sales”,CUBESET(“Sales”,”Summer”,”[2004].[June]”,”[2004].[July]”,”[2004].[August]”),3,”Top Month”)  

Common Function Error(s)

Problem What went wrong
#N/A! Occurs if either:

  the syntax of set_expression is incorrect
  the set contains at least one member with a different dimension than the other members
#NAME? Occurs if either:

  the connection name is not a valid workbook connection stored in the workbook
  the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message