Hiding "error codes" in cells

[Guest]

I have a workbook made up of several individual
worksheets, each with a column of data expressed in time
format HH:MM. At the bottom of this column is the average
Time.
Averages in these worksheets are placed by a link into
applicable cells in the Summary worksheet of this
workbook.

If the individual worksheet has no data entered for a
given period - the Average cell is left blank.

The problem is that on the summary worksheet the cell
which is linked to the individual worksheet by the link
=Day4!$F$40 displays the error #DIV/0! and this
compromises the rest of the summarised data total

Is there a way of reflecting no data as a blank cell in
the Summary worksheet even though the link is still there?
Bill

 

[Ragdyer]

One way:

=IF(ISERR(Day4!$F$40),"",Day4!$F$40)

 

[Norman Harker]

Hi Bill!

Rather than just hiding the error, I think that it is best to work out
why it is occurring and address that. Error codes have their uses in
returning bad entries and you don't want to hide them when you can
address them.

Consider the entry:

C1:
=A1/B1

No problem if B1 contains data but if B1 is empty pending data entry
it will return the dreaded #Div/0! and the sum of column C similar
entries will be #Div/0! Not want you want if you want a dynamic sum of
column C amounts as and when you get data in column B.

But:

=IF(ISBLANK(B1),"",A1/B1)
Returns an empty string pending data entry in B1

But if you put text in B1 in error the formula returns #VALUE! and you
can see that you have a problem that needs addressing.

Using:

=IF(ISERROR(A1/B1),"",A1/B1)
Returns an empty string and hides your problem pending data entry in
B1, but it also hides the #VALUE! when the wrong type of data is put
in B1.

Also note the difference between ISERR and ISERROR. ISERR will return
TRUE for any error except #N/A (eg a valid response from a VLOOKUP).
ISERROR will return TRUE for all error codes.