Formulas in the "Excel". Table "Excel" - 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.

Editing formulas

Any formula that is in the cell can be instantly edited. 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 incorrectly or 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 not valid, #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?
• Indeterminate data are 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 references to 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, there is a special tool in Excel that allows you to see the dependent and influencing cells.

Influential are the cells to which the formulas refer, and the dependent cells are those that contain formulas referring to the coordinates of the 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 allows you to copy 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 links to the column, but change the references to the rows.
• Do not change the references to both rows and columns.

How can 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.

The cell should be selected, then enter the equal sign, then click on the cell. After pressing F4, before the letter of the column and the number of the Excel line, set the dollar sign. Repeated presses will move from one type of link to another. If desired, $ can be entered manually.

Formulas in Excel

The formulas in "Excel" are necessary for calculations with given values. The complexity of the operations can be completely different, but in any case, instead of entering all the addresses of the cells, 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 need to put 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 are fixed the arguments of the function, 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.

Function Wizard

In Excel, the sum formula has a simple definition, but not all functions are so simple. Some require complex syntactic writing and a large number of arguments of certain types. It is often very difficult to make 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 brief description of each function that will be highlighted is displayed. There you can find information on the arguments. After making a choice, you should 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, the argument and the final value.

The function that is the argument is called 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 clicking the button 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 will appear after pressing OK.

For the convenience of users, the formula "Excel" is developed, the interest on which is not difficult to calculate.