Formulas in the "Excel". The Excel table - formulas
Microsoft Excel is designed to work with data, tables, and numeric values. For the convenience of calculations, there are formulas in "Excel" that allow you to get accurate calculations.
Any formula that is in the cell can beinstantly edit. To do this, select a specific cell and set the cursor in the formula bar, where you can make all the necessary changes. Some users are more comfortable editing directly in the cell with the formula: for this you need to click on it a couple of times.
When all necessary changes are entered, you need to press the Enter or Tab buttons. After this action, Excel will recalculate and display the result.
In the event that the formula is entered incorrectlyor due to the removal of the contents of any cell, an error occurs in the calculations, the program will necessarily warn the user about it. Next to the cell, which contains an inaccuracy, an exclamation point will appear in the diamond. The application will also indicate which inaccuracy was admitted.
Error interpretation in Excel
- If the result of calculations is not placed in the cell or the date and time are negative, the symbol ##### appears.
- If you use a formula argument that is invalid, #VALUE is displayed!
- When the user attempts to divide by zero, the program will notify him with the symbol # DEL / 0!
- In the event that the name is incorrect and Excel can not read it, does it give an error #NAME?
- The uncertain data is denoted by # N / D.
- If the formula contains a reference to an invalid cell, the symbol # REFERENCE! will notify about it.
- MS Excel has a certain range of numbers, and in the event that the result of calculations falls out of this range, the program will emit the symbol # EMPTY!
The user should remember that errors can appear not only because the formula contains incorrect data, but also when the cell contains incorrect information.
If the table contains a huge number of linkson the cells, then in the calculations there can be errors, which will be very difficult to identify. To ensure that the user does not experience any inconvenience, Excel has a special tool with which you can see the dependent and influencing cells.
Influences are those cells referred to by formulas, and dependent cells are those that contain formulas referring to the coordinates of cells.
Using commands on the tape, you can graphically display the relationships between formulas and cells. This is done through the dependency arrows. To remove them, click "Remove arrows".
Addresses of cells: absolute and relative
The concept of relative addressing allowscopy formulas from one cell to another, automatically changing addresses. This function is convenient, but sometimes it becomes necessary to use the value of a particular cell and the link should not change. In this case, you need to apply an absolute reference, which will remain unchanged.
Using absolute references, when copying Excel you can give the command:
- Change the reference to the column while keeping the rows.
- Keep references to the column, but change the references to the rows.
- Do not change the references to both rows and columns.
How do I convert a relative reference to an absolute reference?
If necessary, relative references can be turned into mixed or absolute ones. For these purposes, the $ character is used, which is placed before that part of the link that will be absolute.
You need to select the cell, then enter the signequality, then click on the cell. After pressing the F4 key, set the dollar sign in front of the column letter and the Excel line number. Repeated presses will move from one type of link to another. If desired, $ can be entered manually.
Formulas in Excel
Formulas in "Excel" are necessary for calculations ongiven values. The complexity of the operations can be completely different, but in any case, instead of entering all the cell addresses, a specific function is used. The range is specified as an argument.
All the tools for working with functions are in the "Formulas" tab. For convenience, all functions are divided into groups, depending on the complexity of the calculations and the type of tasks to be performed.
Before making a formula in Excel, you needput an equal sign. After this, you must specify the function name. It is represented in the form of an abbreviation of capital letters that determine the value of the function. Further, in the brackets, the function arguments are stored, which are used to obtain the result. The argument can be either a specific number, or a reference to a cell, a series of links, or a range of cells. Some formulas in "Excel" have text or numbers as an argument, others - a time and a date.
In the "Excel" formula of the sum has a simpledefinition, but not all functions are so simple. Some require complex syntactic spelling and a large number of arguments of certain types. It is often very difficult to formulate such a formula without errors. The developers of the program took into account this nuance, so Ecxel has a compilation assistant - "Master of Functions".
To use it to enter formulas in "Excel"you must select the "Insert function" icon. It is located on the left side of the "Formula Line". Then the user needs to find the appropriate category of the formula.
Below the list of categories, a shortA description of each function that will be highlighted. There you can find information on the arguments. Having made a choice, it is necessary to confirm it by clicking OK. After that, the "Function Arguments" window will appear.
At the bottom of the window there is a help, which indicates the purpose of the formula, argument and final value.
The function that serves as an argument hasthe name of the nested. For example, the average value and the amount. In Excel, the formula summarizes the cell values of the selected range, and displays the average value.
You can enter links with the mouse by clickingbutton on the selected cells, and manually. All values will be substituted in the current field. This dialog box can be minimized or minimized if necessary. The result of the calculation appears after pressing OK.
For the convenience of users, the formula "Excel" is developed, the interest on which is not difficult to calculate.