In this tutorial, we will walk you through how to create a complex formula in Excel and also look at typical mistakesarising from novice users due to inattention. If you are new to Excel, then we advise you to first refer to the lesson where we discussed creating simple formulas.

How to create a complex formula in Excel

In the example below, we will demonstrate how Excel calculates complex formulas based on the order of operations. In this example, we want to calculate the amount of sales tax for food services. To do this, write the following expression in cell D4: \u003d (D2 + D3) * 0.075... This formula will add up the value of all items in the invoice and then multiply by the amount of sales tax 7,5% (written down as 0.075).

Excel follows the order and adds the values \u200b\u200bin parentheses first: (44.85+39.90)=$84.75 ... Then he multiplies that number by the tax rate: $84.75*0.075 ... The calculation result shows that the sales tax will be $6.36 .

It is extremely important to enter complex formulas in the correct order. Otherwise, Excel calculations may be inaccurate. In our case, in the absence of parentheses, the multiplication is performed first, and the result will be incorrect. The brackets are the best way determining the order of calculations in Excel.

Create complex formulas using order of actions

In the example below, we will use links along with quantitative data to create a complex formula that calculates the total cost of a food bill. The formula will calculate the cost of each menu item and then add all the values \u200b\u200btogether.

You can add parentheses to any formula to make it easier to read. Although this will not change the result of the calculation in this example, we can still enclose the multiplication in parentheses. This will make it clear that it is executed before the addition.

Excel does not always warn about formula errors, so you need to check all your formulas yourself. To learn how you can do this, see the Lesson Checking Formulas.

If you haven't worked with Excel before, you will soon find out that this is not just a spreadsheet for entering numbers. Of course, in Excel, you can just count the amounts in rows and columns, but you can also calculate mortgage payments, solve mathematical and engineering problems, and find the most favorable options depending on the given variables.

In Excel, all this is done using formulas in cells. These formulas are used to perform calculations and other actions with the data on the worksheet. A formula always begins with an equal sign (\u003d), after which you can enter numbers, mathematical operators (such as the + and - signs for addition and subtraction), and built-in Excel functions that greatly expand the capabilities of formulas.

Below is an example of a formula that multiplies 2 by 3 and adds 5 to the result to get 11.

The following are examples of formulas that can be used on worksheets.

Parts of an Excel Formula

A formula can also contain one or more elements such as functions, links, operators and constants.

The order of actions in formulas

In some cases, the order of evaluation can affect the value returned by a formula, so it is important to understand the standard order of evaluation and know how to change it to get the results you want.

Using functions and nested functions in Excel formulas

Functions are predefined formulas that perform calculations on given values \u200b\u200b— arguments — in a particular order or pattern. Functions can be used to perform both simple and complex calculations. All excel functions can be seen on the Formulas tab.

Using links in Excel formulas

A link points to a cell or range of cells in a worksheet and tells Microsoft Excel, where the values \u200b\u200bor data required by the formula are located. Using links, you can use data in different parts of the worksheet in one formula, as well as use the value of one cell in multiple formulas. In addition, you can set links to cells in different sheets of one workbook or to cells from other books. References to cells in other books are called links or xrefs.

Using names in Excel formulas

You can create specific names to identify cells, cell ranges, formulas, constants, and Excel tables. A name is a meaningful short designation that explains the purpose of a cell reference, constant, formula, or table, as it can be difficult to understand at a glance. The following are examples of names and how they can be used to make formulas easier to understand.

Example 1

Example 2

Copy the sample data from the table below and paste it into cell A1 of the new excel worksheet... To display the results of formulas, select them and press F2, and then press Enter. In addition, you can adjust the width of the columns according to the data they contain.

Note: In the formulas in columns C and D, the specific name Sales is replaced with a reference to the range A9: A13, and the name Sales Information is replaced with the range A9: B13. If the book does not have these names, the formulas in D2: D3 will return the #NAME? Error.

Example type	An example that does not use names	An example using names	Formula and result using names
	"\u003d SUM (A9: A13)	"\u003d SUM (Sales)	SUM (Sales)
	"\u003d TEXT (VLOOKUP (MAX (A9: 13), A9: B13,2, FALSE)," dd / mm / yyyy ")	"\u003d TEXT (VLOOKUP (MAX (Sales), Sales Info, 2, FALSE)," dd.mm.yyyy ")	TEXT (VLOOKUP (MAX (Sales), Sales Info, 2, FALSE), "dd.mm.yyyy")
	Date of sale

For more information, see the article Define and use names in formulas.

Using array formulas and array constants in Excel

An array formula can perform multiple calculations and then return a single value or group of values. An array formula processes multiple sets of values \u200b\u200bcalled array arguments. Each array argument must contain the same number of rows and columns. An array formula is created in the same way as other formulas, with the difference that you use CTRL + SHIFT + ENTER to enter the formula. Some built-in functions are array formulas and must be entered as arrays for correct results.

Array constants can be used instead of references if you do not need to enter each constant in a separate cell on the sheet.

Using an array formula to calculate one or more values

Note: When you enter an array formula, Excel automatically encloses it in curly braces (and). If you try to manually enter curly braces, Excel will display the formula as text.

