There are many common problems that can occur that would result in a #Value! error and they all need to be handled differently. Spaces and Text Often when creating excel documents we rush or jam our keyboards in frustration. Sometimes are fingers run from us and type values in hidden unknown depths of excel. Many times, #Value! errors occur due to values being stored as text when they should be numerical. Spaces will certainly stop things from working too. They are various methods to check for things like this.
#Value Error Unresolved Using SUM and Subtraction
And now the error goes away.
Sometimes you will get values that are stored as text incidentally. You will see a small red error triangle in the corner of the cell. When you hover over it you will see that it says, “Number Stored as Text”. If this is causing an issue with your formula, simply select “Convert to Number” and this should resolve your issue.
Other formulas that may cause a #Value! error are subtraction formulas, referencing formulas and query formulas. Be sure you don’t have incorrect spacing, syntax, referencing or arguments. Other ways to handle #Value! Errors Sometimes the error is not as simple to figure out as others. Maybe you have a complex formula or you are referencing other sheets using a formula and can’t see everything at the same time. You can try the below solution to assist you in figuring out the problem.
Select the cell showing the #Value! Error and select “Show calculation steps”.You’re going to get a popup box that is going to assist you in evaluating your formula. Once the box is open look at the info provided in the “evaluation:” box. Items that are underlined are going to be the first part of the formula that gets evaluated. If you have a simple formula like the one shown, it will likely only have one step to evaluate.Sometimes when viewing the calculation steps it will show the result of the formula. Hit evaluate and then restart. This will show you the formula from the beginning.You’ll see that in the next example the first SUM portion of the example completes successfully. The subtraction portion is the portion that will give the error message such as the image to the right.
Once you figure out the error message and where the issue is coming from you can simply fix it and move on with your project. Although not always recommended, you can use the IFERROR() function to handle the #Value! Error message. The IFERROR() function basically says, “If there is an error, skip this and ‘do’ this”. In the below example you’ll see that we want to see the word “Error” in the event of an error. There are many ways to handle and use this although it is important to understand what IFERROR() really does. Often times this will make it difficult to notice or see that you have an issue somewhere in your workbook and can often times make tracing errors more difficult The IFERROR() function will also handle other errors as well such as #NA, #REF and a few others. It’s best practice to only use this function if necessary or if you are expecting these types of things to happen. Simply “wrap” IFERROR() around the entire formula as shown.
How to Fix ‘An Array Value Could not be Found’ Error on ExcelHow to Fix a Sharing Violation Error in ExcelHow to Fix “#Spill Error” in Microsoft Excel?How to Fix Run Time Error 1004 in Excel
