Как исправить ошибку value excel

Download PC Repair Tool to quickly find & fix Windows errors automatically

When you see the #VALUE error, it means that there is something wrong with the way the formula is typed, or there is something wrong with the cells you are referencing. The #VALUE error is very common in Microsoft Excel, so it’s kind of hard to find the exact cause for it.

Follow the solutions below to fix the #VAUE in Excel:

An unexpected text value
The input of special characters
The function argument is not the expected type
Dates stored as text.

1] An unexpected text value

How to fix the #VALUE error in Excel

One of the issues that may trigger the error is when you try to type the reference as =B2+B3+B4 with a text in the cost area by mistake.

To fix this issue, remove the text and add the cost price.

If you are using the Sum function, it will ignore the text and add the numbers of the two items in the spreadsheet.

2] The input of special characters

Inputting a special character into the data that you want to calculate can cause the #VALUE error.

To fix the issue, remove the space character.

3] The function argument is not the expected type

The error can occur when you input the wrong data that does not match with the function reference, for example, in the photo above the text, Orange does not go along with the YearFrac function. YearFrac is a function that returns a decimal value that represents the fractional years between two dates.

To fix this issue, enter the right data into the cell.

4] Dates stored as text

If dates are written in text, as seen in the photo above, you will see the Value error occur.

Enter the format correctly.

We hope this tutorial helps you understand how to fix the #Value error in Excel; if you have questions about the tutorial, let us know in the comments.

What is error in spreadsheet?

In Microsoft Excel, users will sometimes experience errors in Excel formulas. Errors usually occur when a reference becomes invalid (when there is an incorrect reference in your functions, or columns are rows are removed.)

How do you show errors in Excel?

If you have lots of data or a large table containing data on your spreadsheet and you want to find errors in it, follow the steps below.

Click the Formulas tab.
Click the Error Checking button in the Formula Auditing group and click Error Checking in the drop-down list.
An Error Checking dialog box will appear.
Click Next.
Then, you will see the error in the spreadsheet.

Hope this helps.

Ezoic

Shantel is a university student studying for Bachelor of Science in Information Technology. Her goal is to become a Database Administrator or a System Administrator. She enjoys reading and watching historical documentaries and dramas.

Источник

Download PC Repair Tool to quickly find & fix Windows errors automatically

Follow the solutions below to fix the #VAUE in Excel:

An unexpected text value
The input of special characters
The function argument is not the expected type
Dates stored as text.

1] An unexpected text value

How to fix the #VALUE error in Excel

One of the issues that may trigger the error is when you try to type the reference as =B2+B3+B4 with a text in the cost area by mistake.

To fix this issue, remove the text and add the cost price.

If you are using the Sum function, it will ignore the text and add the numbers of the two items in the spreadsheet.

2] The input of special characters

Inputting a special character into the data that you want to calculate can cause the #VALUE error.

To fix the issue, remove the space character.

3] The function argument is not the expected type

To fix this issue, enter the right data into the cell.

4] Dates stored as text

If dates are written in text, as seen in the photo above, you will see the Value error occur.

Enter the format correctly.

We hope this tutorial helps you understand how to fix the #Value error in Excel; if you have questions about the tutorial, let us know in the comments.

What is error in spreadsheet?

How do you show errors in Excel?

If you have lots of data or a large table containing data on your spreadsheet and you want to find errors in it, follow the steps below.

Click the Formulas tab.
Click the Error Checking button in the Formula Auditing group and click Error Checking in the drop-down list.
An Error Checking dialog box will appear.
Click Next.
Then, you will see the error in the spreadsheet.

Hope this helps.

Ezoic

Источник

#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.

Get live and free answers on Excel

Fix the error for a specific function

Don’t see your function in this list? Try the other solutions listed below.

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:

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.
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.
On the Formats tab, click Additional settings.
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.
Click OK.
Open your workbook. If a cell contains a #VALUE! error, double-click to edit it.
If there are commas where there should be minus signs for subtraction, change them to minus signs.
Press ENTER.
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

Double-click a date that is being used in a subtraction formula.
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:

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.
Select the column that contains the date by clicking its column header.
Click Data > Text to Columns.
Click Next twice.
On Step 3 of 3 of the wizard, under Column data format, click Date.
Choose a date format, and then click Finish.
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:

Create a formula like this: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
The result would be 12/31/2017.
If you want the format to appear like dd/mm/yy, press CTRL+1 (or + 1 on the Mac).
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 , 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 , 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).

Ситуация 1. Формула СУММЕСЛИ на другой файл

Представим себе обычную ситуацию, когда у нас есть файл с базой данных, в которой представлены продажи за месяц и нам нужно в другом файле рассчитать сумму продаж за каждый месяц.