Using array constants

In a regular formula, you can enter a reference to a cell with a value or to the value itself, also called a constant. Likewise, you can enter a reference to an array or an array of values \u200b\u200bcontained in cells (sometimes called an array constant) into an array formula. Array formulas accept constants in the same way as other formulas, however, the array constants must be entered in a specific format.

Array constants can contain numbers, text, Boolean values \u200b\u200bsuch as TRUE or FALSE, or error values \u200b\u200bsuch as # N / A. A single array constant can contain values \u200b\u200bof various types, for example (1,3,4; TRUE, FALSE, TRUE). The numbers in array constants can be integer, decimal, or exponential. The text must be enclosed in double quotation marks, for example "Tuesday".

Make sure the following requirements are met when formatting array constants.

Constants are enclosed in curly braces ( { } ).

Columns are separated by commas ( , ). For example, to represent the values \u200b\u200b10, 20, 30, and 40, enter (10,20,30,40). This array constant is a 1-by-4 matrix and corresponds to a one row and four column reference.

Cell values \u200b\u200bfrom different lines are separated by semicolons ( ; ). For example, to represent the values \u200b\u200b10, 20, 30, 40, and 50, 60, 70, 80 in cells below each other, you could create a 2-by-4 array constant: (10,20,30,40; 50, 60,70,80).

Deleting a formula

Together with the formula, the results of its calculation are also deleted. However, you can delete the formula itself and leave the result of its calculation as the value in the cell.

Using a colon ( : ), the references to the first and last cells in the range are separated. For instance: A1: A5.

Required arguments specified

A function can have required and optional arguments (the latter are indicated in the syntax with square brackets). All required arguments must be entered. Also, try not to enter too many arguments.

The formula contains no more than 64 levels of nesting of functions

Nesting levels cannot exceed 64.

Book and sheet names are enclosed in single quotes

