1207028 - Data disappears on preview in Xcelsius when using array formulas in Excel

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.

• BusinessObjects Xcelsius 2008
• BusinessObjects Dashboard

1. Create an Excel spreadsheet containing Country and, Revenue columns.
2. Create two variables; COUNTRY and Revenue. Both variables should contain all the data range values for the Country and Revenue columns respectively.
3. Add a third column called Revenue in 5 years and for the first cell of this column use this formula:=IF (COUNTRY<>"", Revenue*5)
4. 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.
5. 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.
6. Preview the model and the chart content will disappear.

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

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.

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