How to create formulas in Excel. Creating complex formulas in Microsoft Excel. Formulas with cells

A formula is a mathematical expression that is created to calculate a result and that can depend on the contents of other cells. A formula in a cell can contain data, links to other cells, and also an indication of the actions that need to be performed.

Using cell references allows formula results to be recalculated when the contents of the cells included in the formulas change.

In Excel, formulas begin with an = sign. Parentheses () can be used to define the order of mathematical operations.

Excel supports the following operators:

• Arithmetic operations:
  • addition (+);
  • multiplication (*);
  • finding percent (%);
  • subtraction(-);
  • division(/);
  • exponent (^).
• Comparison operators:
  • = equal;
  • < меньше;
  • > more;
  • = greater than or equal to;
  • not equal.
• Telecom operators:
  • : range;
  • ; association;
  • & operator for joining texts.

Table 22. Examples of formulas

Exercise

Insert formula -25-A1+AZ

Pre-enter any numbers in cells A1 and A3.

The AutoSum button - ∑ can be used to automatically create a formula that sums the area of ​​immediately adjacent cells. left in this line and directly higher in this column.

The AutoSum function automatically transforms when cells are added or deleted within an area.

Exercise

Creating a table and calculating using formulas

Table 23. Original data table

Calculate the number of employees without higher education (using formula B7-VZ).

Table 24.Calculation result

An area of ​​cells (cell) can be expanded by using a fill handle. As shown in the previous section, the fill handle is a control point in the lower right corner of the selected cell.

It is often necessary to reproduce not only data, but also formulas containing address links. The process of replicating formulas using a fill handle allows you to copy the formula while simultaneously changing the address references in the formula.

This process is typically used when copying formulas within rows or columns containing the same type of data. When replicating formulas using the fill marker, the so-called relative addresses of the cells in the formula change (relative and absolute links will be described in detail below).

Exercise

Replication of formulas

1.Open the file Employee_Education.x1s.

Table 25. Changing cell addresses when replicating formulas

Formulas that implement calculations in tables use so-called references to address cells. A cell reference can be relative or absolute.

Using relative references is similar to indicating the direction of travel on a street - "go three blocks north, then two blocks west." Following these instructions from different starting places will lead to different destinations.

For example, a formula that sums the numbers in a column or row is then often copied for other row or column numbers. Such formulas use relative references (see the previous example in Table 25).

An absolute reference to a cell.or area of ​​cells will always refer to the same row and column address. When compared with street directions, it will be something like this: “Go to the intersection of Arbat and the Boulevard Ring.” Regardless of where you start, it will lead to the same place. If the formula requires that the cell address remain unchanged when copied, then an absolute reference must be used (record format $A$1). For example, when a formula calculates fractions of a total amount, the reference to the cell containing the total amount should not change when copied.

A dollar sign ($) will appear before both a column reference and a row reference (for example, $C$2). Pressing F4 successively will add or remove a sign before the column or row number in the reference (C$2 or $C2 - the so-called mixed links).

Table 26. Salary calculation

