How get rid of these errors if cell has no vals?
 

What I need to do is trap for when there is no data,
when there is no data show either "Empty String" or 0, instead of the dreaded
#Value! or #DIV/0! error message. Here is the formula:

=SUMPRODUCT(--('A
Shift'!$A$4:$A$120="A"),--(TEXT('A Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"),('A
Shift'!$D$4:$D$120))/SUMPRODUCT(--('A Shift'!$A$4:$A$120="A"),--(TEXT('A
Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"))

Any assistance will be appreciated.

Thanks,

=IF(COUNTBLANK(A4:A120)=117,"",your formula)


--

Regards,

Peo Sjoblom


"Jay" wrote in message
...
What I need to do is trap for when there is no data,
when there is no data show either "Empty String" or 0, instead of the
dreaded
#Value! or #DIV/0! error message. Here is the formula:

=SUMPRODUCT(--('A
Shift'!$A$4:$A$120="A"),--(TEXT('A
Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"),('A
Shift'!$D$4:$D$120))/SUMPRODUCT(--('A Shift'!$A$4:$A$120="A"),--(TEXT('A
Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"))

Any assistance will be appreciated.

Thanks,

Thanks Peo, apprciate the help. Only, something is falling out some place.

When I use this approach, cell that do contain values, show up as blank
cells after I add my formula to the one below. Somehow, it does half of what
it's supposed to do. When there is data in the referenced cells I'd like to
get the calculated results. Only if it's blank do I not want to get the
dreaded error messages.

"Peo Sjoblom" wrote:

=IF(COUNTBLANK(A4:A120)=117,"",your formula)


--

Regards,

Peo Sjoblom


"Jay" wrote in message
...
What I need to do is trap for when there is no data,
when there is no data show either "Empty String" or 0, instead of the
dreaded
#Value! or #DIV/0! error message. Here is the formula:

=SUMPRODUCT(--('A
Shift'!$A$4:$A$120="A"),--(TEXT('A
Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"),('A
Shift'!$D$4:$D$120))/SUMPRODUCT(--('A Shift'!$A$4:$A$120="A"),--(TEXT('A
Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"))

Any assistance will be appreciated.

Thanks,

=IF(ISERROR(your function),[your text - a zero or "empty string"],(your
function))

wazooli

"Jay" wrote:

What I need to do is trap for when there is no data,
when there is no data show either "Empty String" or 0, instead of the dreaded
#Value! or #DIV/0! error message. Here is the formula:

=SUMPRODUCT(--('A
Shift'!$A$4:$A$120="A"),--(TEXT('A Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"),('A
Shift'!$D$4:$D$120))/SUMPRODUCT(--('A Shift'!$A$4:$A$120="A"),--(TEXT('A
Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"))

Any assistance will be appreciated.

Thanks,