Use-case example: Sort values by absolute values
If you’ve read this article until here, you’d now know how to use the ABS function.
However, are you still wondering why you may even need the ABS function? That’s a common thought, and that’s alright.
The data below shows some transactions made from a bank account.
Some of them are positive (that shows cash inflow), and others are negative (cash outflows).
You quickly want to sort all these positive and negative numbers in descending order to find out the most significant transactions.
1. If you apply the simple SORT function:
=SORT (A2:A6, , -1)
The first argument tells the range to be sorted. The second argument is omitted. And the third argument is set to -1 (to denote descending order).
2. The results would be as follows.
Excel sorts the data from the biggest positive number to the biggest negative number. Like we see numbers on a number line.
This takes a bigger transaction amounting to $8000 to a lower position. And ranks a smaller transaction of $2000 transaction above.
That was not what we wanted.
3. To have the transactions rightly sorted, nest the ABS function in the above formula.
=SORT (ABS(A2:A6), , -1)
This time all the transactions are sorted rightly (in terms of the monetary amount). Signs of the amounts are ignored.