Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Number and Text Filters Examples in Excel
  • Working With Tables in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to calculate average last N values in a table in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS

References

  • How to get address of named range in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Extract all partial matches in Excel
  • Find closest match in Excel

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation must begin with
  • How To Create Drop-down List in Excel
  • Excel Data validation don’t exceed total
  • Excel Data validation must not contain

CUBERANKEDMEMBER function: Description, Usage, Syntax, Examples and Explanation

by

What is CUBERANKEDMEMBER function in Excel?

The CUBERANKEDMEMBER function is one of the Cube functions in Microsoft Excel which returns the nth, or ranked, member in a set.  It is used to return one or more elements in a set, such as the top sales performer or the top 10 students.

Syntax of CUBERANKEDMEMBER function

CUBERANKEDMEMBER(connection, set_expression, rank, )

The CUBERANKEDMEMBER function syntax has the following arguments:

  • Connection: A text string of the name of the connection to the cube.
  • Set_expression: 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: 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.

Explanation of CUBERANKEDMEMBER function

  • 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 of CUBERANKEDMEMBER function

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

Post navigation

Previous Post:

How to generate random date between two dates in Excel

Next Post:

Popularly Used Excel Functions and their examples

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel NOT Function
  • Complete List of Excel Logical Functions, References and Examples

Date Time

  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • Get fiscal quarter from date in Excel
  • Display Days until expiration date in Excel
  • How to calculate Day of the Year in Excel
  • Calculate date overlap in days in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • Running count group by n size in Excel
  • Group arbitrary text values in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign people to groups in Excel

General

  • How to count total number of cells in a rectangular range in Excel
  • Customize Ribbon In Excel
  • Count cells less than in Excel
  • 3D SUMIF for multiple worksheets in Excel
  • How to fill cell ranges with random text values in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning