# CUBERANKEDMEMBER function

This article describes the formula syntax and usage of the **CUBERANKEDMEMBER** function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) 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 (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

**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.