Unwanted results from formula

  • Thread starter Thread starter Gerd
  • Start date Start date
G

Gerd

In my spreadsheet I have entered the following formula
into a cell: =D7*SHEET3!B7

The formula works fine except when there is no data in
either cell D7 or Sheet3!B7. I get #VALUE!

Is there a way that I can change the formula so that the
cell will be empty instead of saying #VALUE!

Thank you.
 
Hi Gerd
are you sure that there is no data in the other cells. I only get the
#VALUE error if in one of both cells a text is entered (e.g. a space or
the formula result ""). you can test this with
=AND(ISBANK(D7),ISBLANK('Sheet3'!B7))
this should return TRUE

But to circumvent your error, try the following
=IF(ISERROR(D7*'Sheet3'!B7),"",D7*'Sheet3'!B7)
or
=IF(AND(ISNUMBER(D7),ISNUMBER('Sheet3'!B7)),D7*'Sheet3'!B7,""

HTH
Frank
 
Back
Top