Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number

Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number

You will sometimes be faced with a long list of numbers, maybe thousands of rows deep, and you’ll just want to know if the sum total is within a plus or minus range of a benchmark number. For example, suppose you oversee a region of several retail stores, and you want to know if their total sales dollars is within a percentage range of your regional sales goal.

In the picture, the formula in cell F2 is
=IF(ABS(C22-F1)/F1<=0.07,"In range","Out of range")

The ABS function allows you to evaluate a "plus or minus" range of (in this example) 7% for the total regional sales goal that is in cell F1.