How to write a formula in Excel: step-by-step instructions, features and recommendations

As you know, the Excel spreadsheet office editororiginally intended for the product of 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 is necessary to take into account that most of the operations at input are radically different from all that is used in everyday life. That is, the formulas themselves have a slightly different form and use quite different operators that are used when writing standard calculations.

Consider the question of how to write the formula inExcel, on a few simple examples, without affecting complex operations, for understanding which it is necessary to study the program deeply enough. But even preliminary knowledge will give any user an understanding of the basic principles of using formulas for different cases.

How to write a formula in Excel: initial concepts

So, entering the formula values ​​in the programis slightly different from the standard operations, the symbols used and the operators used. When solving the problem of how to write a formula in Excel, you need to build on the basic concepts that are used in almost all computer systems.

how to write a formula in excel

The fact is that the machine entering a combination like "2 x2 "or the removal of the general component by brackets (" 2 + 2) 5 ") does not understand. For this, several types of symbols are provided, which are presented in the table below, not including logical operators.

Amount, addition

+

Difference, subtraction

-

Multiplication, product

*

Division, private

/

Exponentiation

^

Interest and operations with them

%

More, less or equal, not equal

>, <,> =, <=, <>

In this case, the priority of operations beginsfrom the degree and ends with addition and subtraction. In addition, in spite of the fact that Excel can be used as a normal calculator, it is usually necessary to specify the cell numbers or their ranges for calculations. It goes without saying that the data format in any such cell must be set appropriately (at least numeric).

Sum and difference

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

If the summation is made for values,entered directly into other cells (for example, A1 and A2), the formula takes the form "= A1 + A2". Often, for the use of additional operators, the amount may be in brackets. For the difference - the same, only with a minus instead of a plus.

how to write a formula in excel with interest

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

Multiplication, division and exponentiation

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

how to register a condition formula in excel

For the work, use the form "= A1 * A2", for private - "A1 / A2". This, by the way, is exactly the same commands that you can find when using a standard Windows-calculator.

how to write the sum formula in excel

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

Interest calculation

With interest, if you do not touch complex calculations, it's also easy. How in Excel to write a formula with interest?

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

Using formula input based on selection of cells

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

how to write the multiplication formula in excel

With the mouse clamped, you can simply selectthe necessary cells, having preliminary entered necessary calculation in a line of formulas. Cells will be added directly to the formula bar. But, depending on the type of formula, sometimes the parentheses will have to be set manually.

Absolute, Relative, and Mixed Cell Types

Separately it is worth noting that the program can use several types of cells, not to mention the data that they contain.

The absolute cell is unchanged andis denoted as $ A $ 1, the relative is a reference to the usual location (A1), mixed is a combination of references to both absolute and relative cells ($ A1 or A $ 1). Typically, these formats are used when creating cross-references, when the formulas involve data that is on different sheets of the book or even in different files.how to write the formula in excel

The VLF Formulas

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

In a general sense, such computations are something like a simple filter applied to columns, when you want to filter only the exact values ​​rather than the approximate ones.

how to write a formula in excel

In this variant, first through the "Function Wizard"the range of values ​​of the original (first) table is used, the second range with content fixation (F4) is specified in the "Table" field, then the column number is indicated, and the value in the interval field is set to "FALSE", if, in fact, but 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 be said that here they have been described far awayNot all formulas that can be used in the Excel spreadsheet. This, so to speak, only the basics. In fact, if you dig back into trigonometry or calculate logarithms, matrices, or even tensor equations, everything looks much more complicated. But in order to learn all this, you need to thoroughly study the manual for the editor himself. And this is no longer the case that in Excel, based on changing data, you can create even the simplest logical games. As an example, you can cite the same "snake", which initially had nothing to do with the table editor, but was reproduced by enthusiasts of her case in Excel.

In the rest it should be clearly understood that,by studying primitive formulas or actions with data, then you can easily master even more complex calculations, for example, with creating cross-references, using different 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'll have to sweat over the theoretical part.

Related news

How to write a formula in Excel: step-by-step instructions, features and recommendations How to write a formula in Excel: step-by-step instructions, features and recommendations How to write a formula in Excel: step-by-step instructions, features and recommendations How to write a formula in Excel: step-by-step instructions, features and recommendations How to write a formula in Excel: step-by-step instructions, features and recommendations How to write a formula in Excel: step-by-step instructions, features and recommendations How to write a formula in Excel: step-by-step instructions, features and recommendations How to write a formula in Excel: step-by-step instructions, features and recommendations How to write a formula in Excel: step-by-step instructions, features and recommendations How to write a formula in Excel: step-by-step instructions, features and recommendations