• “less than or equal” sign – “<=»;

• “not equal” sign – “<>».

Text concatenation operator

The special character “&” (ampersand) is used for this purpose. Using it, you can connect different fragments into one whole - the same principle as with the “CONNECT” function. Here are some examples:

1. If you want to merge text in cells, then you need to use the following code.

1. In order to insert any symbol or letter between them, you need to use the following construction.

1. You can merge not only cells, but also ordinary symbols.

Any text other than links must be quoted. Otherwise the formula will generate an error.

Please note that the quotes used are exactly the same as in the screenshot.

The following operators can be used to define links:

• in order to create a simple link to the desired range of cells, just indicate the first and last cell of this area, and between them the symbol “:”;
• to combine links the sign “;” is used;
• if it is necessary to determine cells that are at the intersection of several ranges, then a “space” is placed between the links. In this case, the value of cell “C7” will be displayed.

Because only it falls under the definition of “intersection of sets.” This is the name given to this operator (space).

Using links

While working in the Excel editor, you can use various types of links. However, most novice users know how to use only the simplest of them. We will teach you how to correctly enter links of all formats.

Simple links A1

As a rule, this type is used most often, since they are much more convenient to compose than others.

• columns – from A to XFD (no more than 16384);
• lines – from 1 to 1048576.

Here are some examples:

• the cell at the intersection of row 5 and column B is “B5”;
• the range of cells in column B starting from line 5 to line 25 is “B5:B25”;
• the range of cells in row 5 starting from column B to F is “B5:F5”;
• all cells in row 10 are “10:10”;
• all cells in rows 10 to 15 are “10:15”;
• all cells in column B are “B:B”;
• all cells in columns B to K are “B:K”;
• The range of cells B2 to F5 is “B2-F5”.

Sometimes formulas use information from other sheets. It works as follows.

The second sheet contains the following information.

If there is a space in the name of the sheet, then it must be indicated in the formula in single quotes (apostrophes).

Absolute and relative links

Excel editor works with three types of links:

• absolute;
• relative;
• mixed.

Let's take a closer look at them.

All the previously mentioned examples refer to relative cell addresses. This type is the most popular. The main practical advantage is that the editor will change the references to a different value during migration. In accordance with where exactly you copied this formula. For the calculation, the number of cells between the old and new positions will be taken into account.

Imagine that you need to stretch this formula across an entire column or row. You will not manually change letters and numbers in cell addresses. It works as follows.

1. Let's enter a formula to calculate the sum of the first column.

1. Press the hotkeys Ctrl + C. In order to transfer the formula to an adjacent cell, you need to go there and press Ctrl + V.

If the table is very large, it is better to click on the lower right corner and, without releasing your finger, drag the pointer to the end. If there is little data, then copying using hot keys is much faster.

1. Now look at the new formulas. The column index changed automatically.

If you want all links to be preserved when transferring formulas (that is, so that they do not change automatically), you need to use absolute addresses. They are indicated as "$B$2".

As a result, we see that no changes have occurred. All columns display the same number.

This type of address is used when it is necessary to fix only a column or row, and not all at the same time. The following constructions can be used:

• $D1, $F5, $G3 – for fixing columns;
• D$1, F$5, G$3 – for fixing rows.

Work with such formulas only when necessary. For example, if you need to work with one constant row of data, but only change the columns. And most importantly, if you are going to calculate the result in different cells that are not located along the same line.

The fact is that when you copy the formula to another line, the numbers in the links will automatically change to the number of cells from the original value. If you use mixed addresses, then everything will remain in place. This is done as follows.

1. Let's use the following expression as an example.

1. Let's move this formula to another cell. Preferably not on the next or on another line. Now you see that the new expression contains the same line (4), but a different letter, since it was the only one that was relative.

3D links

The concept of “three-dimensional” includes those addresses in which a range of sheets is indicated. An example formula looks like this.

In this case, the result will correspond to the sum of all cells “A5” on all sheets, starting from 1 to 4. When composing such expressions, you must adhere to the following conditions:

• such references cannot be used in arrays;
• three-dimensional expressions are prohibited from being used where there is an intersection of cells (for example, the “space” operator);
• When creating formulas with 3D addresses, you can use the following functions: AVERAGE, STDEV, STDEV.V, AVERAGE, STDEV, STDEV.Y, SUM, COUNTA, COUNT, MIN, MAX, MINA, MAX, VARVE, PRODUCT, VARIANCE, VAR. and DISPA.

If you break these rules, you will see some kind of error.

R1C1 format links

This type of link differs from “A1” in that the number is assigned not only to rows, but also to columns. The developers decided to replace the regular view with this option for convenience in macros, but they can be used anywhere. Here are some examples of such addresses:

