Sum with array problem using SRC/BusinessObjects software

[[email protected]]

I don't know how possible this is, but it never hurts to ask right.
We are using a software called SRC (now Business Objects) that runs on
top of Excel and has its own database (My impression is that this
software is a big fancy macro). We dump financial data from AS400 into
this database so that we can generate reports from SRC. We don't have
a way to get year to date numbers efficiently in reports using this
software. This is the formula I am trying to use to get YTD numbers:

{=SUM(($B$3:$B$14<=InputPeriod)*(SRCSUM("Administrative &
Other_IN_CYACT1","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT2","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT3","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT4","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT5","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT6","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT7","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT8","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT9","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT10","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT11","ACCT=***")),(SRCSUM("Administrative &
Other_IN_CYACT12","ACCT=***")))}

'InputPeriod' is simply a number from 1 to 12 representing the 12
financial reporting periods.
'SRCSUM' is a SRC function that uses the parameters in parenthesis to
get the proper dollar amount (this is a number not text). As you can
probably guess, 'CYACT*' stand for 'current year actual' period 1 to
12. This is what I am noticing looking at 'Evaluate Formula':

1. The first part of the formula (i.e. $B$3:$B$14<=InputPeriod) seems
to be working. Meaning, I get 'TRUE' values where I should. For
example, if InputPeriod is 3, I get
{true;true;true;false;false;false;false;false;false;false;false;false}
and this converts to the corresponding 1's and 0's.
2. (Here is where I think part of the problem is)
SRCSUM("Administrative & Other_IN_CYACT1","ACCT=***") seems to
translate to a 0 before it actual 'pulls in' the real number. Then,
the formula multiplies all the 1's and 0's by each "0" and then this is
the result (result when stepping through 'Evaluate Formula')
{0;0;0;0;0;0;0;0;0;0;0;0}.

This is what the formula looks like right before the answer appears
SUM({0;0;0;0;0;0;0;0;0;0;0;0},0,0,0,0,0,0,0,0,0,0,0,0)

Here is the other problem. The smallest amount I get with the above
formula is the entire year (periods 1 through 12). I only get this
when InputPeriod is '1'. If I enter period 2 for example, I get an
amount equal to periods 1-12 + period 1. This is the pattern that it
follows:

Period 2 yields an amount equal to periods 1-12 +
(InputPeriod-1)*Period 1's amount.

So if I enter 12 for the InputPeriod, the formula would spit out
periods 1-12 + (11*Period 1).

Is it somehow possible to get the actual number instead of 0 BEFORE the
formula multiplies it by 1? Is there ANY way we can get YTD numbers
with this situation?

TIA

I am running Excel 2002 if it matters.

[[email protected]]

Can anyone offer any opinions on my problem?


[email protected] wrote:
> I don't know how possible this is, but it never hurts to ask right.
> We are using a software called SRC (now Business Objects) that runs on
> top of Excel and has its own database (My impression is that this
> software is a big fancy macro). We dump financial data from AS400 into
> this database so that we can generate reports from SRC. We don't have
> a way to get year to date numbers efficiently in reports using this
> software. This is the formula I am trying to use to get YTD numbers:
>
> {=SUM(($B$3:$B$14<=InputPeriod)*(SRCSUM("Administrative &
> Other_IN_CYACT1","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT2","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT3","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT4","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT5","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT6","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT7","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT8","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT9","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT10","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT11","ACCT=***")),(SRCSUM("Administrative &
> Other_IN_CYACT12","ACCT=***")))}
>
> 'InputPeriod' is simply a number from 1 to 12 representing the 12
> financial reporting periods.
> 'SRCSUM' is a SRC function that uses the parameters in parenthesis to
> get the proper dollar amount (this is a number not text). As you can
> probably guess, 'CYACT*' stand for 'current year actual' period 1 to
> 12. This is what I am noticing looking at 'Evaluate Formula':
>
> 1. The first part of the formula (i.e. $B$3:$B$14<=InputPeriod) seems
> to be working. Meaning, I get 'TRUE' values where I should. For
> example, if InputPeriod is 3, I get
> {true;true;true;false;false;false;false;false;false;false;false;false}
> and this converts to the corresponding 1's and 0's.
> 2. (Here is where I think part of the problem is)
> SRCSUM("Administrative & Other_IN_CYACT1","ACCT=***") seems to
> translate to a 0 before it actual 'pulls in' the real number. Then,
> the formula multiplies all the 1's and 0's by each "0" and then this is
> the result (result when stepping through 'Evaluate Formula')
> {0;0;0;0;0;0;0;0;0;0;0;0}.
>
> This is what the formula looks like right before the answer appears
> SUM({0;0;0;0;0;0;0;0;0;0;0;0},0,0,0,0,0,0,0,0,0,0,0,0)
>
> Here is the other problem. The smallest amount I get with the above
> formula is the entire year (periods 1 through 12). I only get this
> when InputPeriod is '1'. If I enter period 2 for example, I get an
> amount equal to periods 1-12 + period 1. This is the pattern that it
> follows:
>
> Period 2 yields an amount equal to periods 1-12 +
> (InputPeriod-1)*Period 1's amount.
>
> So if I enter 12 for the InputPeriod, the formula would spit out
> periods 1-12 + (11*Period 1).
>
> Is it somehow possible to get the actual number instead of 0 BEFORE the
> formula multiplies it by 1? Is there ANY way we can get YTD numbers
> with this situation?
>
> TIA
>
> I am running Excel 2002 if it matters.