# A to Z of Excel Functions: the CUBEVALUE Function

23 March 2018

*Welcome back to our regular A to Z of Excel Functions blog. Today we look at the CUBEVALUE function. *

**The CUBEVALUE function**

When the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source, this function returns an aggregated value from the cube.

The **CUBEVALUE **function employs the following syntax to operate:

**CUBEVALUE(connection, [member_expression1], [member_expression2], …)**

The **CUBEVALUE** function has the following arguments:

**connection:**this is required and represents a text string of the name of the connection to the cube**member_expression:**these arguments are optional. Each is a text string of a multi-dimensional expression (**MDX**) that evaluates to a member or tuple within the cube. Alternatively,**member_expression**can be a set defined with the**CUBESET**function. Use**member_expression**as a slicer to define the portion of the cube for which the aggregated value is returned. If no measure is specified in**member_expression**, the default measure for that cube is used.

It should be further noted that:

- the
**CUBEVALUE**function is supported only when the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source - when the
**CUBEVALUE**function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved - if a cell reference is used for
**member_expression**and that cell reference contains a**CUBE**function, then**member_expression**uses the**MDX**expression for the item in the referenced cell, and not the value displayed in that referenced cell - if the connection name is not a valid workbook connection stored in the workbook,
**CUBEVALUE**returns an*#NAME?*error value. If the Online Analytical Processing (OLAP) server is not running, not available or returns an error message,**CUBEVALUE**returns an*#NAME?*error value - if at least one element within the tuple is invalid,
**CUBEVALUE**returns an*#VALUE!*error value **CUBEVALUE**returns an*#N/A*error value when:- the
**member_expression**syntax is incorrect - the member specified by
**member_expression**doesn't exist in the cube - the tuple is invalid because there is no intersection for the specified values (this can occur with multiple elements from the same hierarchy)
- the set contains at least one member with a different dimension than the other members

- the
**CUBEVALUE**may return an*#N/A*error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulae (on the ‘Options’ tab in the Ribbon, in the ‘Tools’ group, click ‘OLAP Tools’, and then click ‘Convert to Formulas’).

*Known issue: null values are converted to zero-length strings*

In Excel, if a cell has no data because you never changed it or you deleted the contents, the cell contains an empty value. In many database systems, an empty value is called a *null value*. An empty or null value literally means "no value". However, a formula can never return an empty string or null value. A formula always returns one of three values: a number value; a text value (which may be a zero-length string) or an error value, such as *#NUM!* or *#VALUE!*

If a formula contains a **CUBEVALUE** function connected to an Online Analytical Processing (OLAP) database and a query to this database results in a null value, Excel converts this null value to a zero-length string, even if the formula would otherwise return a number value. This can lead to a situation where a range of cells contain a combination of numeric and zero-length string values, and this situation can affect the results of other formulae that reference that range of cells. For example, if **A1 **and **A3 **contain numbers, and **A2 **contains a formula with a **CUBEVALUE** function that returns a zero-length string, the following formula would return an *#VALUE!* error:

**=A1+A2+A3**

To prevent this, you can test for a zero-length string by using the **ISTEXT** function and by using the **IF **function to replace the zero-length with a 0 (zero) as the following example shows:

**=IF(ISTEXT(A1),0,A1)+IF(ISTEXT(A2),0,A2)+IF(ISTEXT(A3),0,A3)**

Alternatively, you can nest the **CUBEVALUE** function in an **IF** condition that returns a zero value if the **CUBEVALUE** function evaluates to a zero-length string as the following example shows:

**=IF(CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2020]","[All Product].[Beverages]")="",0, CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2020]","[All Product].[Beverages]"))**

Note that the **SUM** function does not require this test for a zero-length string because it automatically ignores zero-length strings when calculating its return value.

Please see my examples below:

**=CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2020]","[All Product].[Beverages]")**

**=CUBEVALUE($A$1,"[Measures].[Profit]",D$12,$A23)**

**=CUBEVALUE("Sales",$B$7,D$12,$A23)**

*We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.*