Among the spreadsheet tools, Excel is undoubtedly one of the most used, thanks to the amount of features and possibilities it offers. Excel is a treasure of multiple functions and formulas that give us the possibility of keeping a centralized and fully organized control of each data, independent of its type, for the final representation of a report, statistic or task.
One of the functions that we know little about Excel 2016 is the ABS function which returns the absolute value of a number, that is, its value without taking into account the sign that it has.
What is the absolute value in Excel
The absolute value of a number is the value of this number regardless of what sign it has. This is what we know as the Excel ABS function. When we talk about absolute value, we are referring to how far away that number is from zero. This means that the absolute value will always be a positive number even if the value is negative.
Difference between relative values and absolute values
- Relative values: This value is the one that will change depending on the cell in which we place it depending on the row and the place of insertion. These values when copied to other cells, will change automatically.
- Absolute values: these values are invariable and will not change when they are pasted in Excel cells. This means that they do not allow modification regardless of where we drag it.
As we have already said, with this function the absolute value of a number will always be positive even if the origin number is negative. The ABS function can be included in the Excel 2016 worksheets and allows us to obtain the absolute value of a number in a single cell.
Step 1
The syntax to use with ABS is basically Number which will be the number from which we will obtain the absolute value.
= ABS (Number)
Step 2
To represent the use of ABS in cell A1 we will enter a negative number and in cell B1 we will use the ABS formula and we will see that its result will always be positive:
= ABS (A1)
Step 3
ABS can be used with other functions of Excel 2016 to increase its analysis capacity, for example, we can have a series of both negative and positive numbers and make the sum of all, the result in normal conditions should separate the positive numbers from the negative ones and show the difference between these, while if we combine the SUMPRODUCTION function with ABS this will add all the numbers as if they were all positive:
= SUMPRODUCT (ABS (A1: A5))
We see how ABS is a practical function for obtaining the absolute value of any number in Excel spreadsheets. As we can see Excel can be very useful for finding the absolute value of a range of positive and negative numbers.
Articles