How to set a formula in an excel table. Rules for cutting and pasting. The main advantage of formulas with links

Enough important function in Excel are formulas that perform arithmetic and logical operations on data in a table. With their help, you can easily solve mathematical and engineering problems and carry out various calculations, so an advanced user of the program should know how to set a formula in Excel.

All formulas begin with an equal sign (=), this lets the program know that it is a formula that is being entered into the cell. Simple formulas include expressions that calculate sum, difference, multiplication, etc. Arithmetic operators are used to write such expressions:

— “*” - product (=4*3)
— “/” - division (=4/3)
— “+” - addition (=4-3)
— “-” - subtraction (=4+3)
— “^” - exponentiation (=4^3)
- “%” - finding a percentage (to find 4% of 156, use the following expression “=156*4%”, that is, when adding “%” to a number, it is divided by 100 and “4%” is converted to “0.04” ).

Example. Amount formula in Excel:
For cell A1, the expression “=12+3” was entered. The program automatically performed the calculation and indicated in the cell not the entered formula, but the solution to the example. The implementation of other arithmetic operations is done in the same way.
Several operators can be used simultaneously in the formula: “=56 + 56*4%” - this expression adds four percent to the number 56.

For proper operation with operators, you should know the rules of priority:
1) expressions in brackets are counted;
2) after product and division, addition and subtraction are considered;
3) expressions are executed from left to right if they have the same priority.

The formulas “=5*4+8” and “=5*(4+8)” have different meanings, because in the first case the multiplication “5*4” is initially performed, and in the other the expression in brackets “4+8” is calculated.

Excel formula for average value

A useful formula that allows you to find the average of several entered numbers and is denoted “=AVERAGE()”. The average value of the numbers: 5, 10, 8 and 1 is the result of dividing their sum by the quantity, that is, 24 by 4. Implemented this function in excel like this:

Using links

Work in the program is not limited to constant values ​​(constants), so formulas can contain not only numbers, but also cell numbers - links. This function will allow you to calculate formulas even when the data in the specified cell changes and is indicated by its letter and number.

Cells A1, A2 contain the numbers 12, 15 relative. When you add the formula “=A1+A2” to cell A3, the value of the sum of the numbers in cells A1 and A2 will appear in it.

If you change the number of any of the cells A1 or A2, the formula will calculate the amount based on the new data and cell A3 will change its value.

Cell range

In Excel, you can operate on a specific range of cells, which makes filling out formulas easier. For example, to calculate the sum of cells from A1 to A6, it is not necessary to enter the sequence “=A1+A2+A3...”, but it will be enough to enter the addition operator “SUM()” and holding down left button mouse move from A1 to A6.

The result of the work will be the formula “=SUM(A1:A6)”.

Text in formulas

To use text in formulas, it must be enclosed in double quotes- "text". To combine 2 text values, the ampersand operator “&” is used, which connects them into one cell with the assignment of the “text value” type.

To insert a space between words, you need to write it like this: “=A1&” “&A2”.

This operator can unite text values and numeric, for example, you can quickly fill out a similar table by connecting the numbers 10, 15, 17, 45 and 90 with the text “pcs.”

Nowadays, tabular Microsoft editor Office Excel is quite popular. It can be used to produce various mathematical calculations, you just need to choose the right formula and determine the format of the values. We will look at how to correctly compose formulas in this article. You will need:

— Personal Computer;

— Microsoft program Office Excel (order it from us at a discount!).

Instructions

1. Any formula entered into a MO Excel table must begin with an equal sign. Using this sign, the program recognizes the entered information as a formula, and not as a table value.

• To define mathematical operations, use the following symbols:
• — “/” — division operation;
• — “*” — multiplication operation;
• - "^" - raising to a certain degree
• — “+” — addition operation;
• — “-” — subtraction operation.

If you need to calculate the result of calculating 3 times 2 and squaring the result, then the formula should take next view: =(3*2)^2. After entering the formula, press the Enter key, Excel program will display the result in the selected cell.

