+ Reply to Thread
Results 1 to 2 of 2

Sum with array problem using SRC/BusinessObjects software

  1. #1

    Sum with array problem using SRC/BusinessObjects software

    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.


  2. #2

    Re: Sum with array problem using SRC/BusinessObjects software

    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.



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1