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

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

....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

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" <[email protected]> wrote in
message news:[email protected]...
>
> ...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
>

...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

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" <[email protected]> wrote in
message news:[email protected]...
>
> ..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
>