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