Knowledge base / Excel

ABS in Excel: absolute values, differences and tolerance checks

I show how to measure the size of a difference regardless of direction, and why removing signs can hide information that matters in a report.

Kajo RudzińskiPublished Updated 5 min read

Two equal golden measuring arms extending in opposite directions from a central platform

When comparing two values, sometimes I need the size of the difference regardless of its direction. Excel’s ABS function returns that absolute value. For -7 it returns 7, for 7 it also returns 7, and for 0 it returns 0.

The Polish partner of this entry also needed a naming correction. The correct function name is MODUŁ.LICZBY, as confirmed by Microsoft’s Polish documentation. Its former name, WARTOŚĆ.BEZWZGLĘDNA, described the concept but was not a valid function name. The historical URL remains available for existing links.

Syntax and three basic cases

ABS takes one numeric argument:

=ABS(number)

That argument can be a number, a reference or a calculated expression. =ABS(A2) produces a result based on A2 while leaving the source unchanged. I keep the original because its sign may be useful later.

All numbers in this entry are synthetic examples. A small set should include a negative value, a positive value and zero:

Formula Result
=ABS(-7) 7
=ABS(7) 7
=ABS(0) 0
=ABS(-2.5) 2.5

The result is nonnegative, which includes zero. The old English text promised an always positive result. That distinction matters when zero represents an exact match and needs to be recognized explicitly. The basic operation is described in Microsoft’s ABS guide.

English examples use a decimal point and commas between arguments of nested functions. The Polish page uses localized names, decimal commas and semicolons. Separators depend on regional settings, so I check Excel’s argument tooltip when copying a formula between environments.

Distance between two values

Suppose A2 contains a measured value of 93 and B2 a reference value of 100. =A2-B2 gives -7. The sign explains the direction: the measurement is below the reference.

For distance alone, I write:

=ABS(A2-B2)

Subtraction first produces -7, and ABS then returns 7. Swapping the inputs produces a difference of 7 and the same absolute value of 7. This is useful when comparing measurements or reconciling two summaries.

I watch out for the superficially similar =ABS(A2)-ABS(B2). With 93 and 100, it returns -7, so it is not the distance. The grouping matters: when I need the magnitude of a difference, I put the entire subtraction inside ABS.

Tolerance: testing the boundary

Take an illustrative tolerance of 10 units. This is a practice assumption, unrelated to any particular reporting standard. With two valid numeric inputs, I can use:

=ABS(A2-B2)<=10

The comparison returns TRUE or FALSE. The <= operator includes the boundary: a difference of exactly 10 passes. A rule requiring strictly less than 10 needs < instead.

For readable labels, I add IF:

=IF(ABS(A2-B2)<=10,"OK","Outside tolerance")
Measured A2 Reference B2 Absolute difference Label
93 100 7 OK
90 100 10 OK
111 100 11 Outside tolerance

I deliberately include a boundary case. A difference of 7 alone cannot reveal whether the rule includes equality. For a changing threshold, I keep it in a labeled parameter cell and fix its reference when copying. My guide to Excel dollar signs explains how to control that behavior.

A square tolerance frame with several blocks inside and one beyond its boundary

Sum of absolute values versus absolute value of a sum

Consider two differences, -7 and 7. Their sum is 0, and its absolute value is also 0. The opposing directions cancel. Summing their individual absolute values instead gives 14: each deviation has a magnitude of 7.

Both results can be correct, but they answer different questions. A net difference describes the combined direction after aggregation. Summed magnitudes describe the total amount of deviation across records. I name the question before choosing the formula.

For -7 in A2 and 7 in A3, I enter this in B2:

=ABS(A2)

I copy it to B3 and then sum the results:

=SUM(B2:B3)

The result is 14. The helper column exposes both transformations, and SUM collects the resulting numbers. This readable approach also works in older Excel versions without introducing array formulas. It replaces the old range-inside-ABS example, whose entry requirements differed between dynamic array Excel and older versions.

When removing the sign loses useful information

A minus sign may represent a return, decline, shortage or movement in the opposite direction. I do not assume it is a data error without knowing the field’s meaning. Converting every input to a nonnegative number can change what the report says even though the formula works.

If -7 and 7 represent changes in a balance, their net change is 0. After removing signs, 14 describes the amount of movement rather than the final balance change. I therefore keep a signed difference and its absolute value in separate, clearly labeled columns.

A tolerance check also assumes two valid numeric inputs. A missing measurement requires its own rule. I do not want an “OK” label to be mistaken for proof that the record is complete. Before comparing a difference, I establish that both inputs were supplied.

Names, errors and a useful exercise

If Excel does not recognize a formula, I check the function name in the installation’s language. Microsoft lists misspelled and unrecognized names among the causes of a #NAME? error. Correcting an article heading alone does not repair an invalid name in a pasted formula.

Do not confuse ABS with MOD. ABS returns an absolute value; MOD concerns the remainder after division. The similarity between Polish MODUŁ.LICZBY and MOD does not imply similar behavior.

For practice, create pairs of 93 and 100, 90 and 100, then 111 and 100. Calculate the signed difference, its absolute value and the tolerance label. Change the threshold from 10 to 7 and predict the new labels. Finally, add two equal values: both the difference and its absolute value should be 0. That small case is precisely what “always positive” failed to explain.

Key takeaways

What is ABS called in Polish Excel?

The correct name is MODUŁ.LICZBY. The previous Polish version of this entry used WARTOŚĆ.BEZWZGLĘDNA, which is not the function's name in Polish Excel.

Does ABS always return a positive number?

The result is nonnegative. Zero remains 0, a negative input loses its minus sign and a positive input remains unchanged.

How can I check a tolerance of 10?

For numeric A2 and B2, use =ABS(A2-B2)<=10. A difference of exactly 10 meets this inclusive tolerance.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.