If the names of the sheets or workbooks you reference cells or values \u200b\u200bcontain non-alphabetic characters, you must enclose them in single quotes ( " ).

The path to external books is specified

Numbers entered without formatting

Do not use dollar signs when entering numbers in a formula, as they are used to indicate absolute references. For example, instead of the value $1000 need to enter 1000 .

Important: The calculated results of formulas and some Excel worksheet functions may differ slightly on x86 or x86-64 Windows computers and ARM-based Windows RT computers.

In this post, as in most blog posts, Excel tables are what I call the ranges of cells to which the command is applied Box - Tables - Table ... You can read more about the purpose and benefits of such a procedure in. If you have not read this post, it will be more difficult for you to absorb the information presented here.

Now let's take a closer look at how formulas work in tables.

Totals line

To calculate totals, tables have a totals row. To display it, select any cell inside the table and check the box on the ribbon Constructor - Table Style Options - Totals Row ... The "Total" line will appear below the table. Select any cell in this row to calculate the column totals. In the drop-down list, select the formula that the program will use to calculate the totals. For example, Sum, Minimum, Average, etc. Click on "More Functions ..." to select another function.

Total row in Excel

You can also write a formula for calculating the totals yourself, it is written in the same way as ordinary formulas, only instead of cell addresses, their names from the table headers are displayed. Specify references to cells with the mouse, the program will substitute their names by itself.

Remember that the totals row processes the table cells that are visible on the screen. If you have applied a filter by some table values, totals will be recalculated only for selected data.

Writing formulas to columns

If you need to perform calculations in the columns of a table, this is even easier than in the usual range of cells. Create a new column in the table. Place the cursor in any cell in this column and write down the formula. Use the method of pointing the mouse to cell references. The program will display the column names instead of in the formulas, this will facilitate the perception of the formula. Press Enter and be surprised! Excel will not only calculate the result, but also copy the formula to all cells in the column. You don't even have to copy formulas manually!

Excel formulas

This feature is not always useful, so you can disable it. To do this, when inserting a formula, click on the smart tag and select “ Don't automatically create calculated columns "

Customizing Excel Table Formulas

Referring to a table by name

You can refer to the contents of the table by name. To specify the name of the table, enter it in the field on the ribbon: Constructor - Properties - Table Name .

Now, in order to perform some operations with the data of the table, you can refer to it by name. For example, our table is called People:

• Formula \u003d Sums (People) will try to sum all table cells
• Formula \u003d sum (People [Sales_Sum]) will determine the sum of data in the "Amount_sales" column of the "People" table
• \u003d People [@Sales_Sum] / 100 - will divide by 100 sales from the same line

If the line numbers of a cell with a formula and a cell in a table do not match, Excel will display the traditional reference with coordinates.

The use of formulas in tables is highly automated and intuitive. While many will find them difficult to use at first, after a few days you will get used to it and appreciate the benefits that Excel spreadsheets provide.

Thank you for reading this post. And in the next article I will tell you. See you on the blog pages.

As you know, the Excel spreadsheet office editor was originally designed to perform mathematical, algebraic and other calculations, for which the input of special formulas is used. However, in the light of how to write a formula in Excel, it must be borne in mind that most of the input operations are radically different from everything that is customary to use in everyday life. That is, the formulas themselves have a slightly different form and use completely different operators that are used when writing standard calculations.

Let's consider the question of how to write a formula in Excel, using a few simple examples, without touching on complex operations, for which you need to study the program deeply enough to understand. But even prior knowledge will give any user an understanding of the basic principles of using formulas for different cases.

How to write a formula in Excel: basic concepts

So, the input of formula values \u200b\u200bin the program is somewhat different from the standard operations, the symbols used and the operators used. When solving the problem of how to write a formula in Excel, it is necessary to build on the basic concepts that are used in almost all computer systems.

The fact is that the machine does not understand the input of a combination like "2 x 2" or putting a common component outside the brackets ("2 + 2) 5"). For this, several types of symbols are provided, which are presented in the table below, not counting logical operators.

In this case, the priority of performing operations starts from the degree and ends with addition and subtraction. In addition, although Excel can be used like a regular calculator, as a rule, you must specify cell numbers or cell ranges for calculations. It goes without saying that the data format in any such cell must be set to the appropriate (at least numeric).

Sum and difference

How to write a sum or difference formula in Excel? So, let's start with the simplest, when you need to calculate the amount. In the line of formulas (moreover, for all operations), an equal sign is first entered, after which the desired formula is entered. In the case of a conventional calculator, you can specify for the installed cell "\u003d 2 + 2".

If the summation is performed for values \u200b\u200bentered directly in other cells (for example, A1 and A2), the formula becomes "\u003d A1 + A2". It is not uncommon to use parentheses to use additional operators. For the difference - the same thing, only with a minus instead of a plus.

When you need to specify the numbers of cells or their range, a special sum command can be used (in the Russian version "SUM", in English - SUM). When specifying several cells, it looks like this: "\u003d SUM (A1; A2)", for a range - "SUM (A1: A10)", provided that you need to calculate the sum of all numbers in cells from the first to the tenth. In principle, if you set the active cell, which is located immediately after the last one in the column with the initial values, you do not need to enter the formula, but simply click on the automatic summing button.

Multiplication, division and exponentiation

Now let's see how to write a multiplication or division formula in Excel. The order is the same as when entering a sum or difference, only the operators differ.

For the product the form is used "\u003d A1 * A2", for the quotient - "A1 / A2" By the way, these are exactly the same commands that can be found when using a standard Windows calculator.

The symbol "^" is used for exponentiation. For the value in cell A1, which, for example, needs to be squared, the formula "\u003d A1 ^ 2" is applied.

Percentage calculations

With interest, if you do not touch on complex calculations, everything is also simple. How to write a formula with percentages in Excel?

It is enough to enter a formula of the form "\u003d A1 * 5%", after which you will get those very five percent of the value in the corresponding cell.

Using cell selection-based formula input

But all this related to manual assignment or the so-called direct input of formulas (direct or direct input). In fact, it is sometimes useful to use the mouse and the Ctrl key.

While holding down the mouse button, you can simply select the desired cells by first entering the required calculation in the formula bar. The cells will be added directly to the formula bar. But, depending on the type of formula, sometimes the parentheses will have to be entered manually.

Absolute, relative, and mixed cell types

It should be noted separately that the program can use several types of cells, not to mention the data they contain.

An absolute cell is unchanged and is denoted as $ A $ 1, relative is a reference to the usual location (A1), mixed - there is a combination of references to both absolute and relative cells ($ A1 or A $ 1). Typically, such formats are used when creating formulas when data are involved in different sheets of the book or even in different files.

VLOOKUP formulas

Finally, let's see how to write a VLOOKUP formula in Excel. This technique allows you to insert data from one range into another. In this case, the method is somewhat similar to that used when solving the problem of how to write the "Condition" formula in Excel, which uses the symbols shown in the table above.

In general, such calculations are like a simple filter applied to columns, where you want to filter only exact values, not approximate values.

In this option, first through the "Function Wizard", the range of values \u200b\u200bof the original (first) table is used, the second range with fixing the contents (F4) is indicated in the "Table" field, then the column number is indicated, and the value "FALSE" is set in the interval view field if indeed, when filtering, you really only need to get accurate, not approximate values. As a rule, such formulas are used more in warehouse or accounting, when it is not possible to install any specialized software products.

Conclusion

It remains to say that not all formulas that can be used in the Excel spreadsheet editor have been described here. This, so to speak, is just the basics. In fact, if you dig further into trigonometry or calculating logarithms, matrices or even tensor equations, everything looks much more complicated. But in order to study all this, it is necessary to thoroughly study the manual for the editor itself. And this is no longer a question of the fact that even the simplest logic games can be created in Excel based on changing data. As an example, we can cite the same "snake", which initially had nothing to do with the spreadsheet editor, but was reproduced by enthusiasts in their field in Excel.

For the rest, it should be clearly understood that, having studied primitive formulas or actions with data, then it will be possible to easily master more complex calculations, say, with the creation of cross-references, the use of various kinds of scripts or VB scripts, etc. All this takes time, so if you want to study the program and all its possibilities to the maximum, you will have to sweat over the theoretical part.