• R10C10 – absolute reference to the cell, which is located on the tenth line of the tenth column;
• R – absolute link to the current (in which the formula is indicated) link;
• R[-2] – a relative link to a line that is located two positions above this one;
• R[-3]C is a relative reference to a cell that is located three positions higher in the current column (where you decided to write the formula);
• RC is a relative reference to a cell that is located five cells to the right and five lines below the current one.

Use of names

Excel allows you to create your own unique names for naming ranges of cells, single cells, tables (regular and pivot), constants, and expressions. At the same time, for the editor there is no difference when working with formulas - he understands everything.

You can use names for multiplication, division, addition, subtraction, calculation of interest, coefficients, deviation, rounding, VAT, mortgage, loan, estimate, timesheets, various forms, discounts, salaries, length of service, annuity payment, working with VPR formulas , “VSD”, “INTERMEDIATE.RESULTS” and so on. That is, you can do whatever you want.

There is only one main condition - you must define this name in advance. Otherwise Excel will not know anything about it. This is done as follows.

1. Select a column.
2. Call the context menu.
3. Select "Assign a name".

1. Specify the desired name for this object. In this case, you must adhere to the following rules.

1. To save, click on the “OK” button.

In the same way, you can assign a name to a cell, text or number.

You can use the information in the table both using names and using regular links. This is what the standard version looks like.

And if you try to insert our name instead of the address “D4:D9”, you will see a hint. Just write a few characters and you will see what fits (from the name database) the most.

In our case, everything is simple - “column_3”. Imagine that you will have a large number of such names. You won't be able to remember everything by heart.

Using Functions

There are several ways to insert a function in Excel:

• manually;
• using the toolbar;
• using the Insert Function window.

Let's take a closer look at each method.

In this case, everything is simple - you use your hands, your own knowledge and skills to enter formulas in a special line or directly in a cell.

If you do not have working experience in this area, then it is better to use easier methods at first.

In this case it is necessary:

1. Go to the "Formulas" tab.
2. Click on any library.
3. Select the desired function.

1. Immediately after this, the Arguments and Functions window will appear with the function already selected. All you have to do is enter the arguments and save the formula using the “OK” button.

Substitution Wizard

You can apply it as follows:

1. Make any cell active.
2. Click on the “Fx” icon or use the keyboard shortcut SHIFT + F3.

1. Immediately after this, the “Insert Function” window will open.
2. Here you will see a large list of different features sorted by category. In addition, you can use the search if you cannot find the item you need.

All you have to do is type in some word that can describe what you want to do, and the editor will try to display all the suitable options.

1. Select a function from the list provided.
2. To continue, you need to click on the “OK” button.

1. You will then be asked to specify "Arguments and Functions". You can do this manually or simply select the desired range of cells.
2. In order to apply all the settings, you need to click on the “OK” button.

1. As a result of this, we will see the number 6, although this was already clear, since the preliminary result is displayed in the “Arguments and Functions” window. The data is recalculated instantly when any of the arguments changes.

Using Nested Functions

As an example, we will use formulas with logical conditions. To do this, we will need to add some kind of table.

Then follow the following instructions:

1. Click on the first cell. Call up the “Insert Function” window. Select the "If" function. To insert, click on “OK”.

1. Then you will need to create some kind of logical expression. It must be written in the first field. For example, you can add the values ​​of three cells in one row and check whether the sum is greater than 10. If “true”, indicate the text “Greater than 10”. For a false result – “Less than 10”. Then click “OK” to return to the workspace.

1. As a result, we see the following - the editor showed that the sum of the cells in the third line is less than 10. And this is correct. This means our code works.

1. Now you need to configure the following cells. In this case, our formula simply extends further. To do this, you first need to hover the cursor over the lower right corner of the cell. After the cursor changes, you need to left click and copy it to the very bottom.

1. As a result, the editor recalculates our expression for each line.

As you can see, the copying was quite successful because we used the relative links we talked about earlier. If you need to assign addresses to function arguments, then use absolute values.

You can do this in several ways: use the formula bar or a special wizard. In the first case, everything is simple - click in a special field and manually enter the necessary changes. But writing there is not entirely convenient.

The only thing you can do is make the input field larger. To do this, just click on the indicated icon or press the key combination Ctrl + Shift + U.

It's worth noting that this is the only way if you don't use functions in your formula.

If you use functions, everything becomes much simpler. To edit you must follow the following instructions:

1. Make the cell with the formula active. Click on the "Fx" icon.

