BusinessObjects Board

Excel Array function

other Xcelsius - Crystal Dashboard Design
#1

Are the excel array formulas, created with CTRL+SHFT+ENTR keys in excel worksheet, works well in Xcelcius or SWF? Here is the one for example:=
{SUM(IF(B6:B400=“LEAD”,1,0))}

The problem: The Preview does not show the result numbers? However, the excel does not complain and in the design mode, I can see the numbers.
For now, I have created a dummy column using - IF(B^:B400=“LEAD”,1,0) and another column to with SUM function to total and this seems working fine. But the problem is, I have too many similar calculation in this work sheet and creating a dummy column for each of those adds unnecessary columns.
Originally SUMIF with multiple criteria did not work well with Xcelcius. I am trying to explore any workarounds or other similar experience in the forum.

Thanks!

vramadhyani (BOB member since 2014-12-11)

#2

I was trying to get to this:

=SUM(IF(I6:I405=V6,IF(B6:B405=“LEAD”,1,0),0))

As per another thread here, nested IF function is not supported in Xcelsius. SUMIFS are alos not supported. Any work around with this multiple criteria/scenario?
Thanks!

vramadhyani (BOB member since 2014-12-11)

#3

Array functions don’t work well (if at all?) but nested IFs are fine. I use them all the time.

Debbie

Debbie :uk: (BOB member since 2005-03-01)

#4

Array Formulas are not supported in Xcelsius. You’ll have to break each part of the formula down as you’ve done. The best way will be to push this data summarisation into whatever source system you are using and do the number crunching there, then import/connect to the data.

cdavies :uk: (BOB member since 2005-01-28)