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("Administr ative &
Other_IN_CYACT1","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT2","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT3","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT4","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT5","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT6","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT7","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT8","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT9","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT10","ACCT=***")),(SRCSUM("Administra tive &
Other_IN_CYACT11","ACCT=***")),(SRCSUM("Administra tive &
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;fals e;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.

Can noone offering any opinions on this? At least to tell me it can't
be done?


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("Administr ative &
Other_IN_CYACT1","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT2","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT3","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT4","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT5","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT6","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT7","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT8","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT9","ACCT=***")),(SRCSUM("Administrat ive &
Other_IN_CYACT10","ACCT=***")),(SRCSUM("Administra tive &
Other_IN_CYACT11","ACCT=***")),(SRCSUM("Administra tive &
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;fals e;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.