You can use the following basic syntax to perform some calculation in Excel and ignore any #VALUE! errors that may occur:
IFERROR(some calculation, "")
This syntax simply replaces #VALUE! with a blank value.
The following examples show how to use this syntax in different scenarios.
Example 1: Perform Multiplication & Ignore #VALUE! Error
Suppose we have a list of values in column A and attempt to multiply each value by 2 in column B:
Notice that for each of the values in column A that aren’t a numeric value, the result of the multiplication in column B is a #VALUE! error.
We can use the following formula to perform this exact same multiplication but replace any #VALUE! errors with a blank value:
=IFERROR(A2*2, "")
We can type this formula into cell B2 and then click and drag it down to each remaining cell in column B:
Notice that any calculations that would have resulted in a #VALUE! error simply display a blank space now.
Example 2: Use FIND Function & Ignore #VALUE! Error
Suppose we have a list of basketball team names in column A and we attempt to use the FIND function in column B to find the starting position of the string “ets” within each team name:
Notice that for each team that doesn’t have “ets” in the name that the formula returns a #VALUE! error.
We can use the following formula to perform this exact same function but replace any #VALUE! errors with a blank value:
=IFERROR(FIND("ets", A2), "")
We can type this formula into cell B2 and then click and drag it down to each remaining cell in column B:
Notice that any rows that would have resulted in a #VALUE! error simply display a blank space now.
Note: You can also display a different value instead of a blank space by simply changing the value in the last argument of the IFERROR() function to something other than double quotation marks.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Replace #N/A Values in Excel
How to Ignore #DIV/0! When Using Division in Excel
How to Ignore #N/A Values When Using Formulas in Excel