Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a blank value or zero instead of a #VALUE! error.
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! | |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a blank value or zero instead of a #VALUE! error.
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) In article , Peebs wrote: - 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a blank value or zero instead of a #VALUE! error.
Try =IF(ISERROR(F13*G13),"",F13*G13)
"Peebs" wrote: 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! | |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a blank value or zero instead of a #VALUE! error.
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. In article , Jim May wrote: Try =IF(ISERROR(F13*G13),"",F13*G13) "Peebs" wrote: 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! | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is blank AND is error | Excel Worksheet Functions | |||
replace error with blank | Excel Worksheet Functions | |||
blank error | Excel Worksheet Functions | |||
error when blank - please help | Excel Discussion (Misc queries) | |||
#DIV/0! Error - Need to display 0 or blank | Excel Worksheet Functions |