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

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


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

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

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

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

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.