1. After this, a window will appear in which you can change the function arguments you need in a very convenient way. In addition, here you can find out exactly what the result of recalculating the new expression will be.

1. To save the changes you have made, use the “OK” button.

To remove an expression, just do the following:

1. Click on any cell.

1. Click on the Delete or Backspace button. As a result, the cell will be empty.

You can achieve exactly the same result using the “Clear All” tool.

Possible errors when creating formulas in the Excel editor

Listed below are the most popular mistakes made by users:

• The expression uses a huge number of nestings. There should be no more than 64 of them;
• formulas indicate paths to external books without the full path;
• Opening and closing brackets are placed incorrectly. This is why in the editor, in the formula bar, all brackets are highlighted in a different color;

• the names of books and sheets are not placed in quotation marks;
• numbers are used in the wrong format. For example, if you need to enter $2000, you need to simply enter 2000 and select the appropriate cell format, since the $ symbol is used by the program for absolute references;

• Required function arguments are not specified. Note that optional arguments are enclosed in square brackets. Everything without them is necessary for the formula to work properly;

• The cell ranges are specified incorrectly. To do this, you must use the “:” (colon) operator.

Error codes when working with formulas

When working with a formula, you may see the following error options:

• #VALUE! – this error indicates that you are using the wrong data type. For example, you are trying to use text instead of a numeric value. Of course, Excel will not be able to calculate the sum between two phrases;
• #NAME? – such an error means that you made a typo in the spelling of the function name. Or are you trying to enter something that doesn’t exist. You can't do that. Besides this, the problem could be something else. If you are sure of the function name, then try looking at the formula more closely. Perhaps you forgot a parenthesis. In addition, you need to take into account that text fragments are indicated in quotation marks. If all else fails, try composing the expression again;
• #NUMBER! – displaying a message like this means that you have some problem with the arguments or the result of the formula. For example, the number turned out to be too huge or, on the contrary, small;
• #DIV/0! – this error means that you are trying to write an expression in which division by zero occurs. Excel can't override the rules of math. Therefore, such actions are also prohibited here;
• #N/A! – the editor can show this message if some value is not available. For example, if you use the SEARCH, SEARCH, MATCH functions, and Excel does not find the fragment you are looking for. Or there is no data at all and the formula has nothing to work with;
• If you are trying to calculate something and Excel writes the word #REF!, then the function argument is using the wrong range of cells;
• #EMPTY! – this error appears if you have an inconsistent formula with overlapping ranges. More precisely, if in reality there are no such cells (which happen to be at the intersection of two ranges). Quite often this error occurs by accident. It is enough to leave one space in the argument, and the editor will perceive it as a special operator (we talked about it earlier).

When you edit the formula (the cells are highlighted), you will see that they do not actually intersect.

Sometimes you can see a lot of # characters that completely fill the width of the cell. In fact, there is no error here. This means that you are working with numbers that do not fit in a given cell.

To see the value contained there, just resize the column.

In addition, you can use cell formatting. To do this you need to follow a few simple steps:

1. Call the context menu. Select Format Cells.

1. Specify the type as "General". To continue, use the “OK” button.

Thanks to this, the Excel editor will be able to convert this number into another format that fits in this column.

Examples of using formulas

The Microsoft Excel editor allows you to process information in any way convenient for you. There are all the necessary conditions and opportunities for this. Let's look at a few examples of formulas by category. This will make it easier for you to understand.

In order to evaluate the mathematical capabilities of Excel, you need to perform the following steps.

1. Create a table with some conditional data.

1. To calculate the amount, enter the following formula. If you want to add just one value, you can use the addition operator (“+”).

1. Oddly enough, in the Excel editor you cannot take away using functions. For subtraction, the usual “-” operator is used. In this case, the code will be as follows.

1. In order to determine how much the first number is from the second as a percentage, you need to use this simple construction. If you want to subtract several values, you will have to enter a “minus” for each cell.

Note that the percent symbol is placed at the end, not at the beginning. In addition, when working with percentages, you do not need to additionally multiply by 100. This happens automatically.

1. Excel can add based on several conditions at once. You can calculate the sum of cells in the first column whose value is greater than 2 and less than 6. And the same formula can be set for the second column.

=SUMIFS(B3:B9,B3:B9,">2",B3:B9,"<6") =SUMIFS(C3:C9,C3:C9,">2",C3:C9,"<6")

1. You can also count the number of elements that satisfy some condition. For example, let Excel count how many numbers we have greater than 3.

=COUNTIF(B3:B9,">3") =COUNTIF(C3:C9,">3")

1. The result of all formulas will be as follows.

Mathematical functions and graphs

