Excel is one of the most complete programs to create spreadsheets that we can find. With it we will be able to perform all kinds of operations and functions. And even, thanks to its potential, we can even work with a large number of decimals to obtain results that are as precise as possible. However, in most operations we will not need, for example, 10 decimals, and it will be enough for us, and it will be more comfortable, to work with two or three.
The problem that we can find when working with two or three decimals is that we are losing information. If we opt for rounding, for example, we will be adding a value that does not actually exist, while if we opt for truncation, we will be losing a value that, in the long run, could end up altering the results.
Assuming these possible problems that we can find, next, we will see how to use these two functions.
Round numbers based on the decimal
The first thing we are going to see how to do in Excel is rounding. As we have already explained, when choosing this option, what we are doing is that the program reads a specific decimal of a position and, if it is equal to or greater than 5, it adds one to the previous decimal, while if it is less, it leaves it as it is.
For this task we are going to use the ROUND function. We have to indicate to this function the number (or the cell) that we want to round off, as well as the number of decimal places that we want to obtain in the resulting number. Therefore, the function will be as follows, where A1 is the cell to round off, and N the resulting number of decimal places:
=REDONDEAR(A1;N)
Another way to do it, without resorting to this function, is by using a button that appears on the toolbar. This button is specifically found in the “Number” section, and allows us to adjust the decimal places that we want our number to have with a click.
Truncate in Excel: fit without rounding
If we don’t want the ciphers to go up, and we want to get the most accurate value possible, we can choose to truncate the value . By using the TRUNCATE function, what we get is that Excel cuts off the number at a certain decimal, but ignores the value of the next decimal. In this way, if the following decimal is greater than or equal to 5, one will not be added to the previous decimal, but the same value will be kept as is.
This formula is very similar to the rounding formula. That is, we must first indicate the number, or the cell, that we want to truncate, and then we will indicate the number of the decimal where we are going to cut. In this way, taking into account that A1 is the cell where the value is, and N the number of decimals, the formula is as follows:
=TRUNCAR(A1;N)
As we can see, in our case, some of the values (specifically, those whose fourth decimal was less than 5) remain the same with both functions, while those whose fourth decimal was greater than or equal to 5, are greater when rounding than when truncating
Now, depending on the type of operation that we are performing in Excel, we can choose one function or another.
