Working with Excel sometimes you will encounter #VALUE! but you guys don't know how to fix this. There are many causes of #VALUE! Error, below the article will give some of the most common Value errors in Excel and how to fix those errors, please refer.
Error #VALUE with spaces
Many times, your formula involves cells that contain hidden spaces, which appear to be blank, but are not white in reality, so #VALUE!
To fix this problem, you can remove these hidden spaces as follows:
Step 1 : Select the referenced cells, then press Ctrl + H to open the replacement dialog box.
Step 2: In the Find and Replace dialog box, enter a space in the Find what box and in Replace with you do not enter anything. Then click Replace All to remove all spaces.
Many times, there are hidden characters other than spaces that make the field look blank but actually it is not empty.To remove these characters, you can continue with the next steps.
Step 3: On the area you have selected you select Data -> Filter to open the filter.
Step 4 : Select the filter icon on the column header and uncheck the Select All box , then check in the Blanks checkbox or the checkbox has no content.
Step 5 : The filter results appear, select the blank cells and press the Delete key . So you deleted all the hidden characters in the cells.
Step 6 : Clear the filter by clicking the filter icon on the title box and selecting Clear Filter From .
So you will get the following results:
Error #VALUE! when there is text or special characters
If a cell contains text or special characters, an #VALUE! Error will be generated. but it's hard to know which cell contains text or special characters if the data list is very long.
To search for cells containing text or special characters you can fix the #VALUE! then you use the ISTEXT function to check the cells.
Step 1 : Create 1 more column next to the data column you need to check, in the first cell of the column you enter the formula = ISTEXT (C6)
Step 2 : Copy the formula down all remaining cells, ISTEXT () will return TRUE if the cell contains text or special characters, return FALSE if the cell does not contain text or characters especially.
Step 3 : Next, you select the cells containing TRUE and delete the text or special characters, the function will no longer #VALUE!
You also need to delete the column you created in step 1, and the result will be no #VALUE! Error.
Value error occurs when using the numeric operators (+, *)
For example, you use the + operator to add the sales of two days without a sales day. You will get the #VALUE error.
To fix this, replace the + operator with the SUM function formula, the SUM function will ignore the string and return the result.
Similar to the * operator, if you have a #VALUE error, you can replace it with the PRODUCT function to multiply in Excel.
Value error occurs when using the Average, Sum function
If the average or sum contains the #Value! then the result of Average, Sum will also return the #VALUE error.
To fix this, you can use an array formula in combination with two groups of functions: If + Isseror or Iferror .
- SUM function
Method 1
{= SUM (IFERROR (C6: C8; ""))}
Because this is an array formula, once you have entered the SUM function, press Ctrl + Shift + Enter to display the curly braces.
Method 2
{= SUM (IF (ISERROR (C6: C8); ""; C6: C8))}
The ISERROR function will determine if any value in the range C6: C8 is an error value, it will return a blank result, so the values for the final calculation SUM will remove all error values.
- AVERAGE function
Similar to the SUM function, the AVERAGE function you also use the array formula IF and ISERROR.
Method 1
{= AVERAGE (IFERROR (C6: C8; ""))}
Method 2
{= AVERAGE (IF (ISERROR (C6: C8); ""; C6: C8))}
Similar to some other functions (IF, CONCATENATE .) you get #VALUE! When referring to error values, you can also handle errors by using the IF, ISERROR and ISERR or IFERROR functions in the formula.
Above the article has taught you some ways to fix Value errors in Excel, hope you will be able to fix Value errors when you encounter this error. Good luck!