What is an Excel formula
Microsoft Excel is a software that allows, through the use of a spreadsheet, to manage data and perform calculations, which occur through the use of formulas.
A formula of Excel is - to put it simply - a string through which you declare the calculation you want to make on the data in your possession. It is made up of several elements which are, in general, recurring.
To make you understand this topic well, I propose the formula as an example = IF (A1> 0, "Positive"; "Negative) which is made up of the IF () function, from a reference (A1), From one constant (0) and by several operators (for example, the sign >). All these elements are almost always present in the formulas, although in some cases there may be exceptions, based on the calculation to be made.
To better understand how an Excel formula works, in the next chapters I will explain in detail what the different elements I mentioned earlier are and how they are used.
functions
Often, a formula is confused with the function. As I already explained to you in the previous chapter, a formula is a declaration made by the user to perform a calculation. A function, on the other hand, is a code already defined in Microsoft Excel, which allows you to perform a specific operation.
Taking up the example I indicated in the previous chapter, the function SE () it is not a formula, as it simply expresses a concept: to obtain a true or false result, based on a given condition. When it is transcribed inside a cell, by affixing the sign = and giving all the other arguments, you get a formula based on the function SE ().
Functions can be used individually within a formula or linked together, creating more complex formulas, through which a calculation is declared that could never be performed with a single function.
In Microsoft Excel there are many functions available that span different areas of calculation: those concerning the subjects financial statistics trigonometry or logic, just to give you some examples.
Functions can be called within a cell by typing the sign first = and then indicating their declaration parameter: in the case of the function SE (), you have to type SE: in doing so, a tooltip below the cell (i.e. a suggestion), which will provide you with directions for the correct compilation of the function.
If you don't know which function to use, in Excel for Windows and macOS you can call it up via the button Insert function present in the card Formulas. If, on the other hand, you use Excel Online, you can call up the same panel using the key Function present in the card Inserisci.
Finally, as regards theExcel app for Android or iOS, you can add a formula using the drop-down menu at the bottom, selecting the item Formulas (on smartphone), or through the appropriate tab at the top (on tablet).
Operators
Any formula used in Microsoft Excel requires the use of operators: these are nothing more than symbols that define a specific operation in the calculation, based on the category to which they belong.
Even the simplest formula = A1, written in the cell B1 of the spreadsheet, is composed by the operator =. The latter is essential to write a formula and consequently start the calculation connected to it.
Among the operators that you can use within a formula, there are those of comparison, such as the sign = which, in addition to what has already been said in the previous lines, also serves to declare that two values must be equal. Then there are the signs > e < which respectively express whether one value must be greater or less than another. The latter signs, combined with the operator =instead, they express that one value must be greater than or equal (or less than or equal) to another.
There are also other operators that are used to define intervals between cells, such as the sign : (i colon) or to group topics, such as round brackets. Another operator often present within a function is the one expressed with the sign ; (The semicolon), which allows you to separate the arguments of a function from each other.
References
After defining the functions and operators, another recurring element in a formula is the reference: the latter has a function similar to the variable of a mathematical formula. The references are, in fact, expressed with the cell links.
Taking the formula as an example = LENGTH (A1), the term A1 represents a cell reference that can contain any user-defined value. Therefore, it does not necessarily have to be a numeric or textual value entered by you in the cell, but it can also be the result of a calculation carried out in the cell itself.
References can be expressed with a single cell (A1) or by an interval (A1: A100) defined by the operator with the sign : (i colon), which I told you about in the previous chapter. A formula that contains a cell reference will vary the result of its calculation, based on the data entered in the cell itself.
When the formulas are repeated in the other cells, through the operation of dragging, the cell reference varies in its form: if in the cell B1 you wrote a formula that contains a reference to the cell A1, dragging it to the cell below (B2), the reference will automatically change to A2. In this case, a row change occurs in the cell reference. This also happens when the repetition of the formula occurs horizontally, which results in a change in the column in the cell reference.
In certain formulas, the operation described above can cause calculation errors, as some references must remain anchored to a cell, without any changes occurring during the row or column translation. In this case, you have to use the operator $ within the reference, as I explain to you in the next lines.
- The reference $ A1 indicates that, with the repetition of the formula, the column must remain blocked, allowing the rows to be varied.
- The reference A $ 1 indicates that, with the repetition of the formula, the row must remain blocked, allowing the columns to be changed.
- The reference $ A $ 1 indicates that as the formula repeats, both the column and the cell row must remain locked, so that the cell reference remains anchored in the formula.
In addition to what has been said in the previous lines, a reference can also consist of a name. Names are nothing more than references that contain within user-defined data, such as a range of cells, a function, a constant or a table. Names can be set by the user via the tab Formulas and selecting the appropriate button Name management.
Constants
Finally, the last elements that can be useful to you in a formula are the constant. A constant is nothing more than a user-defined value that is not variable. A constant is, therefore, disconnected from the concept of cell, as it must be a non-mutable datum.
The constant can be either a numerical value and textual, based on the calculation that needs to be made. Take, for example, the formula = IF (A1> 0, "Positive"; "Negative). The value 0 represents a constant for the cell reference A1. This cannot vary, as it just asks all values in the cell A1 are positive numbers.
Subsequent arguments to the function as well SE () are constants: the terms Positive e Negative these are the conditions of truthfulness that the formula must express, based on the declared condition. These constants are immutable, as they have been specified by the user.
These conditions of truthfulness in the formula can also be replaced by other functions, thus making the value dynamic and susceptible to variations, based on the cell references present in them. I will explain this concept to you in a later chapter of the tutorial.
Compile an Excel formula
Now that you know all the elements you need in a formula, it's time to see how to compile one.
As an example, let's take a formula that aims to verify whether the tax code belongs to a female or a male person.
= IF (VALUE (RIGHT (LEFT (A1, 11), 2)) <= 31, "M", "F)
In the example above, in the cell A1 it contains a tax code of a natural person. If you do not know, within the tax code a number is expressed in the tenth and eleventh digit, which corresponds to the day of birth. While in men this value is between 1 e 31 (the days of the month), in women the value is also added 40.
Having said that, considering the objective defined in the previous paragraphs, it will be necessary to extract this two-digit value from the tax code and compare it with the data in one's possession. To do this, you need the combination of the two functions RIGHT() e LEFT(), which allow you to extract a number of defined characters from a string. This value will then have to be remediated with the function VALUE(), so that it is recognized as a number. Indeed, the figures ranging from 01 a 09 are interpreted as text by Excel but, using the function VALUE(), will be converted to numbers.
In this case, the formula LEFT (A1; 11) will allow you to extract the first ones 11 characters (constant) of a string contained in the cell A1 (reference) starting from the left. The result obtained, which is not a constant, will be used as a reference for the function RIGHT(), through which the extraction of the first ones is requested 2 characters (constant), starting from the right of the string.
The function VALUE()instead, it requires only a cell reference as an argument in order to convert its contents into a value that can be used in the formula. In this case, the reference will be given by the result of the function RIGHT().
To define whether a tax code belongs to a man or a woman, it may be useful to use the function SE (). In this case, you must indicate as the first argument the condition of veracity for the calculation: what you want to know is whether the result obtained is less than or equal to 31, in order to identify the tax code as belonging to a man. Values different from the one indicated indicate that, instead, it belongs to a woman.
In this case, the value extracted via the function VALUE() will be the reference required by the formula, which must be compared with the value 31 (constant) via the signed operator <=. The true and false conditions will be expressed with constants respectively "M" e "F", separated from the operator ; (The semicolon).
Obviously, as I explained to you in the previous chapters, the formula must be preceded by the sign =, without which the required calculation will never be able to take place.
How to use Excel formulas
