Există un număr aparent infinit de formule în Excel, dar dacă le iei pe rând și înveți câteva mecanisme, îți poți face viața semnificativ mai ușoară, fără să le știi pe toate.
Microsoft Excel is one of the most complex and reliable programs ever created by the American company.However, it is used in every corner of the world.The difference is given by the degree of complexity at which each user is thrown.
Most are limited to type some values in some cells and get them to the printer, enthusiastic that they look nice in that default table.Excel is for much more than that, and the most important component of its exploitation.Even if you are limited to mathematical formulas only, the mechanism is surprisingly friendly and it is enough to take on some basic knowledge to take advantage of it.
In the following lines, I will try to synthesize the basic philosophy that you need to assume to start using formulas in Excel.After you have accumulated a few principles, you can move to significantly more complex constructions than that.However, it is important to start from somewhere and aware that, depending on your field of activity, such information makes your life significantly easier.Ultimately, this is the most important.
How do you use Formulas in Excel for simple or complex calculations
Each excel formula must start with "=" without quotation marks, as in the image above.The symbol for equal at first tells the application that you are going to make a calculation.It does not matter if you will perform logical operations, simple calculations or a complex string of combined operations, you start with "=".
Before you learn that each information in a cell can be used as a parameter in a formula you type in another cell, you must keep in mind that you can do multiplications, assemblies, decreases or divisions with known values, ordinary numbers.
In other words, if you have a Microsoft Excel window displayed on the screen, you no longer have to open the computer to make a multiplication, division or a cumulation of operations between which there is a relationship.You just have to know that, in an Excel cell, the division I learned at school as represented with ":", in the Microsoft program is represented with "/".In the case of multiplications, use "*" instead of "x".The assemblies and decreases are still represented with "+" and "-".
O alte diferență importantă constă în utilizarea parantezelor. Deși poți folosi un număr infinit de construcții cu paranteze când creezi formule în Excel, nu vei folosi niciodată paranteze pătrate sau acolade. Întotdeauna te vei rezuma la paranteze rotunde. Dacă ai mai multe seturi de paranteze, vei tasta paranteze rotunde care ”îmbrățișează” alte paranteze rotunde, iar Excel-ul va calcula rezultatul final pornind de la prima paranteză din interior spre exterior.
All these detailed principles above I tried to reproduce them in the table above with examples.So you see that a simple association of terms can generate different results depending on the use of brackets.
Formulas in Excel with references to cells: How much can be complex
The parameters of a formula in Excel can be typed in a lot of ways and you do not have to learn them all from the beginning.You just have to keep in mind that there is and, with a little patience, to learn because you need them.In the image above, I tried to reproduce a significant part of them, starting from the values printed in a table to the left.
If you want to make a multiplication between the C8 and C9 cell values, type the formula:
= C8*C9
Where C is the name of the column in which the value you want and 8 and 9 are found, respectively, is the reference for the row in the respective column.When you press Enter, your result will be displayed immediately.It is important to note that the references to the formulas can be achieved by the manual type of the C8 variable, as well as by clicking on the cell of interest after pressing "=".
If the product between C8 and C9 is used in a more complex calculation, you can pass it in round brackets, as in the case of ordinary numbers, as I explained above.
Într-o formulă poți combina valori cunoscute și variabile, fără niciun fel de emoții, precum în exemplul de mai jos.
= C8*C9/(23+15)
Initially, the amount will be calculated from the bracket, after which the multiplied values will be divided into the result of the meeting.
The advantage derived from the use of variables of form C8 or C9 is that, if you later change the information from those cells, without additional intervention from you, the formula in which you typed those variables will generate a different result.It is normal to be so, but it is interesting to see, when you have ten different formulas in a table, that they will all generate different values immediately after you have changed a single cell.
Formula in Excel with serial references
It is useful to calculate in Excel the product of some hand -tuned values or a sum, but it is more interesting when you find out the result of the meeting between ten, twenty or thirty numbers in just a few moments.For this purpose, many mathematical formulas in Excel bear the definition of a value beach.
= Sum (C2: C10)
After pressing Enter for confirmation, it will gather all the values in the cells C2, C3, C4, C5, C6, C7, C8, C9 and C10.Also from the prism of flexibility, the same formula will generate the same result if you write it in the next form.
=SUM(C2:C5;C6:C10)
In other words, the members of a sum or product, can be separated with ";", if you pass them into a series of the type C2: C10.It may seem strange at first, but those two points have to translate them mentally in a form construction, I want the amount from C2 to C10, where it is replaced with ":".
After you have assumed these details, instead of the amount you can calculate the product of all the values in brackets with the formula
= Product (C2: C10)
For the arithmetic mean, you replace Product with Average.If you want to find out the maximum value in a string, you can use Max, while the minimum is with min.
As an interesting reference, although you would be tempted to believe that you can declare series in a formula only on a single column or line, it is not.If you use in a C2 formula: E9, the Excel will interpret the respective surface as a matrix and perform the desired operation on all values that are curved between the upper left corner C2 and the lower right corner E9.
Formulas in Excel generated automatically, cherry on the cake
The last detail I would insist in Excel is the fact that you can write a formula from scratch, as then, to be adapted to many other series of cells.
For example, let's say you have a table with ten employees.Near the first employee, you have the salary and you would like to find out how much the state tax pays, going from the premise that the tax is 16%.If his salary is passed in cell C2 and you want to display the d2 tax, click in D2 and type
= C2*0.16
If you receive error in typing the formula, you may have the language set on English-US.Then, type 0.16 instead of 0.16 inside the formula.Most formula problems in Excel are solved by replacing the comma point or vice versa.
After you have obtained the result in the first cell on the top right, with the left click, pull down the lower right corner of the respective cell, until you reach the end of the list of employees.Because I was talking about ten employees, the cell you stop is D11.Automatically you should see the tax calculated for each employee's salary.In each propagated formula, without any intervention on your part, the variable will change from the C2 typed by you to C3, C4 and so on.Predictably, the detailed mechanism above applies to significantly more complex formulas.