### Symptom

You have a formula containing variables referencing a cell range in Excel.

If you create a model using an Excel data source which contains formulas such as the following:

**=IF (COUNTRY<>"", Revenue*5)**

where **COUNTRY** and **Revenue** are variables with data range values.

While previewing the model in .xlf, the chart/graph content will disappear.

If you display the data in a table, then "NaN" (Not a Number) will be displayed.

### Environment

- BusinessObjects Xcelsius 2008
- BusinessObjects Dashboard

### Reproducing the Issue

- Create an Excel spreadsheet containing
**Country**and,**Revenue**columns. - Create two variables;
**COUNTRY**and**Revenue**. Both variables should contain all the data range values for the**Country**and**Revenue**columns respectively. - Add a third column called
**Revenue in 5 years**and for the first cell of this column use this formula:**=IF (COUNTRY<>"", Revenue*5)** - If the revenue for the first country is 20,000 then the value for this cell should be 100,000. Copy this formula to the whole column to get respective data.
- Create a model using this as a data source. Select a simple column chart selecting only the first value of the
**Revenue in 5 years**column. i.e.100,000 as the data range. - Preview the model and the chart content will disappear.

### Cause

The reason for this is Array formulas are not supported by Xcelsius.

### Resolution

Get rid of the array formulas by replacing the variables with their respective data cell reference.

For example, the formula:

**=IF (COUNTRY<>"", Revenue*5)**

can be replaced with:

**=IF (COUNTRY<>"", $D5*5)**

Where cell **D5** contains the revenue for a country and the formula will calculate the revenue in five years.

### Keywords

data disappears using array formulas , 9726470 , KBA , BI-RA-XL , Dashboards and Presentation Design , How To