All versionsExamples need Excel 2021 or laterMath

ABS

ABS in Dutch Excel

Returns a number without its sign, so a shortfall and an overshoot of the same size become the same distance from zero.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCDE
1AccountDescriptionLedgerSpecificationDifference size
213001300Receivables€412,350.00€ 412.350,00€413,100.00€ 413.100,00€750.00€ 750,00=ABS(D2 - C2)=ABS(D2 - C2)
316001600Payables€268,900.00€ 268.900,00€267,400.00€ 267.400,00
411001100Bank€95,210.00€ 95.210,00€95,210.00€ 95.210,00
540004000Payroll costs€1,184,300.00€ 1.184.300,00€1,190,500.00€ 1.190.500,00
680008000Revenue€3,350,000.00€ 3.350.000,00€3,348,200.00€ 3.348.200,00

Result: €750.00€ 750,00

=ABS(D2 - C2)
=ABS(D2 - C2)

What ABS does and when to use it

What it does: it removes the minus sign from a number, so the difference between a ledger of 412,350 and a specification of 413,100 is 750 whichever way you subtract.

When to use it: when you want the size of a difference and the direction does not matter, such as a tolerance check or a list of variances sorted by size. When the direction matters, leave ABS out and read the sign.

How Excel evaluates it

Excel evaluates whatever is inside the parentheses first, so a subtraction or a whole array expression is finished before ABS sees it. It then looks at the sign: a negative number is multiplied by -1, a positive number or zero is returned unchanged. That is the entire function.

Given a range or an array, ABS handles every element separately and returns an array of the same shape, which is what makes it useful inside SUMPRODUCT, MAX and AVERAGE.

Watch out: ABS destroys information: apply it only where the direction does not matter.

Mental model

The distance between two marks on a ruler: it is the same whether you measure left to right or right to left.

Terms used on this page: array, range

Syntax

Official ABS documentation
ABS(number)
ABS(number)
ArgumentTypeMeaning
numberRequiredThe number, cell or calculation whose sign you want removed.

Return value:A number: the value without its sign

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Size of each difference between ledger and client specification

You reconcile the trial balance of Veldhuis Installatie BV at 31 December 2025 to the client's specifications:

  • the receivables list
  • the payables list
  • the bank statement
  • the payroll summary

For each general ledger account you subtract the ledger balance from the specification. A difference can go either way, but to decide which accounts to chase first you want the size of each difference, so you can sort on it.

ABCDEF
1AccountDescriptionLedgerSpecificationDifference size
213001300Receivables€412,350.00€ 412.350,00€413,100.00€ 413.100,00€750.00€ 750,00=ABS(D2 - C2)=ABS(D2 - C2)
316001600Payables€268,900.00€ 268.900,00€267,400.00€ 267.400,00
411001100Bank€95,210.00€ 95.210,00€95,210.00€ 95.210,00
540004000Payroll costs€1,184,300.00€ 1.184.300,00€1,190,500.00€ 1.190.500,00
680008000Revenue€3,350,000.00€ 3.350.000,00€3,348,200.00€ 3.348.200,00
7
Formula cell (E2)Input

Formula in E2

=ABS(D2 - C2)
=ABS(D2 - C2)

Result: €750.00€ 750,00

Step by step
  1. Excel evaluates the subtraction inside the parentheses first: D2 - C2 = 413100 - 412350 = 750. The receivables list is 750 higher than the ledger.
  2. ABS looks at the sign of 750. It is positive, so the value comes back unchanged: 750.
  3. Copy down to E3. D3 - C3 = 267400 - 268900 = -1500: the payables list is lower than the ledger. ABS sees a negative number, multiplies by -1 and returns 1500.
  4. E4: 95210 - 95210 = 0, and ABS(0) is 0. E5: 1190500 - 1184300 = 6200, so 6200. E6: 3348200 - 3350000 = -1800, so 1800.
  5. Sort the table on column E, largest first: 4000 Payroll costs (6200), 8000 Revenue (1800), 1600 Payables (1500), 1300 Receivables (750), 1100 Bank (0). Sorting on the signed difference would have put revenue last, because -1800 is the smallest number.
  6. Change D4 to 95510 and E4 becomes 300. That the underlying difference is +300 is not visible in column E, which is the point of this column.

Watch out: If a specification cell holds text such as 'to follow', the subtraction returns #VALUE! and ABS passes it on. Blank cells are fine: they count as 0, so a blank specification shows the full ledger balance as the difference.

More on this example: why it works, 1 variation

Why it works

