Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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, |
#2
|
|||
|
|||
=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, |
#3
|
|||
|
|||
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, |
#4
|
|||
|
|||
=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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFIll -by dragging the bottom-right corner of the cell | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |