Menu

Sum with array problem using SRC/BusinessObjects software

Search this Thread

Prev

Previous Post

Next Post

Next

#2

Report Post

Posted to microsoft.public.excel.programming

[email protected] bird_222@my-deja.com is offline

bird_222@my-deja.com is offline

external usenet poster

Posts: 38

Default

Sum with array problem using SRC/BusinessObjects software

Can anyone offer any opinions on my problem?

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.

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sum with array problem using SRC/BusinessObjects software	[email protected]	Excel Worksheet Functions	1	June 8th 06 02:12 PM
Really weird problem... software glitch?	SOX101010	Excel Discussion (Misc queries)	2	January 6th 06 09:43 PM
Array problem: Key words-Variant Array, single-element, type mismatch error	davidm	Excel Programming	1	November 8th 05 04:21 AM
Problem caused by Googels personal index software!	Frank Kabel	Excel Programming	0	December 13th 04 12:29 PM
REPOST: Software recommendation (Recording software)	BruceJ[_2_]	Excel Programming	1	November 7th 03 12:52 AM

All times are GMT +1. The time now is 06:16 AM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement