CUBERANKEDMEMBER function
CUBERANKEDMEMBER returns the member at a chosen position in a cube set, which makes it handy for top-10 lists. The set must come from a CUBESET result, and a rank beyond the set size gives #N/A.
Example
=CUBERANKEDMEMBER("ThisWorkbookDataModel",A1,1)
Expected result: n/a Not yet checked in desktop Excel
Syntax
CUBERANKEDMEMBER(connection, set_expression, rank, [caption])
Arguments in [square brackets] are optional.
-
connection(required) - Name of the cube connection.
-
set_expression(required) - A set, usually a cell holding a CUBESET.
-
rank(required) - Position to retrieve, 1 for the first item.
-
caption(optional) - Text to display instead of the member name.
Returns: The member at the given position in a set.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=CUBERANKEDMEMBER("NoSuchConnection","[Product].[All].Children",1) | #N/A | Unknown connection name |
=CUBERANKEDMEMBER("ThisWorkbookDataModel","[Product].[All].Children",1) | #N/A | ThisWorkbookDataModel without a model |
=CUBERANKEDMEMBER("","[Product].[All].Children",1) | #N/A | Empty connection string |
=CUBERANKEDMEMBER(1,"[Product].[All].Children",1) | #N/A | Connection is a number |
=CUBERANKEDMEMBER("NoSuchConnection","[Product].[All].Children",0) | #N/A | Rank 0 |
=CUBERANKEDMEMBER(Data!D1,"[Product].[All].Children",1) | #N/A | Connection from blank cell |
The Data sheet these formulas read
A cell that starts with = holds that formula.
D
1
Data
Watch out for
- The set cell must contain a CUBESET result.
- A rank beyond the set size gives #N/A.
Where it works
- Excel
- CUBERANKEDMEMBER is in every version of Excel from 2007 on. Needs an OLAP cube or Data Model connection; Windows desktop Excel only for most uses.
- Google Sheets
- No direct equivalent. No cube support in Google Sheets.
Part of Cube functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .