Sometimes when you use the regular addition (+), multiplication (*), division (/), or subtraction (-) operators in the Excel formula, you get the #VALUE error. This normally occurs because one or more cells in the data range contain values of the wrong data type, for example, text values instead of numeric values.
The following example shows the #VALUE error caused by text values in two cells.
The simplest way to correct this error is to replace the wrong data with the correct values. However, sometimes you may not have the correct values and may want the Excel formula to ignore the text values and work only with the numeric ones.
How to ignore text in Excel formula
I will show you three ways you can ignore text in the Excel formula.
1. Use the functions such as SUM, AVERAGE, and PRODUCT rather than the regular addition (+), division (/), or multiplication (*) operators. These functions automatically ignore text values in the data range.
In the following example, the SUM function has automatically ignored the “Unfinished”, and “N/A” texts and summed up only the numeric values.
2. Use the SUBSTITUTE function to replace the text with an empty string. This will result in only numeric values remaining.
In the following example, the following formula has been used to get the correct total square feet of office space:
|
1 |
=SUBSTITUTE(B2, "sq ft", "")+SUBSTITUTE(B3, "sq ft","")+SUBSTITUTE(B4,"sq ft","")+SUBSTITUTE(B5,"sq ft","")+SUBSTITUTE(B6,"sq ft","") |
3. Use the IFERROR function to ignore the text values and replace them with zeros.
In the example above, the following formula has been used:
|
1 |
=SUM(IFERROR(B2*1,0),IFERROR(B3*1,0),IFERROR(B4*1,0),IFERROR(B5*1,0),IFERROR(B6*1,0)) |
Within the IFERROR function, multiplying by 1 does not alter the value but generates the #VALUE error if the value is text. The error is then replaced with a zero. If there is no error the values in the cells are used.