Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
xlguy
 
Posts: n/a
Default Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it !


Hi All,

I'm in the process of converting a large Lotus 1-2-3 model into
Excel....and aside from all the other problems (that I should have no
problems resolving), this one has really got me. Hoping someone here
could help.

The problem :
The original Lotus model had the following DSUM formula.....which
obviously did not convert properly to Excel.....and which I presume
must be replaced with an Excel array formula (correct me if I'm wrong
somebody...maybe there's a better/different way to do it) ;
@DSUM($COST DATA,"ytd",TYPE=$A5#AND#DEPT=X$3)

Now, one of the sheets (in the model) has 10,000 odd rows, and I have
created 3 range names that start from row 4 and go down all the way to
row 10,000 let's say.....here's the 3 range name definitions :

xDEPT='8xx Raw Data'!$I$4:$I$10000
xTYPE='8xx Raw Data'!$I$4:$J$10000
xYTD='8xx Raw Data'!$I$4:$K$10000

Now, on a separate sheet I have the following array formula (which is
supposed to sum all the YTD numbers for a given DEPT and TYPE :)
{=SUM((xTYPE=$A5)*(xDEPT=Y$3)*xYTD)}
where $A5 contains say "Salaries", and Y$3 contains say
"Accounting"

The above array formula (correctly) gives me a (summed) amount as long
as the 3 range name definitions have the last row number less than
around 2800. If I try to increase the number to 2850 or so....I get the
#VALUE! error.

Question : is there something I can do to fix this problem...or at
least a way to get around it ?

The wierd thing is that Lotus can (easily) do it without even batting
an eyelid....however Excel just rolls over and dies (at less than the
half-way mark)

I hope the answer isn't for me to use Excel's DSUM function (b'coz
that's a b*t*h to setup)

Any ideas guys ? Really appreciate it !

Later,

C


--
xlguy
------------------------------------------------------------------------
xlguy's Profile: http://www.excelforum.com/member.php...o&userid=29666
View this thread: http://www.excelforum.com/showthread...hreadid=493783

  #2   Report Post  
Posted to microsoft.public.excel.misc
Pete
 
Posts: n/a
Default Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it !

Check your data - perhaps you don't have numeric values in some of the
cells between 2800 and 2850. I use array formulae over much larger
ranges than this.

Pete

  #3   Report Post  
Posted to microsoft.public.excel.misc
xlguy
 
Posts: n/a
Default Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it !


....there were some non-numeric values in the YTD (amounts)
column....and therefore the problem. It works just fine once I replace
those values with 0.

BTW, I had extracted that portion of data rows 2800 to 2900 to a
separate workbook (as values)...and still couldn't catch the problem
there.

How strange is it that Lotus can easily get a sum of a column that
contains both numeric and non-numeric data....while Excel cringes !

Later,

C


--
xlguy
------------------------------------------------------------------------
xlguy's Profile: http://www.excelforum.com/member.php...o&userid=29666
View this thread: http://www.excelforum.com/showthread...hreadid=493783

  #4   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it !

Actually Excel can handle this as well albeit not as easily as Lotus

=SUMPRODUCT(--(xTYPE=$A5),--(xDEPT=Y$3),xYTD)

will not balk at text values in xYTD


--

Regards,

Peo Sjoblom



"xlguy" wrote in
message ...

...there were some non-numeric values in the YTD (amounts)
column....and therefore the problem. It works just fine once I replace
those values with 0.

BTW, I had extracted that portion of data rows 2800 to 2900 to a
separate workbook (as values)...and still couldn't catch the problem
there.

How strange is it that Lotus can easily get a sum of a column that
contains both numeric and non-numeric data....while Excel cringes !

Later,

C


--
xlguy
------------------------------------------------------------------------
xlguy's Profile:

http://www.excelforum.com/member.php...o&userid=29666
View this thread: http://www.excelforum.com/showthread...hreadid=493783



  #5   Report Post  
Posted to microsoft.public.excel.misc
xlguy
 
Posts: n/a
Default Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it !


...wow ! it sure did work...and it's not even an array formula, right ?
So it must be much quicker...as far as calc times go.

Would you recommend that I replace all my array formulas with the
SUMPRODUCT solution you gave ?

Thanks again,


Later


--
xlguy
------------------------------------------------------------------------
xlguy's Profile: http://www.excelforum.com/member.php...o&userid=29666
View this thread: http://www.excelforum.com/showthread...hreadid=493783



  #6   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it !

I don't think it's faster, it's easier to enter and it won't balk if there
is text in the summing range, that might be a reason as good as any to
change.
Pivot tables are probably the fastest way to go if applicable

--

Regards,

Peo Sjoblom


"xlguy" wrote in
message ...

..wow ! it sure did work...and it's not even an array formula, right ?
So it must be much quicker...as far as calc times go.

Would you recommend that I replace all my array formulas with the
SUMPRODUCT solution you gave ?

Thanks again,


Later


--
xlguy
------------------------------------------------------------------------
xlguy's Profile:

http://www.excelforum.com/member.php...o&userid=29666
View this thread: http://www.excelforum.com/showthread...hreadid=493783



  #7   Report Post  
Posted to microsoft.public.excel.misc
Harlan Grove
 
Posts: n/a
Default Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it !

Peo Sjoblom wrote...
....
Pivot tables are probably the fastest way to go if applicable

....

Except for the fact that Excel doesn't regenerate pivot tables as part
of automatic recalculation. Syncing pivot tables is a manual process.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Suppress array formula #NA [email protected] Excel Worksheet Functions 4 November 15th 05 06:17 PM
Editing Array Formula [email protected] Excel Worksheet Functions 7 August 28th 05 06:46 AM
referencing the value of a cell containing an array formula KR Excel Worksheet Functions 4 July 5th 05 06:15 PM
problem with Array Formula OrdOff Excel Worksheet Functions 2 June 30th 05 04:57 PM
What instead of an array formula part 2 Reg Besseling Excel Discussion (Misc queries) 2 December 10th 04 08:35 AM


All times are GMT +1. The time now is 04:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"