I need a blank value or zero instead of a #VALUE! error.

[Peebs]

I have automated a sheet to populate the value of a specific item using the
IF, VLOOKUP formulas.

Everything is great, but at the very end I am getting an error in the last
field

- F13 is empty until i enter a number (quantity)

- in G13 I have the following which auto pulls up a price for a # entered in
a previous cell

=IF(D13="","",VLOOKUP($E13,$E69:$F$144,COLUMN(B51),0))

- in H13 I have the following,

=F13*G13

If F13 and G13 are blank, I get the "#VALUE1" error. I need that to be
blank or zero, but can't figure out how. Please help!


Column | F13 | G13 | H13
|
Formula | | (=IF(D13..etc.) | (=F13*G13) |
Excel Shows | | | #VALUE!
|

 

[JE McGimpsey]

Since you're getting the #VALUE! error, F13 or G13 (or both) aren't
really blank. They most likely were "cleared" using the space bar, which
inserts a space character (i.e., text). The multiplication operator
chokes on text, giving the error.

Try:

=IF(COUNT(F13:G13)=2,F13*G13,0)

 

[Jim May]

Try =IF(ISERROR(F13*G13),"",F13*G13)

 

[JE McGimpsey]

Note that ISERROR() is a pretty blunt instrument - it doesn't
distinguish the source of the error.

In general it's better to try to limit ignoring errors to those that are
expected (e.g.,ISNA() for the #N/A returned by a failed MATCH or
VLOOKUP) so that errors generated elsewhere aren't masked.