The subtraction produces a signed difference and ABS keeps only its size, so column E answers one question: how far off is the ledger from the specification. Because the direction is discarded, the column can be sorted and summed as a measure of total unreconciled amount, which the signed column cannot, since its positive and negative differences would cancel.

Variations

=D2 - C2
=D2 - C2

Keep this signed version in its own column next to the ABS column, so you see both the size and the direction when you explain the difference to the client.

Intermediate

Check that debit equals credit per entry within 5 cents

In the auditfile of a client you total the debit and the credit per entry (boekstuk) as part of journal entry testing. Every entry must balance. A difference of a few cents can come from rounding in the VAT lines of the export and is accepted; anything larger must be followed up with the client.

You want a status column that says OK or Check, with the tolerance in one cell so you can change it without editing the formula.

ABCDEFG
1Entry no.DebitCreditStatusTolerance
225-0412€4,840.00€ 4.840,00€4,840.00€ 4.840,00OK=IF(ABS(B2 - C2) <= $F$2, "OK", "Check")=IF(ABS(B2 - C2) <= $F$2; "OK"; "Check")0.050,05
325-0413€1,210.50€ 1.210,50€1,210.53€ 1.210,53
425-0414€9,075.00€ 9.075,00€9,705.00€ 9.705,00
525-0415€362.34€ 362,34€362.30€ 362,30
625-0416€15,000.00€ 15.000,00€15,000.08€ 15.000,08
7
Formula cell (D2)Input

Formula in D2

=IF(ABS(B2 - C2) <= $F$2, "OK", "Check")
=IF(ABS(B2 - C2) <= $F$2; "OK"; "Check")

Result: OK

Step by step
  1. Excel evaluates the innermost part first: B2 - C2 = 4840 - 4840 = 0. ABS(0) is 0.
  2. The comparison 0 <= $F$2 reads F2 (0.05) and gives TRUE. IF returns its second argument, OK.
  3. D3: 1210.5 - 1210.53 = -0.03, the credit is 3 cents higher than the debit. Without ABS, -0.03 <= 0.05 would be TRUE for the wrong reason: every negative difference, however large, passes a one-sided test. ABS turns it into 0.03, which passes on merit: OK.
  4. D4: 9075 - 9705 = -630. ABS(630) = 630, and 630 <= 0.05 is FALSE, so IF returns Check. The digits 7 and 0 look swapped, which suggests a typing error in a manual entry; that is what the column is for.
  5. D5: 362.34 - 362.3 = 0.04, within tolerance, OK. D6: 15000 - 15000.08 = -0.08, ABS gives 0.08, and 0.08 <= 0.05 is FALSE: Check. Two of five entries need a look.
  6. Change F2 to 0.1 and D6 flips to OK, because 0.08 <= 0.1 is TRUE, while D4 stays on Check. The $ signs keep every row reading F2; without them D3 would compare against F3, an empty cell, and treat the tolerance as 0.

Watch out: A difference that is exactly the tolerance can fail because of floating point: 12.55 - 12.50 can be stored as 0.05000000000000071, which is not <= 0.05. If exact boundaries matter, compare ROUND(ABS(B2 - C2), 2) instead.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

A tolerance is a distance, not a direction: an entry is out of balance whether the debit or the credit is the larger. ABS converts the signed difference into that distance, and a single <= comparison then covers both sides. Without ABS you would need two comparisons joined by AND, and it is common to forget one of them.

More to watch out for

  • If an entry has no credit lines in your extract, the credit cell is empty and counts as 0. The difference is then the full debit and the entry shows Check. That is correct, but it may point to an incomplete extract rather than a wrong amount.

Variations

=ABS(B2 - C2) <= $F$2
=ABS(B2 - C2) <= $F$2

Return TRUE or FALSE instead of text, which is simpler to count with COUNTIF and to use in conditional formatting.

Advanced

Count invoices that differ from the ledger by more than the follow-up threshold

You match the invoices in the receivables subledger to the amounts posted in the general ledger. Differences up to the follow-up threshold of 200 (set from the audit threshold for misstatements to report) are not pursued. You want one cell that says how many invoices differ by more than that, in either direction, without adding a helper column per invoice, because the sheet holds hundreds of invoices.

