Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it !

[xlguy]

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

[Pete]

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

[xlguy]

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

[Peo Sjoblom]

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
>

[xlguy]

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

[Peo Sjoblom]

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
>

[Harlan Grove]

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.