3. If you need to include data from the current table in the formula, then insert a link to the cell with these values ​​into the formula. If you need to calculate the values ​​of multiplying a number that is in cell A1 by a number from cell A2, then the formula should look like this: =A1*A2. To use a link, just place the cursor in the desired place in the formula and click on the desired cell.

4. For more complex calculations, the editor provides an option to insert a function. To use it, select the “Function Wizard” tool. If you need to calculate the average in specific cells, enter an equal sign in the cell where you want the result to appear. Then click on the “Insert Function” icon, it is located near the formula input field.

6. Click OK. A window with settings will appear; for this function you will need to specify the range of cells for calculation. If you select another function, the settings for it will be different.

7. Click OK and specific cell The result of the function calculation will appear.

It's just basic concepts about the capabilities of the Excel program, in the future you will be able to study them more deeply.

Video: Calculations in Excel

The formula is mathematical expression, which is created to calculate the result and which 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;
  • ; Union;
  • & text concatenation operator.

Table 22. Examples of formulas

Exercise

Insert formula -25-A1+AZ

Pre-enter any numbers in cells A1 and A3.

1. Select the required cell, for example B1.
2. Start entering the formula with the = sign.
3. Enter the number 25, then the operator (- sign).
4. Enter a reference to the first operand, for example by clicking on the desired cell A1.
5. Enter the following operator (+ sign).
6. Click in the cell that is the second operand in the formula.
7. Complete the formula by pressing the key Enter. In cell B1 you will get the result.

Autosummation

Button AutoSum- ∑ can be used for automatic creation formula that sums up the area of ​​neighboring cells located directly left in this line and directly higher V this column.

1. Select the cell in which to place the summation result.
2. Click the AutoSum - ∑ button or press the key combination Alt+=. Excel will decide which area to include in the sum range and will highlight it with a dotted moving frame called a border.
3. Click Enter to accept the area that Excel has selected, or select with the mouse new area and then press Enter.

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

Exercise

Creating a table and calculating using formulas

1. Enter numeric data in the cells as shown in the table. 23.

Table 23. Original data table

1. Select cell B7 in which the vertical sum will be calculated.
2. Click the AutoSum - ∑ button or click Alt+=.
3. Repeat steps 2 and 3 for cells C7 and D7.

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

1. Select cell B8 and type the (=) sign.
2. Click cell B7, which is the first operand in the formula.
3. Enter the (-) sign on your keyboard and click in the cell V3, which is the second operand in the formula (the formula will be entered).
4. Click Enter(the result will be calculated in cell B8).
5. Repeat steps 5-8 to calculate using the appropriate formulas in cells C8 and 08.
6. Save the file with the name Education_employees.x1s.

Table 24.Calculation result

Duplicate formulas using a fill marker

The cell area (cell) can be multiplied by using fill marker. As shown in the previous section, the fill handle represents 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.

1. Select the cell containing the formula to replicate.
2. Drag fill marker in the right direction. The formula will be replicated in all cells.

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.

1. Enter the formula for auto-summing cells =SUM(VZ:03) into cell E3.
2. Copy the formula by dragging the fill handle into cells E4:E8.
3. Look at how the relative addresses of cells change in the resulting formulas (Table 25) and save the file.

Table 25. Changing cell addresses when replicating formulas

Relative and absolute references

Formulas that implement calculations in tables use so-called references to address cells. The 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 various starting places will result in different places appointments.

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 Boulevard Ring". Regardless of the starting location, this 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 the formula calculates shares of the total amounts, reference to the cell containing 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).

1. Create a table similar to the one below.

Table 26. Salary calculation

1. In cell SZ, enter the formula for calculating Ivanov’s salary =B1*VZ.

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

1. Set an absolute reference to cell B1 by placing the cursor in the formula bar on B1 and pressing the F4 key. The formula in cell C3 will look like =$B$1*B3.
2. Copy the formula into cells C4 and C5.
3. Save the file (Table 27) under the name Salary.xls.

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 be used Excel functions, such as SUM IF and so on.

