As you know, the table-based office editor Excel is originally designed to perform mathematical, algebraic and other calculations, which uses the input of special formulas. However, in the light of how to register the formula in Excel, it must be borne in mind that most of the operations during input are fundamentally different from all that is commonly used in ordinary life. That is, the formulas themselves have a slightly different appearance and use completely different operators that are used to write standard calculations.
Consider the question of how to write a formula in Excel, using a few simple examples, without affecting complex operations, for which you need to study the program quite deeply. 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: initial concepts
So, entering formula values in a program is somewhat different from standard operations, symbols used and operators used. When solving the problem of how to register a formula in Excel, you need to start from 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 the common component out of brackets (“2 + 2) 5”). For this, the use of several types of characters is provided, which are presented in the table below, not counting the logical operators.
Sum, addition | + |
Difference, subtraction | - |
Multiplication, product | * |
Private Division | / |
Exponentiation | ^ |
Interest and operations with them | % |
Greater than, less than or equal, not equal | >, <,> =, <=, <> |
Moreover, the priority of operations starts from the degree and ends with addition and subtraction. In addition, despite the fact that Excel can be used as a regular calculator, as a rule, for calculations it is necessary to indicate cell numbers or their ranges. It goes without saying that the data format in any such cell must be set to the appropriate (at least numerical).
Amount and Difference
How to write 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 (and, 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 "= 2 + 2" for the installed cell.
If summing is done for values entered directly in other cells (for example, A1 and A2), the formula takes the form "= A1 + A2". Often, to use additional operators, the amount can be enclosed in brackets. For the difference - the same thing, only with a minus instead of a plus.
When you need to specify cell numbers or their range, a special sum command can be applied (in the Russian version "SUM", in English - SUM). When specifying several cells, it looks like this: "= SUM (A1; A2)", for the range - "SUM (A1: A10)", provided that it is necessary to calculate the sum of all numbers in the cells from the first to the tenth. In principle, if you set an active cell, which is located immediately after the last one in the column with the original values, you can not enter the formula, just click on the button for automatic summation.
Multiplication, Division, and Power
Now let's see how to write the formula for multiplication or division in Excel. The order is the same as when entering the sum or difference, only the operators differ.
For the work, the form “= A1 * A2” is used, for the particular “A1 / A2”. These, by the way, are exactly the same commands that can be found when using the standard Windows calculator.
For exponentiation, the symbol “^” is used. For the value in cell A1, which, for example, needs to be squared, the formula "= A1 ^ 2" is used.
Percent calculation
With interest, if you do not touch complex calculations, everything is also simple. How to register a formula with percent in Excel?
It is enough to enter a formula of the form “= A1 * 5%”, after which you will receive the same five percent of the value in the corresponding cell.
Using cell input formulas
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.
When the mouse button is pressed, you can simply select the necessary cells by first entering the necessary calculation in the formula bar. Cells will be added directly to the formula bar. But, depending on the type of formula, sometimes 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.
An absolute cell is immutable and is denoted as $ A $ 1, a relative one is a link to a normal location (A1), a mixed one is a combination of links to an absolute and relative cell ($ A1 or A $ 1). Typically, such formats are used when creating cross-references, when the formulas involve data on different sheets of the book or even in different files.
VLF formulas
Finally, let's see how to write the 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 to solve the problem of how to write the formula “Condition” in Excel, which uses the symbols shown in the table above.
In a general sense, such calculations are a kind of simple filter applied to columns when you want to filter out only exact, not approximate, values.
In this option, first, through the “Function Wizard”, a range of values of the initial (first) table is used, the second range with content fixing (F4) is indicated in the “Table” field, then the column number is indicated, and in the interval viewing field is set to “FALSE” if indeed, when filtering, you really need to get only exact, not approximate, values. As a rule, such formulas are used more in warehousing 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 into trigonometry or computing logarithms, matrices, or even tensor equations, everything looks much more complicated. But in order to learn all this, you must thoroughly study the manual for the editor itself. And this is not to say that in Excel, even simple logic games can be created on the basis of 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 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, for example, with the creation of cross-references, using various kinds of scripts or VB scripts, etc. All this takes time, so if you want to study the program and all its features to the maximum, you have to sweat over the theoretical part.