CUBERANKEDMEMBER

What it does

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

CUBERANKEDMEMBER is the family's indexer: given a set that lives on the server, it retrieves the item at one position. Microsoft's framing is "the top sales performer or the top 10 students", and the "top" in that phrase is doing quiet but load-bearing work — see below.

The member at position rank of a set, where position 1 is the first item.

The word to be careful about is ranked. CUBERANKEDMEMBER does not rank anything. It reads a position out of an order that already exists — the order the set was built in. If the set came from CUBESET with sort_order 2 and a sort_by measure, then rank 1 genuinely is "the top performer by that measure". If the set was built with sort_order 0 (SortNone, the default), rank 1 is simply whichever item the cube happened to return first, and the formula CUBERANKEDMEMBER(conn, mySet, 1) will read like a top-N query while meaning nothing of the kind.

So the function's real semantics are: position lookup in a server-defined sequence, with all the ranking meaning imported from the CUBESET that produced the sequence. The pairing is inseparable, and this is the single most useful thing to understand about the function.

The standard idiom is a top-N block: one CUBESET cell defining and sorting the set, a column of CUBERANKEDMEMBER cells for ranks 1…N, and a parallel column of CUBEVALUE cells reading the measure at each of those coordinates. CUBESETCOUNT supplies N when it is not fixed.

Like CUBEMEMBER, the resulting cell is dual: its display value is a caption, and its identity is the member, which is what CUBEVALUE consumes when the cell is referenced.

Arguments

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

• connection (required) — text; the name of a connection stored in the workbook, used as a lookup key. Note that CUBERANKEDMEMBER takes a connection even when set_expression is a reference to a CUBESET cell that already has one; CUBESETCOUNT, by contrast, takes no connection at all. The asymmetry between the two accessors is not explained by the documentation.
• set_expression (required) — text holding an MDX set expression such as "{[Item1].children}", or a CUBESET call, or a reference to a cell containing one. Three quite different things share one argument slot.
• rank (required) — an integer position. Microsoft: "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." One-based, and the documentation says nothing about zero, negative, fractional, or out-of-range values.
• caption (optional) — text displayed instead of the cube's own caption.

Try it

This example needs an Excel cube connection. OneCalc cannot provide that connection in this preview.

Quirks

Result and edge cases

The result is a member: displayed as a caption, carrying member identity for downstream CUBE functions. It is a single cell — asking for ranks 1…10 requires ten formulas, not one spilling call. CUBERANKEDMEMBER predates dynamic arrays and has no array form.

While the request is outstanding the cell displays #GETTING_DATA… — a transient extended-family placeholder catalogued in the value universe, not a failure.

The out-of-range case (rank greater than the set's cardinality) is the obvious boundary, and the documentation does not state its result. Real workbooks hit it constantly, because a top-10 block over a set that turns out to hold six members is an everyday occurrence. The Handbook does not know what Excel returns there; it is the first probe listed below.

Errors

As documented by Microsoft on the page linked below:

• #NAME? — the connection name is not a valid workbook connection; or the OLAP server is not running, not available, or returns an error message.
• #N/A — the set_expression syntax is incorrect, or the set contains at least one member with a different dimension from the others.

Microsoft's CUBERANKEDMEMBER page documents no error for an out-of-range rank, and no #VALUE! condition of the kind its siblings list. Whether that reflects the behaviour or the documentation is unknown to the Handbook.

How to implement

Notes for implementers

• The order is not the function's to define. Any implementation must treat rank as an index into a sequence fixed by the set constructor, and must therefore preserve set order faithfully across the boundary — including tie order.
• Tie handling is a genuine semantic question that neither this function nor CUBESET documents. If two members share a sort_by value, which is rank 1 — and is that answer stable across recalculations? An implementation that sorts with an unstable algorithm will produce a workbook that changes its answers without any input changing.
• Because set_expression accepts a literal string, a nested call, and a cell reference, the argument must be resolved with the same identity-versus-display rule the rest of the family uses: a referenced cell containing a CUBE function contributes its expression, not its text.
• The result is scalar-per-call. A modern reimplementation would be tempted to return an array for a range of ranks; that would be a different function, not this one.

Rust code

This entry has no Rust implementation yet.

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• CUBESET — supplies the set and, crucially, its order. CUBERANKEDMEMBER cannot be understood without it.
• CUBESETCOUNT — the other accessor on a set; gives the loop bound that keeps rank in range.
• CUBEMEMBER — the other coordinate producer. CUBEMEMBER names a member directly; CUBERANKEDMEMBER names one positionally. Both feed CUBEVALUE.
• CUBEVALUE — the consumer that turns the retrieved coordinate into a number.

Outside the family, readers reach for LARGE, INDEX, or SORTBY+INDEX by analogy. The analogy is decent for intent and wrong for mechanism: those functions rank data present on the sheet, whereas CUBERANKEDMEMBER never brings the set to the client.

Named variants

No named variants are recorded yet.