Cube
Excel 2007
◉ 32 views
=CUBERANKEDMEMBER()
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.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016Excel for iPadExcel Web AppExcel for iPhoneExcel for Android tabletsExcel for Android phones
=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])
Hover argument names in the table for details.
Output typeMixed
Popularity20 / 100
VolatileNo
Dynamic ArrayNo
Download sample .xlsx ▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =CUBERANKEDMEMBER() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
connection |
Text | Required | A text string of the name of the connection to the cube. |
set_expression |
Text | 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 |
Number | 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 |
Text | Optional | A text string displayed in the cell instead of the caption, if one is defined, from the cube. |
▣ Examples
Example 1
=CUBERANKEDMEMBER("Sales",$D$4,1,"Top Month")
Example 2
=CUBERANKEDMEMBER("Sales",CUBESET("Sales","Summer","[2004].[June]","[2004].[July]","[2004].[August]"),3,"Top Month")
§ 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.
✦ Tips
- 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.
- When the CUBERANKEDMEMBER function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
⚠ Common Errors
#NAME?
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.
Fix: Check the connection name in the Connections section
#N/A
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.
Fix: نحو عبارت مجموعه و سازگاری ابعاد اعضا را بررسی کنید.
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!