When replicating the formula of this example with relative references, an error message (#VALUE!) appears in cell C4, since the relative address of cell B1 will change, and the formula =B2*B4 will be copied to cell C4;

Table 27. Salary calculation results

Names in formulas

Names in formulas are easier to remember than cell addresses, so you can use named scopes (one or more cells) instead of absolute references. The following rules must be followed when creating names:

• names can contain no more than 255 characters;
• names must begin with a letter and can contain any character except a space;
• names should not be similar to references, such as VZ, C4;
• names should not use Excel functions such as SUM, IF, etc.

In the Insert, Name menu, there are two different commands for creating named areas: New and Assign.

The Create command allows you to specify (enter) the required name ( only one), The Assign command uses labels placed on the worksheet as area names (allows you to create several names at once).

Rice. 88. Dialog box Create names

You can insert a name into the formula instead of an absolute link.

If an error is made when entering formulas or data, an error message appears in the resulting cell. The first character of all error values ​​is the # character. The error values ​​depend on the type of error made.

Excel can not recognize all errors, but those that are detected must be able to be corrected.

Error # # # # appears when the entered number does not fit in the cell. In this case, you should increase the column width.

Error #DIV/0! appears when a formula attempts to divide by zero. This most often happens when the divisor is a cell reference that contains a null or empty value.

Error #N/A! is an abbreviation for the term "undefined data". This error indicates that a formula is using a blank cell reference.

Error #NAME? appears when a name used in a formula has been removed or was not previously defined. To correct, determine or correct the data area name, function name, etc.

Error #EMPTY! appears when there is an intersection between two regions that do not actually have common cells. Most often, the error indicates that an error was made when entering references to cell ranges.

Error #NUMBER! appears when a function with a numeric argument uses an incorrect argument format or value.

Error #VALUE! appears when a formula uses an invalid argument or operand type. For example, text was entered instead of a numeric or logical value for an operator or function.

In addition to the listed errors, a circular link may appear when entering formulas.

A circular reference occurs when a formula directly or indirectly includes references to its own cell. A circular reference can cause distortions in worksheet calculations and is therefore considered an error in most applications. When you enter a circular reference, a warning message appears (Figure 89).

To correct the error, delete the cell that caused the circular reference, edit or re-enter the formula.

More complex calculations in Excel tables are carried out using special functions (Fig. 90). A list of function categories is available by selecting the Function command on the Insert, Function menu.

Financial functions carry out such calculations as calculating the amount of payment on a loan, the amount of payment of profit on investments, etc.

The Date and Time functions let you work with date and time values ​​in formulas. For example, you can use the current date in a formula by using the TODAY function.

Rice. 90. Function Wizard

Math functions perform simple and complex mathematical calculations, such as calculating the sum of a range of cells, the absolute value of a number, rounding numbers, etc.

Statistics functions allow you to perform statistical analysis of data. For example, you can determine the mean and variance of a sample and much more.

Database functions can be used to perform calculations and to filter records based on conditions.

Text functions provide the user with the ability to process text. For example, you can concatenate multiple strings using the CONCATENATE function.

Logical functions are designed to test one or more conditions. For example, the IF function allows you to determine whether a specified condition is true and returns one value if the condition is true and another value if it is false.

The Property and Value Validation functions are used to determine the data stored in a cell. These functions test the values ​​in a cell against a condition and return TRUE or FALSE depending on the result.

To perform calculations in a table using built-in functions, we recommend using the Function Wizard. The Function Wizard dialog box is available by selecting the Function command from the Insert menu or by clicking the button , on the standard toolbar. During the dialogue with the wizard, you need to specify the arguments of the selected function; to do this, you need to fill in the fields in the dialog box with the corresponding values ​​or addresses of table cells.

Exercise

Calculate the average value for each line in the Education.xls file.

Rice. 91. Entering an Argument in the Function Wizard

Table 28. Table of calculation results using the function wizard

To enter a range of cells into the Function Wizard window, you can use your mouse to circle this range on the table worksheet (in the example, B3:D3). If the Function Wizard window covers the desired cells, you can move the dialog box. After selecting a range of cells (B3:D3), a running dotted frame will appear around it, and the address of the selected range of cells will automatically appear in the argument field.

Excel is a multifunctional tool. With which you can apply a variety of formulas and make the use of the program more productive.

Using formulas in excel is a useful feature. Thanks to it, you can quickly calculate the sum, difference, product and quotient of certain values. By learning to use formulas directly in the table, you can then easily calculate complex values. Even if you don’t have a calculator at hand.

The calculator will not be able to calculate complex formulas unless you write down intermediate results. In Excel, you can write the entire formula; moreover, you can automatically use one formula for different numerical values.

To enter any formula in Excel, you must first insert the “=” sign. If you specify this sign before entering any value, the program will understand that you are entering a formula. If you specify a formula without this symbol, the program will not be able to perform the calculation.

• Select any cell, for example A2;
• In the line with the Fx sign (located at the top of the table), enter “=1+3” and press “Enter”;
• In the selected cell you will see the result (4). The entered value will remain in the formula bar.

You can also use other arithmetic symbols in Excel: “-” for subtraction, “*” for multiplication, “/” for division. Try entering different formulas in the Fx line.

Basic entry rules:

In excel, you can apply formulas by specifying cells. Let’s say in cell B2 enter the number 7, in cell B3 the number 20. In cell B4 enter the formula “=B2+B3”. In cell B4 you will see the result of the addition in cells B2 and B3 - 27. You can change the values ​​in them, then the result in cell B4 will automatically change.

To use formulas, you can use the manipulator instead of doing it manually.

There are 3 types of them:

• Relative indicates the position of the cell relative to the cell with the formula. It is designated as B2, for example;
• Absolute indicates a cell whose position does not change. It is designated as $B$2;
• A mixed link contains a combination of the first two links. It is designated as $В2, В$2.

For a better understanding of the various links in excel, let's type out some formulas.

Specify in the table fields:

• B2 - 10 C2 – 100
• B3 - 70 C3 – 700

In cell B4, enter “=B2+B3”, then “Enter”.

Next, place the cursor in the lower right corner of B4, hold down the right mouse button, drag over cell C4, and release the manipulator key. In the context menu that opens, select “Copy Cells”. The value from field B4 is copied to cell C4. Activate cell C4, you will see the formula in it - “C2 + C3”. That is, it seems like you copied the formula from field B4, but the value turned out different. This is what makes this type of link different. For example, if you take cell B4 and copy it into cell A54, for example, the formula will be obtained by adding the cells above it, that is, “A52 + A53”.

Indicate in the cells:

• B2 - 10 C2 – 100
• B3 - 70 C3 – 700
• Enter the value 10 in field D2.

In field B4, enter the formula “=B2+B3+$D$2.” Copy the formula from B4 to C4. What happened in the end? Regular links have changed, but absolute links have not changed.

Let's say that to enter a link to field B7 (Sheet 2) in field B2 (Sheet 1), follow these steps:

• Select B2 and insert the “=” sign;
• Click on the “Sheet 2” shortcut;
• Highlight B7 and press enter;
• Sheet 1 will open again, and the formula “=Sheet2!B7” will appear in field B2.

You can edit formula values ​​in the same way as text. That is, activate the cell by double-clicking the mouse and perform editing.

Use text values ​​for formulas if they contain the following symbols: “0 – 9”, “+”, “-”, “e”, “E”, “/”. You can also use the following formatting characters: "$", "%", "(", ")", "space", "";;".

For example, if cell B1 contains the value “Ivan”, and cell B2 contains “Ivanov”, and cell B4 contains the formula “=B2&B3”, then the result will be “IvanIvanov”.

Sign "&;" is a text operator, an ampersand, that concatenates text.

To insert a space, use “&"; "&", that is, the formula will be “=B2&" "&B3".

The ampersand is also used to combine numbers with text. For example, if the first cell contains the number 5, and the second cell contains buckets, then the result will be “5 buckets”.

To enter complex functions, open the Insert menu and click the Function icon there. Select the required function. In this section you will see how diverse Excel is and what complex calculations can be made in it. You can use powers, roots, fractions, radicals, indices, create matrices and much more.

If you use formulas all the time, you will quickly remember how to use them in excel. Thanks to them, you will save time and be able to perform complex calculations in just a minute.

Using EXCEL is not as difficult as it might seem at first glance. This program was developed by a large corporation, Microsoft, and when they created it, they knew that with the help of this program it would be possible to simplify the life of workers in different fields using the function of adding formulas. This program has incredibly huge functionality that can be used for a variety of purposes, both personal and for work or study.

The formulas themselves are not difficult and are very easy to understand. But even though they are not difficult to learn, they are useful, because thanks to these formulas you can reduce the time for calculations. Thanks to formulas, you can obtain certain results of expressions in one cell, the variables of which will be other cells. Thanks to this program, you can make the most complex mathematical and financial calculations with virtually no bothering yourself.

In addition, cells that act as variables for expressions can accept not only number input from the keyboard, but also the results of other formulas. In addition to ordinary mathematical calculations, formulas can help you carry out logical calculations.
Thanks to this program you can find out such indicators as:

Maximum, minimum and average.
- Percentage of numbers
- Various criteria, including Student's test
- And also many more useful indicators

The program has many advantages, but the main one, of course, is that it can convert numbers and create an alternative option, any scenario, and at the same time all calculations are almost instantaneous.

Applying simple formulas in Excel.

Let's look at the formulas using a simple example of the sum of two numbers in order to understand the principle of their operation. An example would be the sum of two numbers. The variables will be cells A1 and B1, into which the user will enter numbers. Cell C3 will display the sum of these two numbers if the following formula is specified in it:
=SUM(A1;B1).

You can calculate the amount yourself, but in more complex examples, where you need to add tens and hundreds of thousands, it will be more difficult to do this in your head, and using the sum formula, the value will be calculated automatically, practically, like in a calculator.

You can change the data in cells with variables, but you don't need to change the cell with the formula unless you want to replace it with a different formula. In addition to the sum, you can perform other mathematical operations, such as difference, division and multiplication. The formula always begins with a "=" sign. If it is not there, the program will not count your formula.

How to create a formula in the program?

In the previous example, we looked at an example of the sum of two numbers, which anyone can handle without the help of Excel, but when you need to calculate the sum of more than 2 values, it will take more time, so you can perform the sum of three cells at once, for this you just need to write the following formula to cell D1:
=SUM(A1:B1,C1).

But there are times when you need to add, for example, 10 values; for this you can use the following version of the formula:
=SUM(A1:A10), which will look like this.

And in exactly the same way with the product, only use PRODUCT instead of SUM.

You can also use formulas for several ranges; to do this, you need to write the following version of the formula, in our case the product:
=PRODUCT(A1-A10, B1-B10, C1-C10)

Combinations of formulas.

In addition to the fact that you can specify a large range of numbers, you can also combine different formulas. For example, we need to add numbers of a certain range, and we need to calculate their product multiplied by different coefficients for different options. Let's say we need to find out the coefficient of 1.4 from the sum of the range (A1:C1) if their sum is less than 90, but if their sum is greater than or equal to 90, then we need to find out the coefficient of 1.5 from the same sum. For this seemingly complex task, only one simple formula is given, which combines two basic formulas, and it looks like this:

IF(SUM(A1:C1) Enter to force the format change.

The SUM function is not updated.

Check if the calculation is set to Automatic. On the Formulas tab, open Calculation Options. You can also force a calculation on a worksheet by pressing F9.

Some values ​​are not added.

Error #NAME? is displayed instead of the expected result.

This usually means that there is an error in the formula: for example, instead of =SUM(A1:A10), you entered =SUM(A1:A10).

The SUM function displays an integer number when it should display a decimal number.

Check to see if displaying decimal numbers in cell format is selected. Select the appropriate cell or range and press Ctrl+1 to open the Format Cells dialog box, then click the Number tab and select the format you want, specifying the number of decimal places you want.

A formula is an expression that calculates the value of a cell. Functions are predefined formulas and are already built into Excel.

For example, in the figure below, cell A3 contains a formula that adds the values ​​of cells A2 and A1.

Another example. Cell A3 contains a SUM function that calculates the sum of the range A1:A2.

SUM(A1:A2)
=SUM(A1:A2)

To enter the formula, follow the instructions below:

Tip: Instead of manually typing A1 and A2, simply click on cells A1 and A2.

When you select a cell, Excel displays the value or formula in the cell in the Formula Bar.

Excel uses a built-in order in which calculations are carried out. If part of the formula is in parentheses, it will be calculated first. Then multiplication or division is performed. Excel will then add and subtract. See example below:

Excel first multiplies (A1*A2), then adds the value of cell A3 to this result.

Another example:

Excel first calculates the value in parentheses (A2+A3), then multiplies the result by the value of cell A1.

When you copy a formula, Excel automatically adjusts the references for each new cell the formula is copied into. To understand this, follow these steps:

All functions have the same structure. For example:

SUM(A1:A4)
SUM(A1:A4)

The name of this function is SUM. The expression between the brackets (arguments) means that we have given the range A1:A4 as input. This function adds the values ​​in cells A1, A2, A3, and A4. Remembering which functions and arguments to use for each specific task is not easy. Luckily, Excel has an Insert Function command.

To insert a function, do the following:

Note: Instead of using the Insert Function tool, simply type =COUNTIF(A1:C2,”>5″). When you type “=COUNTIF(“, instead of typing “A1:C2”, manually select that range with your mouse.