Excel - CUBERANKEDMEMBER Function
CUBERANKEDMEMBER Function
The Excel CUBERANKEDMEMBER function will return the ranking number, nth, or a specific member in a set that resided in OLAP cubes. You may utilize this function to retrieve one or many members in a set. For example, the best sales executive or the first rank student in a class 10th. The CUBESET function must first be calculated in the target cell to follow best practices. Then, in a selected cell, you can employ the CUBERANKEDMEMBER function.
Compatibility
This advanced excel function is compatible with the following versions of MS-Excel −
- Excel for Microsoft 365
- Excel for Microsoft 365 for Mac
- Excel for the web
- Excel 2021
- Excel 2021 for Mac
- Excel 2019
- Excel 2019 for Mac
- Excel 2016
- Excel for iPad
- Excel Web App
- Excel for iPhone
- Excel for Android tablets
- Excel for Android phones
Syntax
The syntax of CUBERANKEDMEMBER function is as follows −
CUBERANKEDMEMBER (connection, set_expression, rank, [caption])
Arguments
You can use the following arguments with the CUBERANKEDMEMBER function −
| Argument | Description | Required / Optional |
|---|---|---|
| Connection | A text string representing the connection name of the cube. | Required |
| set_expression |
A text string that specifies the expression of a set OR The CUBESET function. OR A reference to a cell where you have been evaluated CUBESET function. |
Required |
| Rank |
A number representing the top value. For example 1 In case of retrieving the top value, 2 In the case of retrieving the top value, which is in second place, and so on. To retrieve the first 5 top values, you may utilize this function repeatedly five times, specifying a different rank, 1 through 5, each time. |
Required |
| Caption | It specifies a text string that is showcased in the designated cell. | Optional |
Points to Remember
- Whenever you compute the CUBERANKEDMEMBER function, the first "#GETTING_DATA" message in the cell will be showcased before obtaining the result.
- If you specify the invalid connection name saved in the workbook, CUBERANKEDMEMBER will retrieve a #NAME? Error. If the Online Analytical Processing (OLAP) server is not functioning properly, is corrupted, or receives an error message, this function will get a #NAME Error.
- The CUBERANKEDMEMBER function will retrieve a #N/A error when the set's expression is invalid, or the incorrect rank is specified in the third argument.
Examples of CUBERANKEDMEMBER Function
Practice the following examples to learn the use of the CUBERANKEDMEMBER function in Excel.
Example 1
Step 1 − First, you may consider the sample dataset, which consists of three columns: Region, Product Name, and Sales Production. Its respective pivot table is shown in the range F1:G11 cells.
Step 2 − After that, you may select the PivotTable Analyze tab, expand the OLAP Tools tile, and select the Convert to Formulas option from the drop-down list. After selecting this option, all the field values are transformed into the formulas.
Step 3 − Furthermore, you may type the formula =CUBESET("ThisWorkbookDataModel",G1) in the C14 cell and hit the Enter tab.
Here, you can observe in the screenshot, no result is displayed by the cubeset function.
Step 4 − Lets try to get its result from the CUBERANKEDMEMBER function. You may enter the expression =CUBERANKEDMEMBER("ThisWorkbookDataModel",F14,1) in the F16 cell. Here, you need to provide the cell reference where the CUBESET function has been evaluated, which is F14. And then press the Enter tab.
Therefore, the Sum of Sales Production will be returned by the CUBERANKEDMEMEBER function.
Example 2
The CUBERANKEDMEMBER function will retrieve a #N/A error when the set's expression is invalid, or the incorrect rank is specified in the third argument.
Solution
Case 1 − Lets suppose that you gave the invalid set expression that is F13 as a second argument in this function. Here, there is no cube set expression in the F13 cell. So when you compute the formula =CUBERANKEDMEMBER("ThisWorkbookDataModel", F13,1) in the F16 cell and hit the Enter tab.
Therefore, the #N/A error will be retrieved by the CUBERANKEDMEMBER function.
Case 2 − Lets say you have given the incorrect rank number.
Will this CUBERANKEDMEMBER function give an accurate result?
So, the answer is no.
For instance, You may enter the formula =CUBERANKEDMEMBER("ThisWorkbookDataModel",F14,6) in the F16 cell and press the Enter tab.
Hence, the #N/A will be returned by the CUBERANKEDFUNCTION for this case.