Saturday, January 28, 2017

How to correct a #VALUE! error

How to correct a #VALUE! error

#VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." The error is very general, and it can be hard to find the exact cause of it. The information on this page shows common problems and solutions for the error. You may need to try one or more of the solutions to fix your particular error.

Fix the error for a specific function

Problems with subtraction

If you're new to Excel, you might be typing a formula for subtraction incorrectly. Here are two ways to do it:

Subtract a cell reference from another

Cell D2 with $2,000.00, Cell E2 with $1,500.00, Cell F2 with formula: =D2-E2 and result of $500.00

Type two values in two separate cells. In a third cell, subtract one cell reference from the other. In this example, cell D2 has the budgeted amount, and cell E2 has the actual amount. F2 has the formula =D2-E2.

Or, use SUM with positive and negative numbers

Cell D6 with $2,000.00, Cell E6 with $1,500.00, Cell F6 with formula: =SUM(D6,E6) and result of $500.00

Type a positive value in one cell, and a negative value in another. In a third cell, use the SUM function to add the two cells together. In this example, cell D6 has the budgeted amount, and cell E6 has the actual amount as a negative number. F6 has the formula =SUM(D6,E6).

If you're using Windows, you might get the #VALUE! error when doing even the most basic subtraction formula. The following might solve your problem:

  1. First do a quick test. In a new workbook, type a 2 in cell A1. Type a 4 in cell B1. Then in C1 type this formula =B1-A1. If you get the #VALUE! error, go to the next step. If you don't get the error, try other solutions on this page.

  2. In Windows, open your Region control panel.

    • Windows 10: Click Start, type Region, and then click the Region control panel.

    • Windows 8: At the Start screen, type Region, click Settings, and then click Region.

    • Windows 7: Click Start and then type Region, and then click Region and language.

  3. On the Formats tab, click Additional settings.

  4. Look for the List separator. If the List separator is set to the minus sign, change it to something else. For example, a comma is a common list separator. The semicolon is also common. However, another list separator might be more appropriate for your particular region.

  5. Click OK.

  6. Open your workbook. If a cell contains a #VALUE! error, double-click to edit it.

  7. If there are commas where there should be minus signs for subtraction, change them to minus signs.

  8. Press ENTER.

  9. Repeat this process for other cells that have the error.

Subtract a cell reference from another

Cell D10 with 1/1/2016, Cell E10 with 4/24/2016, Cell F10 with formula: =E10-D10 and result of 114

Type two dates in two separate cells. In a third cell, subtract one cell reference from the other. In this example, cell D10 has the start date, and cell E10 has the End date. F10 has the formula =E10-D10.

Or, use the DATEDIF function

Cell D15 with 1/1/2016, Cell E15 with 4/24/2016, Cell F15 with formula: =DATEDIF(D15,E15,"d") and result of 114

Type two dates in two separate cells. In a third cell, use the DATEDIF function to find the difference in dates. For more information on the DATEDIF function, see Calculate the difference between two dates.

Make your date column wider. If your date is aligned to the right, then it's a date. But if it's aligned to the left, this means the date isn't really a date. It's text. And Excel won't recognize text as a date. Here are some solutions that can help this problem.

Check for leading spaces

  1. Double-click a date that is being used in a subtraction formula.

  2. Put your cursor at the beginning and see if you can select one or more spaces. Here's what a selected space looks like at the beginning of a cell: Cell with space selected before 1/1/2016

    If your cell has this problem, proceed to the next step. If you don't see one or more spaces, go to the next section on checking your computer's date settings.

  3. Select the column that contains the date by clicking its column header.

  4. Click Data > Text to Columns.

  5. Click Next twice.

  6. On Step 3 of 3 of the wizard, under Column data format, click Date.

  7. Choose a date format, and then click Finish.

  8. Repeat this process for other columns to ensure they don't contain leading spaces before dates.

Check your computer's date settings

Excel uses your computer's date system. If a cell's date isn't entered using the same date system, then Excel won't recognize it as a true date.

For example, let's say that your computer displays dates as mm/dd/yyyy. If you typed a date like that in a cell, Excel would recognize it as a date and you'd be able to use it in a subtraction formula. However, if you typed a date like dd/mm/yy, then Excel wouldn't recognize that as a date. Instead, it would treat it as text.

There are two solutions to this problem: You could change the date system that your computer uses to match the date system you want to type in Excel. Or, in Excel you could create a new column and use the DATE function to create a true date based on the date stored as text. Here's how to do that assuming your computers date system is mm/dd/yyy and your text date is 31/12/2017 in cell A1:

  1. Create a formula like this: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

  2. The result would be 12/31/2017.

  3. If you want the format to appear like dd/mm/yy, press CTRL+1 (or Image of the MAC Command button icon + 1 on the Mac).

  4. Choose a different locale that uses the dd/mm/yy format, for example, English (United Kingdom). When you're done applying the format, the result would be 31/12/2017 and it would be a true date, not a text date.

Note: The formula above is written with the DATE, RIGHT, MID, and LEFT functions. Please notice that it is written with an assumption that the text date has two characters for days, two characters for months, and four characters for year. You may need to customize the formula to suit your date.

Problems with spaces and text

Often #VALUE! occurs because your formula refers to other cells that contain spaces, or even trickier: hidden spaces. These spaces can make a cell look blank, when in fact they are not blank.

1. Select referenced cells

Column selected

Find cells that your formula is referencing and select them. In many cases removing spaces for an entire column is a good practice because you can replace more than one space at a time. In this example, clicking the E selects the entire column.

2. Find and replace

Home tab > Find & Select > Replace

On the Home tab, click Find & Select > Replace.