ABCDEF
1Invoice no.SubledgerGeneral ledgerFollow-up threshold200200
2F-25-0881€4,235.00€ 4.235,00€4,235.00€ 4.235,00Invoices above it33=SUMPRODUCT(--(ABS(B2:B7 - C2:C7) > E1))=SUMPRODUCT(--(ABS(B2:B7 - C2:C7) > E1))
3F-25-0882€1,815.00€ 1.815,00€2,057.00€ 2.057,00
4F-25-0883€12,100.00€ 12.100,00€11,495.00€ 11.495,00
5F-25-0884€968.00€ 968,00€968.00€ 968,00
6F-25-0885€3,025.00€ 3.025,00€3,150.00€ 3.150,00
7F-25-0886€7,260.00€ 7.260,00€7,560.00€ 7.560,00
8
Formula cell (E2)Input

Formula in E2

=SUMPRODUCT(--(ABS(B2:B7 - C2:C7) > E1))
=SUMPRODUCT(--(ABS(B2:B7 - C2:C7) > E1))

Result: 33

Step by step
  1. Excel evaluates the innermost expression on the whole range: B2:B7 - C2:C7 subtracts the ledger from the subledger per invoice and gives the array 0, -242, 605, 0, -125, -300.
  2. ABS works on each element and removes the signs: 0, 242, 605, 0, 125, 300. These are the sizes of the differences.
  3. The comparison > E1 tests each size against 200: FALSE, TRUE, TRUE, FALSE, FALSE, TRUE. Invoices F-25-0882, F-25-0883 and F-25-0886 are above the threshold.
  4. The double minus converts TRUE and FALSE into 1 and 0: 0, 1, 1, 0, 0, 1. SUMPRODUCT ignores logical values, so without this step it would return 0.
  5. SUMPRODUCT adds the array: 0 + 1 + 1 + 0 + 0 + 1 = 3. Three invoices need follow-up.
  6. Change E1 to 300 and only 605 is above it (300 > 300 is FALSE), so the result becomes 1. Set E1 back to 200 and change C7 to 7360: the difference is -100 and the result is 2.

Watch out: Leave out ABS and the formula silently counts only invoices where the subledger is the higher one: B2:B7 - C2:C7 > E1 is TRUE for 605 alone, and the result is 1. It looks like a valid count and nobody notices the other two invoices.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

ABS accepts an array and returns one, so every invoice difference is turned into a size in one step and a single comparison covers both a subledger above and below the ledger. SUMPRODUCT then does the counting. COUNTIFS cannot do this, because a criterion cannot subtract two columns and apply ABS to the result.

More to watch out for

  • If one amount is text (an export with a currency sign, 'EUR 4235'), the subtraction gives #VALUE! for that element and the whole count is #VALUE!. Clean the text before you match.
  • The test uses >, so a difference of exactly 200 is not counted. If your methodology says 'up to but excluding 200', change it to >= and document which one you mean.

Variations

=MAX(ABS(B2:B7 - C2:C7))
=MAX(ABS(B2:B7 - C2:C7))

The largest single difference, 605, when the question is 'how big is the worst invoice' rather than 'how many'.

=SUMPRODUCT(ABS(B2:B7 - C2:C7))
=SUMPRODUCT(ABS(B2:B7 - C2:C7))

The gross difference, 1272. The net difference SUM(B2:B7) - SUM(C2:C7) is only -62, because the differences offset each other, which is why the net total alone would hide the problem.

MasterExcel 2021 or later

Mean absolute deviation of monthly payroll costs with LET

For the analytical review of account 4000 Payroll costs you want a single number for how much the monthly postings fluctuate, in euros, that you can explain to the engagement partner: 'in an average month the costs are about 8,4008.400 away from the typical month'. That is the mean absolute deviation, and you can use it to set what counts as an unusual month.

The standard deviation answers a similar question but squares the misses, so one unusual month dominates it and the unit is harder to explain. The formula must show its steps for the reviewer of your file.

ABCDE
1MonthPayroll costs (x 1000)Mean absolute deviation
2Jan82.0082,008.48,4=LET( costs, B2:B6, typical, AVERAGE(costs), distances, ABS(costs - typical), AVERAGE(distances) )=LET( costs; B2:B6; typical; AVERAGE(costs); distances; ABS(costs - typical); AVERAGE(distances) )
3Feb85.0085,00
4Mar79.0079,00
5Apr108.00108,00
6May81.0081,00
7
Formula cell (D2)Input

Formula in D2

=LET(
  costs, B2:B6,
  typical, AVERAGE(costs),
  distances, ABS(costs - typical),
  AVERAGE(distances)
)
=LET(
  costs; B2:B6;
  typical; AVERAGE(costs);
  distances; ABS(costs - typical);
  AVERAGE(distances)
)

Result: 8.48,4

