CUBEMEMBER is a function in Excel that allows you to determine the position of a given member in a cube. The function takes four arguments: the name of the cube, the name of the member, the row number, and the column number. The function will return the number of the cell in the cube where the given member is located.
The syntax of CUBEMEMBER in Excel is as follows:
database is the range of cells that contains the data for the cube column is the column in the database that contains the data for the cube row is the row in the database that contains the data for the cube
One way to use CUBEMEMBER is to create a pivot table. For example, consider the table below that shows the sales for a company by region and product.
To create a pivot table, select the data and then click on the Insert tab and select Pivot Table.
The Pivot Table Fields window will open. Drag Region to the Row Labels area and Product to the Column Labels area.
In the Values area, drag Sales to the Values area.
CUBEMEMBER can then be used to calculate the percentage of total sales for each product in each region. In the Pivot Table, select the Values field and then click on the Insert tab and select CUBEMEMBER.
The CUBEMEMBER dialog box will open. In the Member area, select Product and in the Cube area, select Sales.
Click on the OK button.
A new column will be added to the pivot table called CUBEMEMBER. This column shows the percentage of total sales for each product in each region.
When you should not use CUBEMEMBER in Excel is when you are trying to create a formula that will return a value. CUBEMEMBER is designed to return a value that is based on a cell reference and the members of a cube. If you are trying to create a formula that will return a value, you should use the CUBESET function.
Some similar formulae to CUBEMEMBER in Excel are CUBESET, CUBESUM, CUBERANK, and CUBESETCOUNT. These formulae all calculate information about a cube or set of cubes. CUBESET returns a set of all the unique members in a given cube, CUBESUM returns the sum of all the members in a given cube, CUBERANK returns the rank of a given member in a given cube, and CUBESETCOUNT returns the number of members in a given cube.