## 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 the**Excel 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.