3. Replace spaces with nothing

Find what box containing a space, Replace with containing nothing

In the Find what box, type a single space. Then, in the Replace with box, delete anything that might be there.

4. Replace or Replace all

Replace all button

If you are confident that all spaces in the column should be removed, click Replace All. If you'd like to step through and replace spaces with nothing on an individual basis, you can click Find next first, and then click Replace when you are confident the space isn't needed. When you're done, the #VALUE! error may be resolved. If not, go to the next step.

5. Turn on the filter

Home > Sort & Filter > Filter

Sometimes there are hidden characters other than spaces that can make a cell appear blank, when it's not really blank. Single apostrophes within a cell can do this. To get rid of these characters in a column, turn on the filter by going to Home > Sort & Filter > Filter.

6. Set the filter

Filter menu with the Select all checkbox unselected, (Blanks) checkbox selected

Click the filter arrow Filter arrow , and then deselect Select all. Then, select the Blanks checkbox.

7. Select any unnamed checkboxes

An unnamed checkbox selected

Select any check boxes that don't have anything next to them, like this one.

8. Select blank cells, and delete

Filtered blank cells selected

When Excel brings back the blank cells, select them. Then press the Delete key. This will clear any hidden characters in the cells.

9. Clear the filter

Filter menu, Clear filter from ...

Click the filter arrow Filter arrow , and then click Clear filter from... so that all cells are visible.

10. Result

#VALUE! error gone, and replaced with the formula result. Green triange in cell E4

If spaces were the culprit of your #VALUE! error then hopefully your error has been replaced by the formula result, as shown here in our example. If not, repeat this process for other cells that your formula refers to. Or, try other solutions on this page.

Note: In this example, notice that cell E4 has a green triangle and the number is aligned to the left. This means the number is stored as text. This may cause more problems later. If you see this problem, we recommend converting numbers stored as text to numbers.

Text or special characters within a cell can cause the #VALUE! error. But sometimes it's hard to see which cells have these problems. Solution: Use the ISTEXT function to inspect cells. Please note that ISTEXT doesn't resolve the error, it simply finds cells that might be causing the error.

Example with #VALUE!

H4 with =E2+E3+E4+E5 and result of #VALUE!

Here's an example of a formula that has a #VALUE! error. This is likely due to cell E2. There is a special character that appears as a small box after "00." Or as the next picture shows, you could use the ISTEXT function in a separate column to check for text.

Same example, with ISTEXT

Cell F2 with =ISTEXT(E2) and result of TRUE

Here the ISTEXT function was added in column F. All cells are fine except the one with the value of TRUE. This means cell E2 has text. To resolve this, you could delete the cell's contents and retype the value of 1865.00. Or you could also use the CLEAN function to clean out characters, or use the REPLACE function to replace special characters with other values.

After using CLEAN or REPLACE, you'll want to copy the result, and use Home > Paste > Paste Special > Values. You might also have to convert numbers stored as text to numbers.

Formulas with math operations like + and * may not be able to calculate cells that contain text or spaces. In this case, try using a function instead. Functions will often ignore text values and calculate everything as numbers, eliminating the #VALUE! error. For example, instead of =A2+B2+C2, type =SUM(A2:C2). Or, instead of =A2*B2, type =PRODUCT(A2,B2).

Other solutions to try

Select the error

Cell H4 with formula =E2+E3+E4+E5 and result of #VALUE!

First select the cell with the #VALUE! error.

Click Formulas > Evaluate Formula

Evaluate formula dialog with " "+E3+E4+E5

Click Formulas > Evaluate Formula > Evaluate. Excel will step through the parts of the formula individually. In this case the formula =E2+E3+E4+E5 breaks because of a hidden space in cell E2. You can't see the space by looking at cell E2. However, you can see it here. It shows as " ".

Sometimes you just want to replace the #VALUE! error with something else like your own text, a zero or a blank cell. In this case you can add the IFERROR function to your formula. IFERROR will check to see if there's an error, and if so, replace it with another value of your choice. If there isn't an error, your original formula will be calculated. IFERROR will only work in Excel 2007 and later. For earlier versions you can use IF(ISERROR()).

Warning: IFERROR will hide all errors, not just the #VALUE! error. Hiding errors isn't recommended because an error is often a sign that something needs to be fixed, not hidden. We don't recommend using this function unless you are absolutely certain your formula works the way that you want.

Cell with #VALUE!

Cell H4 with =E2+E3+E4+E5 and result of #VALUE!

Here's an example of a formula that has a #VALUE! error due to a hidden space in cell E2.

Error hidden by IFERROR

Cell H4 with =IFERROR(E2+E3+E4+E5,"--")

And here's the same formula with IFERROR added to the formula. You can read the formula as: "Calculate the formula, but if there's any kind of error, replace it with two dashes." Note that you could also use "" to display nothing instead of two dashes. Or you could substitute your own text, such as: "Total Error".

Unfortunately, you can see that IFERROR doesn't actually resolve the error, it simply hides it. So be certain that hiding the error is better than fixing it.

Your data connection may have become unavailable at some point. To fix this, restore the data connection, or consider importing the data if possible. If you don't have access to the connection, ask the creator of the workbook to make a new file for you. The new file ideally would only have values, and no connections. They can do this by copying all the cells, and pasting only as values. To paste as only values, they can click Home > Paste > Paste Special > Values. This eliminates all formulas and connections, and therefore would also remove any #VALUE! errors.

If you're not sure what to do at this point, you can search for similar questions in the Excel Community Forum, or post one of your own.

Link to the Excel Community Forum

Post a question in the Excel community forum

See Also

Overview of formulas in Excel

How to avoid broken formulas

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)