It happens, copied the table in Excel, and the numbers in it are not considered formulas do not work inExcel. One of the reasons may be that the values \u200b\u200bof the cells in the text format. We remember, in Excel there are two formats - the format of the cells and the format of values. Read more about this in the article "Convert a date to Excel text."
So consider why inExcel does not work formula.
You need to fold the upper numbers in the cells. In cells, the values \u200b\u200bare written in a text format with the installation between the strings of non-fired signs. We need to convert a text format of values \u200b\u200bin numeric and select the first number in the cell. We have such a copied table of 1C.
First stage.
We transform the meaning of the cells in the numeric format.
Select cells. On the "Home" tab, in the Edit section, click on the "Find" button.
In the dialog box that appears in the "Find" row, we put a comma. " In the string "Replace" do not put anything. 
Press the "Replace All" button. It turns out.
Second phase.
In the same window, "find and replace" (we did not remove it and the cells were allocated), in the "Find" string put the point. And in the string "Replace" put the comma. Click "Allocate everything".
It turned out so.
We remove the "Find and Replace" window.
Third stage.
How to highlight the first number in the cellExcel.
In cell B1 we write such a formula. \u003d Levsimv (A1; Find (symbol (10); A1) -1)
With this formula, we highlighted the first number from the cell. Copy the column formula. It turned out so.
In the B4 cell we wrote the formula of addition. \u003d B1 + B2 + B3
Avosumn does not work, but, manually written formulas.
In Excel, you can install a link to another sheet, range, cell, site, etc. in the cell. When you click on this link, Excel goes through it. But, how to highlight the cell itself, not a hyperlink? Read this article "
Hello, friends. Did you have that, after entering the formula, the formula itself is displayed in the cell instead of the calculation result? It discourages a bit, because you did everything right, and they got it unclear what. How to force the program to calculate the formula in this case?
First you need to deal with the causes of such behavior, after which the situation is corrected. And the reasons may be so.
Included mapping formulas
In Excel there is a computing check mode. When it is turned on, you see on a sheet of formula. How and why apply the display of formulas, I told in. Check may be activated, then disconnect. On the ribbon there is a button Formulas - dependencies of formulas - show formulas. If it is enabled - click on it to disable.
Frequency formulas are often included by randomly pressing the Ctrl + `key combination.
Formula is perceived by the program as text
If Excel believes that in the cell a text string, it will not calculate the value, but simply displays the contents on the screen. There may be several reasons for any:
- In the cell installed text format. For the program, this is a signal that the contents need to be displayed in the same form in which it is stored.
Change the format on the numeric, as stated. - No sign "\u003d" first formula. As I told in, any calculations begin with the "equal" sign.
If it is missing - the content will be perceived as text. Add the equality sign and recalculate the formula. - Space before "\u003d". If the gap is randomly specified before the equality sign, it will also be perceived as text.
This error is difficult to notice, so you will have to be checked manually. Remove the extra space and recalculate. - Formula enclosed in quotation. This error happens when we work with text data. Of course, the information in quotes is interpreted as text.
Remove extra quotes, press ENTER to recalculate.
I listed all the common cases when the formula is displayed instead of the result. I think your case will be in the list of the list, it will not be difficult to fix the problem. If it did not work, write in the comments.
Promises to be very interesting. We will look at some simple techniques for quickly writing formulas. If you bring them to automatism, your work will be completed easier and faster. Subscribe to updates to not miss!
Today I encountered the situation when excel During stretching, formulas did not work. There were two columns and it was necessary to calculate the difference between the first two columns in the third column. As a result, the formula is made and stretches down to the end, but the result from the first cell is copied to the other cells.
1 option
It is likely that you are disabled automatic leaf recalculation, for this, simply press the key F9.
2 option for all versions of Office
Check that the cell format would not be text (due to this may not work calculations and according to the formula)
Ps to fix it, highlight cells and Choose common format (see Figure Format Cells)
3 option for Office 2010
Check the settings for calculating the formulas.
To do this, click on the File menu-\u003e parameters-\u003e Formulas and in the "Calculation Parameters" menu, see where you have a check mark (see a picture should be so)
4 Option for Office 2007/2010
For Office 2007 the path is Formula-\u003e Calculation parameters-\u003eand put a daw on Automatically except data tables.(see Figure below)
The Excel application is used not only to create tables. Its main purpose is the calculation of numbers by formulas. It is enough to enter into the cells new values \u200b\u200band the system automatically recalculates them. However, in some cases the calculation does not occur. Then, it is necessary to find out why Excel does not consider the amount.
The main causes of malfunction
Excel may not count the amount or formula for many reasons. The problem often lies like in improper formulaand in system settings books. Therefore, it is recommended to use several tips to find out what exactly is suitable in this particular situation.
Change the format of cells
The program displays incorrect calculations if the specified formats does not match the valuewhich is in the cell. Then the calculation or will not be applied at all, or issue a completely different number. For example, if the format is text, then the calculation will not be carried out. For the program, it is only text, not a number. Also, there may be a situation when the format does not match the valid. In this case, the user does not work correctly insert the calculation, and Excel does not consider the amount and does not calculate the result of the formula.
To check if it really is in the format, you should go to the tab " the main" Previously, you need to select an unverified cell. This tab is located information about format.
If it needs to be changed, just click on the arrow and select the required from the list. After that, the system will make a new calculation. 
List of formats B. this section Full, but without descriptions and parameters. Therefore, in some cases, the user cannot find the desired one. Then, it is better to use another method. Just as in the first variant, it follows choose a cell. After that, click the right mouse button and open the command "". 
IN open window located full list of formats with description and settings. It is enough to choose the desired and click on " OK». 
Turn off the "Show Formulas" mode
Sometimes the user may notice that instead of the number, the calculation itself is displayed and the formula is not considered in the cell. Then you need to disable this mode. After that, the system will display a ready-made result calculation, not expression.
To disable the function " Show formulas", You should go to the appropriate section" Formulas" Here is the window " Dependencies" It is in it that the desired team is located. To display a list of all dependencies, click on the arrival. From the list you need to choose " Show"And disable this mode if it is active. 
Errors in syntax
Often, incorrect display of the result is a consequence syntax errors. This happens if the user entered the calculation independently and did not resort to the help of the built-in master. Then, all cells with errors will not issue a calculation. 
In this case, follows check correctly writing Each cell that issues an incorrect result. You can rewrite all the values \u200b\u200bby using the built-in master.
Turn on the recalculation of formula
All calculations can be spelled out correctly, but in the event of a change in cell values, it does not recalculate. Then may be disabled automatic Change Change Function. To check this, go to the section " File", Then" Parameters». 
In the open window you need to go to the tab " Formulas" Here are the parameters of the calculations. Simply install the checkbox at the point " Automatically"And save changes that the system began to recalculate. 
The program can carry out a complete calculation, but instead of the finished value, an error and a column or cell may not summarize the numbers. Depending on the displayed message, one can judge what kind of malfunction arose, for example, division by zero or wrong format.
In order to double-check the syntax and correct the error, go to the " Formulas" Dependes are a team that is responsible for calculations. 
A window opens, which displays the formula itself. Here, you should click on " Calculate"To check the error.
Other errors
Also, the user may encounter other errors. Depending on the cause, they can be corrected accordingly.
The formula is not stretched
Stretching is necessary in the case when several cells should perform the same calculations with different values. But it happens that this does not happen automatically. Then, you should verify that the function is installed automatic fillingwhich is located in the parameters.
The amount of cells is incorrect
The amount is also considered incorrect if in the book there are hidden cells. Their user does not see, but the system is calculated. As a result, the program displays one value, and the real amount must be different.
The same problem occurs if displayed values \u200b\u200bwith chin numbers. In this case, they are required to round up so that the calculation is carried out correctly.
Formula is not considered automatically
In the open window, go to the setting of automatic recalculation and install the checkbox on the relevant team. After that you need to save the changes.
Graphs and charts (5)
Work with VB Project (12)
Conditional formatting (5)
Lists and ranges (5)
Macros (VBA Procedures) (64)
Miscellaneous (41)
Excel bugs and glitches (4)
Excel misunderstands. Why?
Often, when calculating the difference between two cells in Excel, it can be seen that it is not zero, although the numbers are the same. For example, in cells A1 and B1, the number of 10.7 is recorded, and in C1 we will deduct from one other:
And the stordst thing is that in the end we do not get 0! Why?
The reason is obvious - cell format
First, the most obvious answer: if there is a comparison of the values \u200b\u200bof the two cells, it is necessary to make sure that the numbers are really equal and are not rounded by the cell format. For example, if you take the same numbers from the example above, then if you select their mouse button - Format cells (Format Cells) - Locking Number (Number) - Singing the format numeric and exhibit the number of decimal discharges to 7: 
Now everything becomes obvious - the numbers differ and were simply rounded by the cell format. And naturally cannot be equal. IN this case It will be optimal to understand why the numbers are precisely such, and then make a decision. And if we are confident that numbers need to be really rounded to tenths - then the function can be applied in the formula:
\u003d Rounded (B1; 1) -OCR (A1; 1) \u003d 0
\u003d Round (B1,1) -round (A1,1) \u003d 0
There is also a more cardinal method:
- Excel 2007: Office button -Excel Settings (Excel Options) -Advanced) -
- Excel 2010: File (File) -Options (options) -Advanced) -Set accuracy as on the screen (set preision as displayed)
- Excel 2013 and above: File (File) -Options (options) -Advanced) -Set the specified accuracy (set precision as displayed)
It will record all the numbers on all sheets of the book exactly as they are displayed by the cell format. This action Better to perform on copies of the book, because It brings all numeric data in all sheets of the book to the same way as they are displayed on the screen. Those. If the number itself contains 5 decimal discharges, and the cell format is set only 1 - after applying this option, the number will be rounded up to 1 decimal decimal. In this case, cancel this operation It is impossible if you just do not close the book without saving.
Cause Programmer
But often in Excel, you can observe a more interesting "phenomenon": the difference between two fractional numbers, the resulting formula is not equal to the same number recorded directly into the cell. For example, write to the cell such formula:
=10,8-10,7=0,1
According to the result, the result should be the answer Truth (True) . But on the fact there will be FALSE (FALSE) . And this example is not the only one - such an Excel behavior is not uncommon when calculating. It can be found in less explicit form - when the calculations are based on the value of other cells, which,, in turn, are also calculated by formulas, etc. But the reason in all cases is one.
Why is the same number not equal?
First you will understand why Excel considers the above expression of false. After all, if the deduction of 10.8 is 10.7 - in any case, it turns out 0.1. So somewhere on the way something went wrong. We write to a separate cell to the left part of the expression: \u003d 10.8-10.7. 0.1 will appear in the cell. And now we highlight this cell - when the mouse button - Format cells (Format Cells) - Locking Number (Number) -Sective format numeric and exhibit the number of decimal discharges equal to 15: 
And now it can be seen that in fact in the cell not exactly 0.1, and 0.1000000000001. Those. In a 15 significant discharge, we have a "tail" in the form of an extra unit.
And now we will deal with where this "tail" appeared, because and logically and mathematically should not be there. I will try to tell very briefly and without unnecessary gain - they can be found on this topic, if you wish, you can find a lot on the Internet.
The thing is that in those distant times (this is about the 1970s), when PC was still something like exotic, there was no unified Standard Work with floating semicolons (fractional, if simple). Why is this standard in general? Then, that computer programs They see numbers in their own way, and fractional so generally with the status "everything is difficult." And at the same time fractional number You can imagine differently and process operations with it too. Therefore, in those days, the same program, when working with numbers, could have issued a different result on different PCs. Take into account all possible underwater rocks Each PC task is not simple, so at one finest time the development of a single standard for working with floating semicolons began. Omitting various details, nuances and the interest of the story itself will only say that in the end it all resulted in iEEE754 standard. And in accordance with its specification in the decimal representation of any number, errors are allowed in the 15th significant discharge. What leads to inevitable errors in calculations. Most often, this can be observed in the subtraction operations, because It is the subtraction of the closest among themselves, it leads to the loss of significant discharges.
More about the specification itself, you can also find out in Microsoft article: The results of the floating point arithmetic operations in Excel may be inaccurate
This is just the fault of such an Excel behavior. Although the sake of justice should be noted that not only Excel, but all programs based on this standard. Of course, a logical question begins: why did they take such a buggy standard? I would say that a compromise was chosen between productivity and functionality. Although it may have been other reasons.
Much more important than others: how to deal with it?
In fact, in no way, because This is a program "error". And in this case there is no other way out, how to use all sorts of patches like rounded and similar functions. At the same time, the rounded it is necessary to apply here as well as demonstrated at the very beginning, but a little differently:
\u003d Rounded (10.8 - 10.7; 1) \u003d 0.1
\u003d Round (10.8-10.7,1) \u003d 0.1
those. In the round, we must put the "buggy" expression itself, and not every argument separately. If you put every argument - then the effect does not give it, because the problem is not in the case, but in how the program sees it. And in this case, 10.8 and 10.7 are already rounded to one discharge and it is clear that rounding separately each number will not give any effect at all. You can, however, get out and otherwise. Multiply each number on a certain amount (say for 1000 to remove the decimal signs 100%) and then deduct and compare:
=((10,8*1000)-(10,7*1000))/1000=0,1
I would like to believe that at least the described feature of the IEEE754 Microsoft standard will be able to defeat or at least make a patch that will produce simple calculations no worse than a 50-ruble calculator :) The article helped? Share a link with friends! Video lessons
("Bottom Bar" :( "TextStyle": "Static", "TextPositionStatic": "Bottom", "TextAutohide": True, "TextPositionMarginStatic": 0, "TextPositionDynamic": "Bottomleft", "TextPositionMarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "TEXTEFFECTDELAY": 500, "TEXTEFFECTSEPARATE": FALSE, "TEXTEFFECT1": "SLIDE", "TEXTEFFECTSLIDIRECTION1": "RIGHT", "TEXTEFFECTSLIDISTANCE1": 120, "TEXTEFFECTEASING1": "EASEOUTCUBIC", "TEXTEFFECTDURATION1": 600 , "TexTeffectDelay1": 1000, "TexTeffect2": "Slide", "TexTeffectSlidedIrection2": "Right", "TexTeffectSlidedistance2": 120, "TexTeffectEasing2": "EaseoutCubic", "TexTeffectDuration2": 600, "TexTeffectDelay2": 1500, " TextCSS ":" Display: Block; Padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; Height: 100% ; background-color: # 333333; Opacity: 0.6; Filter: A LPHA (opacity \u003d 60); "," titlecss ":" display: block; Position: relative; Font: Bold 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", Sans-Serif, Arial; Color: #FFF; "," DescriptionCSS ":" Display: Block; Position: relative; font: 12px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", Sans-Serif, Arial; Color: #FFF; margin-top: 8px; "," Buttoncss ":" Display: Block; Position: relative; margin-top: 8px; "," TexteffectResponsive ": True," TexTeffectResPonsize ": 640," TitleCsSResponsive ":" Font-Size: 12px; "," DescriptionCSSResponsive ":" Display: None! Important; "," ButtonCsSResponsive ": "", "addgoooglefonts": false, "googlefonts": "", "TextLeftRightPerCentForStatic": 40))
