Validation in Excel using the ABS function

Avoid issues with negatives

When doing validations in Excel you often need to check values against a tolerance figure, usually 1 cent or 1 dollar.

One way to perform the validation check is to use the ABS function. Rather than checking that the value is between 1 and -1 you can use the ABS function to convert the difference into a positive number.

The ABS function is short for ABSOLUTE, which is a mathematical term meaning the distance from zero. In practical terms, using the ABS function converts all numbers to positives.

This means you can simply compare the difference between two numbers to a single value. See example below.
ABS function example
The formula in cell C2 is

=ABS(A2-B2)<=$F$1

It returns TRUE if the values are within one of each other and FALSE if more than one.

I use these type of logical formulas, that return TRUE and FALSE, for most of my validation calculations. They are calculated very quickly by Excel.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.