Using Excel, you can calculate various functions and build graphs based on them, and then conduct graphical analysis. As a rule, such techniques are used in presentations.

As an example, let's try to build graphs for an exponent and some equation. The instructions will be as follows:

1. Let's create a table. In the first column we will have the initial number “X”, in the second - the “EXP” function, in the third - the specified ratio. It would be possible to make a quadratic expression, but then the resulting value would practically disappear against the background of the exponential on the graph.

As we said earlier, the growth of the exponent occurs much faster than that of the ordinary cubic equation.

Any function or mathematical expression can be represented graphically in this way.

Everything described above is suitable for modern programs of 2007, 2010, 2013 and 2016. The old Excel editor is significantly inferior in terms of capabilities, number of functions and tools. If you open the official help from Microsoft, you will see that they additionally indicate in which version of the program this function appeared.

In all other respects, everything looks almost exactly the same. As an example, let's calculate the sum of several cells. To do this you need:

1. Provide some data for calculation. Click on any cell. Click on the "Fx" icon.

1. Select the “Mathematical” category. Find the “SUM” function and click on “OK”.
1. You can try to recalculate in any other editor. The process will happen exactly the same.

Conclusion

In this tutorial, we talked about everything related to formulas in the Excel editor, from the simplest to the very complex. Each section was accompanied by detailed examples and explanations. This is done to ensure that the information is accessible even to complete dummies.

If something doesn’t work out for you, it means you’re making a mistake somewhere. You may have misspelled expressions or incorrect cell references. The main thing is to understand that everything needs to be driven in very carefully and carefully. Moreover, all functions are not in English, but in Russian.

In addition, it is important to remember that formulas must begin with the “=” (equals) symbol. Many novice users forget about this.

Examples file

To make it easier for you to understand the previously described formulas, we have prepared a special demo file in which all the above examples were compiled. You can do it from our website completely free of charge. If during training you use a ready-made table with formulas based on the completed data, you will achieve results much faster.

Video instruction

If our description did not help you, try watching the video attached below, which explains the main points in more detail. You may be doing everything right, but you're missing something. With the help of this video you should understand all the problems. We hope that lessons like this have helped you. Check us out more often.

Excel is used for more than just creating tables. With this program you can perform various calculations. This tool is very convenient when creating any database in Excel. The built-in calculator will reduce time and make work more convenient. To perform calculations, you need to know basic Excel concepts.

Formulas and cell addresses

A formula is an equality that can be used to add, subtract, multiply and divide values ​​directly in a table. To make calculations more convenient, you should be well aware of the concept of “cell address”. An address is the designation of an individual table cell. The figure shows a small table consisting of six rows and four columns. Each cell has its own address. It is designated by a Latin letter and number. For example, the cell “Milk” has the address “A4”. That is, “A” is the column number, and “4” is the row number.

Application of formulas in calculations

Now you can start studying the formulas. The formula includes the equal sign, “+”, “-”, “/” and “*”. To create any equation, you need to select a cell and write a function in it. For example, if you need to calculate the total cost of four loaves of bread, then place the cursor on cell D3 and write the “equals” sign. Next, you should write a formula from cell addresses or simply numbers with a multiplication sign. Let's consider the simplest method - finding the total cost using known numbers. To do this, enter “=4*15” in the cell and press “Enter”.

Now let's look at a universal method for finding different values. When you use cell addresses when writing a formula, you can change the values ​​in other cells. In this case, Excel will automatically recalculate the value in the column where the formula is written. To do this, instead of numerical values, enter address data into cell D3. The quantity of bread has the address “B3”, and the price of one loaf is “C3”. Now we enter these addresses in the column where the calculation will be performed.

As you write the formula, the cells are highlighted. This will allow you to correctly specify the addresses of the values. Another important advantage of creating a formula using row and column names is that it calculates all the values ​​at once. That is, if you enter the formula in cell “D3” and press “Enter,” Excel will automatically calculate the total cost of all other products.

The formula bar is a special line located above the column headings and intended for entering and editing formulas and other information. A fragment of the formula bar is shown in Figure 3.4.

Figure 3.4 - Formula bar

The formula bar consists of two main parts: the address bar, which is located on the left, and the line for entering and displaying information. In Figure 3.4, the name of the last used function (in this case, the sum calculation function) is displayed in the address bar, and the formula “=A1+5” is displayed in the information entry and display line.

The address line is designed to display the address of the selected cell or range of cells, as well as to enter the required addresses from the keyboard. However, when you select a group of cells, the address bar will only show the address of the first cell in the range, located in its upper left corner.

In the Excel 2007 spreadsheet editor, you can fully automate calculations using the “Formula” data type. Formula is a special tool of Excel 2007 designed for calculations, calculations and data analysis.

