Microsoft Excel is designed to work with data, tables, and numeric values. For ease of calculation, there are formulas in Excel that allow you to get accurate calculations.
Editing Formulas
Any formula that is in a cell can be instantly edited. To do this, select a specific cell and place the cursor in the formula bar, where you can make all the necessary changes. Some users find it more convenient to edit directly in the cell with the formula: to do this, click on it a couple of times.
When all the necessary changes are entered, you need to press the Enter or Tab buttons. After this step, Excel will recount and display the result.
In the case when the formula is entered incorrectly or due to the deletion of the contents of any cell, an error occurs in the calculations, the program will necessarily warn the user about this. An exclamation mark in a rhombus will appear next to the box containing the inaccuracy. The application will also indicate exactly which inaccuracy was made.
Error treatment in Excel
- If the calculation result does not fit in the cell or the date and time number is negative, the ##### symbol will appear.
- If a formula argument is used that is not valid, #VALUE!
- When a user tries to divide by zero, the program will notify him with the symbol #DEL / 0!
- In the event that the name is incorrect and Excel cannot read it, it gives an error #NAME?
- Uncertain data is denoted by # N / A.
- If the formula includes a link to an invalid cell, the symbol #REF! will notify about it.
- MS Excel has a certain range of numbers, and in the case when the calculation result falls outside this range, the program will display the # EMPTY symbol!
The user must remember that errors can occur not only because the formula contains incorrect data, but also when the cell contains incorrect information.
If the table contains a huge number of cell references, then errors may occur during calculations, which will be very difficult to identify. So that the user does not experience inconvenience, Excel has a special tool with which you can see dependent and influencing cells.
Influential cells are those that are referenced by formulas, and dependent cells are those that contain formulas that refer to cell coordinates.
Using the ribbon commands, you can graphically display the relationships between formulas and cells. This is done through the dependency arrows. To remove them, click "Remove arrows".
Cell Addresses: 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 be changed. In this case, you must apply an absolute link, which will remain unchanged.
Using absolute links, when copying Excel, you can give the command:
- Change the link to the column, while maintaining the row.
- Keep column references, but change row links.
- Do not change links to both rows and columns.
How to convert a relative link to an absolute?
If necessary, relative links can be converted to mixed or absolute. For these purposes, the $ symbol is used, which is placed in front of the part of the link that will be absolute.
The cell must be selected, then enter the equal sign, and then click on the cell. After pressing F4, set the dollar sign in front of the column letter and Excel row number. Repeated clicks will allow you to switch from one type of link to another. If desired, $ can be entered manually.
Excel Formulas
The formulas in Excel are necessary for calculations on given values. The complexity of 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 tools for working with functions are in the tab “Formulas”. For convenience, all functions are divided into groups depending on the complexity of the calculations and the type of tasks being solved.
Before you make a formula in Excel, you need to put an equal sign. After that, you must specify the name of the function. It is represented in the form of an abbreviation of capital letters that define the value of a function. Then in 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 link to a cell, a series of links, or a range of cells. Some formulas in Excel have text or numbers as arguments, others have time and date.
Feature Wizard
In Excel, the sum formula has a simple definition, but not all functions are so simple. Some require complex syntax spelling and a large number of arguments of certain types. To make such a formula without errors is often very difficult. The developers of the program took this nuance into account, therefore Ecxel has a compilation assistant - the “Function Wizard”.
To use it to enter formulas in Excel, you need to select the "Insert Function" icon. It is located on the left side of the "Formula Bar". Then the user needs to select the appropriate category of the formula.
Just below the list of categories, a brief description of each function that will be highlighted is displayed. There you can find information and arguments. Having made a choice, it is worth confirming it by clicking OK. After that, the “Function Arguments” window will appear.
At the bottom of the window there is help, which indicates the purpose of the formula, argument and final value.
The function that is the argument is called nested. For example, the
average value and the sum. In Excel, the formula both sums the values of the cells in the selected range and displays the
average value.You can enter links using the mouse, clicking on the selected cells, and manually. All values will be substituted in the current field. This dialog box can be minimized or reduced if necessary. The result of the calculation appears after clicking OK.
For the convenience of users, the Excel formula has been developed, the percentages of which will not be difficult to calculate.