Excel ABS Function
Summary
The Excel ABS function returns the absolute value of a number. Negative numbers are converted to positive numbers, and positive numbers are unaffected.
Purpose
Find the absolute value of a number
Return value
A positive number.
Syntax
=ABS (number)
Arguments
- number - The number to get the absolute value of.
Usage notes
For example, ABS(-3) returns a value of 3 and ABS(3) returns a value of 3, because the ABS function returns a number's distance from zero.
ABS formula examples
To find the closest match with a lookup value and numeric data, you can use an array formula based the INDEX, MATCH, ABS and MIN functions. In the example shown, the formula in E5 is:
{=INDEX(data,MATCH(MIN(ABS(data-...
To change negative numbers to positive and leave positive numbers along (i.e. get absolute values) use the built-in ABS function. In the example shown the formula in C6 is:
=ABS(B6)
How this formula works
The ABS...
You can calculate a percent variance by subtracting the original number from the new number, then dividing that result by the original. For example, if the baseline number is 100, and the new number is 110:
=(110-100...
To add an ordinal suffix to a number (i.e. 1st, 2nd, 3rd, etc.) you can use a formula based on the CHOOSE function to assign the suffix.
In the example shown, the formula in C5 is:
=CHOOSE(B5,"st","nd...
If you need to round a number to a given (variable) number of specified digits or figures, you can do so with an elegant formula that uses the ROUND and LOG10 functions.
In the example shown, the formula in D6 is as...
To count values that are out of tolerance in a set of data, you can use a formula based on the SUMPRODUCT and ABS functions. In the example shown, the formula in F6 is:
=SUMPRODUCT(--(ABS(data-target)>tolerance))...
To test if a value is within expected tolerance or not, and report "pass" or "fail", you can use a formula based on the IF and ABS functions. In the example shown, the formula in E5 is:
=IF(ABS(B5-C5)<=D5,"OK...
Good links