The formula begins with the "=" sign, followed by operands and operators. The list of arithmetic operators is given in Table 3.1. The precedence of operations when calculating Excel formulas is as follows:

− telecom operators (performed first);

− operator percentage;

− unary minus;

− exponentiation operator;

− multiplication and division operators;

− addition and subtraction operators (last). Table 3.1 - Symbols for indicating operators in Excel

Brackets in Excel formulas perform the usual, from an algebraic point of view, role of indicating the priority of calculating a particular part of the expression. For example:

= 10*4+4^2 gives the result 56

= 10*(4+4^2) gives the result 200

Particular care must be taken when placing parentheses when specifying a unary minus. For example: = -10^2 gives the result 100, and =-(10^2) gives the result -100; - 1^2+1^2 gives the result 2, and 1^2-1^2 gives the result 0.

If the formula cannot be calculated correctly, Excel displays an error code in the cell instead of the expected result (Table 3.2).

Table 3.2 – Error messages when calculating formulas

Functions in Excel

You can also perform many types of calculations using special built-in functions in Excel 2007. A function is a procedure originally created and embedded in the Excel program that performs calculations based on given arguments in a certain order.

Each function must include the following elements: name or title (examples of names - SUM, AVERAGE, COUNT, MAX, etc.), as well as an argument (or several arguments), which is specified in parentheses immediately after the function name . Function arguments can be numbers, links, formulas, text, logical values, etc. If the argument

The function has several components, they are specified separated by commas. If a function has no arguments, for example, the PI() function, then nothing is specified inside the brackets. Parentheses allow you to define where the argument list begins and ends. You cannot insert anything between the function name and the parentheses. Therefore, the symbol for raising a function to a power is given after writing the argument. For example, SIN(A1)^3. If the rules for writing a function are violated, Excel displays a message stating that there is an error in the formula.

You can enter functions either manually or automatically. In the latter case, use the function wizard, which opens with the button Insert function, which is located on the Excel 2007 ribbon on the Formulas tab.

All functions available in the program are grouped into categories for ease of use. The category is selected from the Category drop-down list, and a list of functions included in this category is displayed at the bottom of the window. If you select the required function and press the OK button, a window will open (its contents depend on the specific function) in which the function arguments are indicated.

Trigonometric functions are often used in engineering calculations. Please note that the argument of the trigonometric function must be specified in radians. Therefore, if the argument is given in degrees, it must be converted to radians. This can be implemented either through the conversion formula “=A1*PI()/180” (assuming that the argument is written in the cell with address A1), or using the RADIANS(A1) function.

Example. Write the Excel formula = − for2 + calculation3 1+e function tg 3 (5 2 ) b .

Assuming that the x value is in degrees and is written in cell A1, and the b value is in cell B1, the formula in the Excel cell will look like this:

=(- (B1^2) + (1+exp(B1))^(1/3)) /TAN(5*RADIANS(A1)^2) ^3

Relative and absolute cell addresses

To write constants in Excel formulas, you should use absolute cell addressing. In this case, when copying the formula to another cell, the address of the cell with the constant will not change. To change the relative address of cell B2 to the absolute $B$2 in the formula, you must successively press the F4 key, or manually add dollar symbols. There are also mixed cell addresses (B$2 and $B2). When copying a formula containing

mixed addresses, only the part of the address that is not fixed (with the $ sign on the left) is changed.

When you copy a formula into an adjacent cell along a row, the letter component changes in the relative link address. For example, link A3 will be replaced by link B3, but the mixed address $A1 will not change when copied along the line. Accordingly, when copying a formula into an adjacent cell in a column in the relative link address, the digital component changes. For example, link A1 will be replaced by link A2, but the mixed address A$1 will not change when copied along the column.

Building charts

In Excel, the term chart refers to any graphical representation of numerical data. Charts are built based on a data series - a group of cells with data within one row or column. You can display multiple data series on one chart.

The easiest way to create charts is as follows: select one or more data series, and in the Charts group of the Insert tab of the Excel ribbon, select the desired chart type. The diagram will be placed on the current sheet of the workbook. If necessary, it can be transferred to another sheet using the command Move chart tabs Designer for working with diagrams. Using the tab Layout Working with diagrams

you can change the appearance of the chart: add the name of the chart, axes, change fonts, etc. If necessary, you can change the labels on the horizontal axis. To do this, in the context menu of the diagram, select

mandu Select data and in the dialog box Selecting a data source (Fig.

Tip 3.5) change the labels of the horizontal axis.

Figure 3.5 – Window for changing data on the X axis