Step by step
  1. LET binds costs to B2:B6: the five values 82, 85, 79, 108 and 81.
  2. typical is AVERAGE(costs): (82 + 85 + 79 + 108 + 81) / 5 = 435 / 5 = 87. This is the 'typical month' the deviations are measured from.
  3. costs - typical subtracts 87 from each month: -5, -2, -8, 21, -6. They add up to 0, as deviations from a mean always do, which is why averaging them without ABS would tell you nothing.
  4. ABS removes the signs element by element: distances is 5, 2, 8, 21, 6.
  5. AVERAGE(distances) = (5 + 2 + 8 + 21 + 6) / 5 = 42 / 5 = 8.4. In an average month, the costs are 8,4008.400 away from the typical 87,00087.000.
  6. Change B5 (April) from 108 to 88: the mean becomes 415 / 5 = 83, the distances 1, 2, 4, 5, 2, and the result drops to 14 / 5 = 2.8. Without the April outlier the series looks calmer, in proportion, not squared.

Watch out: The mirror-image mistake is AVERAGE(costs - typical) without ABS: it returns 0 for every dataset, and a 0 in a fluctuation cell looks like 'perfectly stable' rather than 'wrong formula'.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

Deviations from a mean cancel out by construction, so a measure of spread has to remove the signs before averaging, and ABS does that in the unit of the data itself (euros, not squared euros). LET names each stage, so the formula reads as its own explanation and the mean is a named value (typical) you can inspect, instead of an AVERAGE nested inside another AVERAGE.

Do not use this construction when the number goes into a statistical test, a sampling calculation or a comparison with published figures: those conventions use the standard deviation, and STDEV.S is the alternative, with the trade-off that outliers weigh more and the number is harder to explain in a meeting.

More to watch out for

  • ABS(AVERAGE(costs) - costs) gives the same distances, but ABS(AVERAGE(costs)) - costs does not: the parentheses decide whether you measure distance or subtract from a positive mean. Check that the subtraction sits inside ABS.
  • A month that is not posted yet must be blank, not 0. AVERAGE skips blanks, but a 0 is a real month with zero costs and drags the mean down: with a sixth month of 0 in the range, the mean drops from 87 to 435 / 6 = 72.5, that month alone is 72.5 away, and the result jumps from 8.4 to 145 / 6 = 24.17.

Variations

=AVERAGE(ABS(B2:B6 - AVERAGE(B2:B6)))
=AVERAGE(ABS(B2:B6 - AVERAGE(B2:B6)))

The same calculation in one line for Excel 2021 or Microsoft 365, where AVERAGE accepts the array directly; harder to audit, because the two AVERAGE calls do different jobs and neither result has a name.

=SUMPRODUCT(ABS(B2:B6 - AVERAGE(B2:B6))) / COUNT(B2:B6)
=SUMPRODUCT(ABS(B2:B6 - AVERAGE(B2:B6))) / COUNT(B2:B6)

For Excel 2019 and earlier, where AVERAGE over an array expression needs Ctrl+Shift+Enter and SUMPRODUCT does not.

Common mistakes

SymptomCauseFix
0 as the distance between 50 and -50ABS(A2) - ABS(B2) is used to measure how far apart A2 and B2 are, and gives 0 for 50 and -50.Subtract first, then remove the sign: ABS(A2 - B2). The distance between 50 and -50 is 100.
FALSE for a difference of exactly one centA tolerance check such as ABS(A2 - B2) <= 0.01 sometimes returns FALSE for two amounts that differ by exactly one cent.Decimals are stored in binary, so a one-cent difference can come out as 0.0100000000000051, a hair above 0.01. Round the difference first, ROUND(ABS(A2 - B2), 2) <= 0.01, or set the tolerance a little above the boundary you mean, such as 0.015.
Overspending no longer shows in the reportA variance report wraps everything in ABS, and nobody can see any more which departments overspent.Keep the signed variance in its own column and add the ABS column next to it for sorting.

Used in combinations

  • Differences above the reporting threshold with LET, FILTER, HSTACK and ABSReconcile the ledger to the client's specification per customer and list only the differences above the reporting threshold: LET names the differences once, HSTACK pairs them with the customer, FILTER keeps the rows where ABS exceeds the threshold.Excel 2024 or later
  • Round-sum journal entries with FILTER, MOD, ROUND and ABSFlag ledger lines whose amount is an exact multiple of 1,000 (and above a minimum): ABS removes the sign, ROUND clears floating-point noise, MOD tests the multiple and FILTER lists the lines.Excel 2021 or later