Learn how to fix cells that show #VALUE after calculation.  This guide explains why the #VALUE error occurs and will teach you how to prevent it occurring on your reports…

What causes the #VALUE Error

The #VALUE error will appear as the result of a calculation within your Excel file, the cause is that one of the values that your formula is using is not in numeric format.  For example if we try to calculate 1+b then that will return the #VALUE error because the letter b is not a numeric and Excel cannot add this to 1:

How to fix the cell or cells with the error

In order to fix your spreadsheet calculations to prevent the #VALUE error you have a couple of options:

• Fix all data so that the cells which are supposed to be numeric always contain numerical data.Use a workaround formula and where the data is not a value output a default number or a blank.
• Use a workaround formula and where the data is not a value default the result to a blank or zero.

If your spreadsheet is small and static, i.e. never going to change again, then it’s usually best practice to get the data into the correct numerical format, however this is not always possible or practical so a lot of the time you will need to use a workaround formula to stop the #VALUE error.

The workaround is achieved by introducing a couple of extra statements to your formula.  The first of these is the ISERROR function, which takes the format:

=ISERROR(value)

The ISERROR function works by testing the result of a formula, or value of a cell, against whether the outcome is an error or not. If it is an error then then result is TRUE if not then it is FALSE.  See the following examples below:

Now that you can tell if your formula result is returning an error or not the next stage is to use that information within an IF statement to tell Excel what to do when there is an error, and what to do when there is not.

In plain English terms we want to say:

Result = IF FORMULA RESULT IS AN ERROR THEN CHANGE TO “something”, ELSE SHOW FORMULA RESULT

So in the original example of trying to add cell A2 and B2 together we can construct the following IF statement:

=IF(ISERROR(A2+B2),0,(A2+B2))

This statement will test for an error, if it returns an error then the result is displayed as zero, if no error exists then Excel will just do A2+B2 as we originally intended:

As the above shows we now get a zero for the first calculation of 1+b in row 2, which is expected as we know this produces an error, and in row 3 where there will be no error Excel just calculates the formula as intended.  The main thing, no more #VALUE errors!

Hopefully you can find many ways to implement this in your reporting.

Keep Excelling,