On the menu Insert, Name there are two various commands creating named areas: Create and Assign.

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

Creating a name

1. Select cell B1 (Table 26).
2. Select from menu Insert, Name (Insert, Name) command Assign (Define).
3. Enter your name Hourly rate and click OK.
4. Select cell B1 and make sure that the name field says Hourly rate.

Creating Multiple Names

1. Select cells VZ:C5 (Table 27).
2. Select from menu Insert, Name (Insert, Name) command Create (Create), a dialog box will appear Create names(Fig. 88).
3. Make sure the radio button in the left column is checked and click OK.
4. Select cells VZ:NZ and make sure that the name field says Ivanov.

Rice. 88. Dialog box Create names

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

1. In the formula bar, place the cursor where you want to add the name.
2. Select from menu Insert, Name (Insert, Name) command Paste (Paste), The Insert Names dialog box appears.

1. Select desired name from the list and click OK.

Errors in formulas

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 # symbol. 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 of 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.

Functions in Excel

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

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 in the formula current date, using the function TODAY.

Rice. 90. Function Wizard

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

Statistical functions allow you to perform statistical analysis 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 select records based on conditions.

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

Logic functions are intended 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 if it is false.

Functions Checking Properties and Values are intended to determine the data stored in a cell. These functions check the values ​​in a cell according to a condition and return values ​​depending on the result TRUE or FALSE.

To perform table calculations using built-in functions, we recommend using the Function Wizard. The Function Wizard dialog is available when you select the command Function in the Insert menu or pressing a button, on standard panel tools. 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.

1. Select cell F3 and click on the Function Wizard button.
2. In the first window of the Function Wizard dialog, from the Statistical category, select the function AVERAGE, click on the button Further.
3. The second dialog box of the Function Wizard must provide arguments. The input cursor is in the input field of the first argument. In this field as an argument number! enter the range address B3:D3 (Fig. 91).
4. Click OK.
5. Copy the resulting formula into cells F4:F6 and save the file (Table 28).

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.

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 the cell A3 contains a formula that adds cell values A2 And A1.

One more example. Cell A3 contains a function SUM(SUM), which calculates the sum of the range A1:A2.

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

Entering a formula

To enter the formula, follow the instructions below:

Advice: Instead of manually typing A1 And A2, just click on the cells A1 And A2.

Editing formulas

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

1. 1. To edit a formula, click on the formula bar and change the formula.

Operation priority

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:

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

Another example:

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

Copy/paste formula

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

Inserting a function

All functions have the same structure. For example:

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

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

To insert a function, do the following:

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

Excel is essential when you need to organize, process, and save a lot of information. It will help automate calculations, making them easier and more reliable. Formulas in Excel allow you to carry out arbitrarily complex calculations and get results instantly.

How to write a formula in Excel

Before learning this, there are a few basic principles to understand.

1. Each one begins with an “=” sign.
2. Cell values ​​and functions can participate in calculations.
3. Operators are used as familiar mathematical symbols for operations.
4. When you insert a record, the cell displays the result of the calculation by default.
5. You can view the design in the line above the table.

Each cell in Excel is an indivisible unit with its own identifier (address), which is indicated by a letter (column number) and a number (row number). The address is displayed in the field above the table.

So, how to create and paste a formula in Excel? Act on to the following algorithm:

Designation Meaning

Addition
- Subtraction
/ Division
* Multiplication

If you need to specify a number rather than a cell address, enter it from the keyboard. To specify a negative sign in an Excel formula, click "-".

How to enter and copy formulas in Excel

They are always entered after clicking on “=”. But what to do if there are many similar calculations? In this case, you can specify one and then simply copy it. To do this, enter the formula and then “stretch” it in the desired direction to multiply it.
Position the pointer over the cell to be copied and move the mouse pointer to the lower right corner (over the square). It should take the form of a simple cross with equal sides.


Press the left button and drag.


Release when you need to stop copying. At this moment, the calculation results will appear.


You can also stretch it to the right.
Move the pointer to an adjacent cell. You will see the same entry, but with different addresses.


