by using a MIN function call
perhaps a MAX() would be better for the OP's purposes.

"Rick Rothstein (MVP - VB)" wrote:

Also, looking at the original formula (quickly) and seeing what it does
(make a 0 value into the near 0 value of 1E-50), it would seem you could
eliminate one of the SUMPRODUCT calculations by using a MIN function call
instead....

=MIN(1E-50,SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*Qt yProduced))

Rick


"Dave Peterson" wrote in message
...
If those ranges are larger than what they need to be, it'll take longer.

But is there a real reason why you're replacing 0 with that very small
number.
If your =sumproduct() doesn't equal 0, then your formula does the
calculation
twice.

If you really have to have that 1e-50 shown, then maybe you can use two
cells--one for the =sumproduct() formula and one for the check if 0

=if(a2=0,1e-50,a2)
(and hide that column (A in my example)

diaare wrote:

I have a worksheet filled with formulas similiar to this one:

=IF(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))=0,1E-50,(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))))

Where ProductionDate, LineID, and QtyProduced are neamed ranges on a
different tab of the same workbook.

If is very very slow to calculate.

Does anyone have a suggestion on how to speed things up?

Thanks,
Diane

--

Dave Peterson