When copying in this way, the row numbers increase if the shift occurs downward, or the column numbers increase if it is shifted to the right. This is called relative addressing.
Let's enter the VAT value into the table and calculate the price including tax.


The price including VAT is calculated as price*(1+VAT). Let's enter the sequence into the first cell.


Let's try to copy the entry.


The result was strange.


Let's check the contents in the second cell.


As you can see, when copying, not only the price, but also the VAT shifted. And we need this cell to remain fixed. Let's anchor it with an absolute link. To do this, move the pointer to the first cell and click on address B2 in the formula bar.


Press F4. The address will be appended with a “$” sign. This is the sign of an absolute cell.


Now after copying the B2 address will remain unchanged.
If you accidentally entered data into the wrong cell, simply move it. To do this, move the mouse pointer over any border, wait until the mouse looks like a cross with arrows, press the left button and drag. At the desired location, simply release the manipulator.

Using functions for calculations

Excel offers a large number of functions that are divided into categories. You can view the full list by clicking on the Fx button next to the formula line or opening the “Formulas” section on the toolbar.


Let's talk about some functions.

How to Set "If" Formulas in Excel

This function allows you to set a condition and perform a calculation depending on its truth or falsity. For example, if the quantity of goods sold is more than 4 packs, you should purchase more.
To insert the result depending on the condition, we will add another column to the table.


In the first cell under the heading of this column, set the pointer and click the “Boolean” item on the toolbar. Let's select the "If" function.


As with inserting any function, a window will open for you to fill in the arguments.


Let's indicate the condition. To do this, click on the first row and select the first cell “Sold”. Next, put the “>” sign and indicate the number 4.


In the second line we will write “Purchase”. This message will appear for those items that have been sold out. Last line can be left empty since we have no action if the condition is false.


Click OK and copy the entry for the entire column.


To prevent “FALSE” from being displayed in the cell, let’s open the function again and correct it. Place the pointer on the first cell and press Fx next to the formula bar. Insert the cursor on the third line and put a space between the quotes.


Then OK and copy again.


Now we see which product should be purchased.

Formula text in Excel

This function allows you to apply a format to the contents of a cell. In this case, any type of data is converted to text, which means it cannot be used for further calculations. Let's add a column to format the total.


In the first cell, enter a function (the “Text” button in the “Formulas” section).


In the arguments window we indicate the cell reference total amount and set the format to “#rub.”


Click OK and copy.


If we try to use this amount in calculations, we will receive an error message.

"VALUE" indicates that the calculation cannot be performed.
You can see examples of formats in the screenshot.

Date formula in Excel

Excel provides many options for working with dates. One of them, DATE, allows you to construct a date from three numbers. This is convenient if you have three different columns- day month Year.

Place the pointer on the first cell of the fourth column and select a function from the Date and Time list.

Arrange the cell addresses accordingly and click OK.


Copy the entry.

AutoSum in Excel

In case you need to fold big number data, Excel provides the SUM function. For example, let's calculate the amount for goods sold.
Place the pointer in cell F12. It will calculate the total.


Go to the Formulas panel and click AutoSum.


Excel will automatically highlight the closest number range.


You can select a different range. In this Excel example I did everything right. Click OK. Pay attention to the contents of the cell. The SUM function was substituted automatically.


When inserting a range, the first cell address, a colon, and the last cell address are specified. ":" means "Take all cells between the first and last. If you need to list multiple cells, separate their addresses with a semicolon:
SUM (F5;F8;F11)

Working in Excel with formulas: example

We told you how to make a formula in Excel. This is knowledge that can be useful even in everyday life. You can manage your personal budget and control expenses.


The screenshot shows the formulas that are entered to calculate the amounts of income and expenses, as well as the calculation of the balance at the end of the month. Add sheets to the workbook for each month if you don't want all the sheets on one. To do this, simply click on the “+” at the bottom of the window.

To rename a sheet, double-click it and enter a name.

The table can be made even more detailed.
Excel is very useful program, and calculations in it provide almost unlimited possibilities.

Have a great day!