ABS function

ABS returns the absolute value of any number, removing the sign to show only magnitude, useful for comparing distances regardless of direction.

=ABS(number)

Generate a ABS formula

Describe what you need. The generator will reach for ABS where ABS is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How ABS reads its arguments
numberrequiredABS →
ArgumentRequiredDescription
numberRequiredThe value to convert to its absolute equivalent; must be numeric. If text, blank, or an error reference, ABS returns #VALUE!, #N/A, or #REF! respectively.

Returns

Returns a non-negative number with the same magnitude as the input, stripping any negative sign.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate absolute variance from budget

CategoryMonthBudgetedActualVariance
GroceriesJan400385-15
UtilitiesJan15016212
EntertainmentJan10095-5
GroceriesFeb40042020
UtilitiesFeb150148-2
EntertainmentFeb10011515
=ABS(E2)

Result: 15

The variance in E2 is -15, indicating $15 under budget. ABS removes the negative sign to show the absolute magnitude of 15, making it easy to see how far actual spending was from budget regardless of direction.

2. Find the largest budget variance

CategoryMonthBudgetedActualVariance
GroceriesJan400385-15
UtilitiesJan15016212
EntertainmentJan10095-5
GroceriesFeb40042020
UtilitiesFeb150148-2
EntertainmentFeb10011515
=MAX(ABS(E2:E7))

Result: 20

The variances range from -15 to +20. Using MAX with ABS finds the largest absolute value (20), representing the biggest budget miss—February's Groceries went $20 over budget, which is the largest deviation in either direction across all months.

3. Sum all spending deviations

CategoryMonthBudgetedActualVariance
GroceriesJan400385-15
UtilitiesJan15016212
EntertainmentJan10095-5
GroceriesFeb40042020
UtilitiesFeb150148-2
EntertainmentFeb10011515
=SUMPRODUCT(ABS(E2:E7))

Result: 69

To understand total spending variance, SUMPRODUCT with ABS sums the absolute values: 15+12+5+20+2+15=69. This shows $69 total deviation from budget across all categories, treating over-budget and under-budget misses equally rather than canceling them out.

Common errors

Which ABS error are you seeing?
ABS returned an error#VALUE!
Convert text to a number first using VALUE(), or use IFERROR to handle mixed ranges: =IFERROR(ABS(E2),0)
#N/A
Resolve the error in the source cell, or wrap ABS in IFERROR: =IFERROR(ABS(E2),"?")
#REF!
Verify the cell range exists and is correct; update the formula to reference valid cells in your dataset.
ErrorWhy it happensHow to fix it
#VALUE!ABS received text instead of a number, such as =ABS("fifteen") or a reference to a text cell.Convert text to a number first using VALUE(), or use IFERROR to handle mixed ranges: =IFERROR(ABS(E2),0)
#N/AThe cell referenced by ABS contains #N/A or another error propagated from a formula in that cell.Resolve the error in the source cell, or wrap ABS in IFERROR: =IFERROR(ABS(E2),"?")
#REF!The cell reference was deleted or moved, such as =ABS(E99) where E99 doesn't exist or column E was deleted.Verify the cell range exists and is correct; update the formula to reference valid cells in your dataset.

Tips and when to use something else

  • Use ABS when you need magnitude regardless of sign, such as finding the largest variance, error distance, or deviation in quality metrics.
  • Combine ABS with SUMPRODUCT to sum absolute distances across a range: =SUMPRODUCT(ABS(range)), useful for total-deviation analysis.
  • Choose SIGN instead of ABS when you need to detect direction (-1, 0, +1) rather than magnitude; use both together for complete analysis.
  • For risk analysis with multiple conditions or to exclude error values, consider AGGREGATE as a more powerful alternative to simple ABS formulas.

Related functions

SIGNSIGN returns the mathematical sign of a number—positive returns 1, negative returns -1, and zero returns 0, enabling quick classification of values.SUMPRODUCTSUMPRODUCT multiplies arrays element-by-element and sums the results, useful for weighted totals and multi-criteria counting.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.ROUNDROUND returns a number rounded to a specified number of decimal places, using standard rounding rules (0.5 rounds up).MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.

Frequently asked questions

What's the difference between ABS and SIGN?
ABS returns the magnitude (always non-negative), while SIGN returns the direction: -1 for negative, 0 for zero, +1 for positive. Use ABS when you need size; use SIGN when you need direction.
Can I use ABS with text or mixed data?
No; ABS returns #VALUE! if the argument is text. Wrap it with IFERROR to handle mixed ranges safely, or convert text to numbers using VALUE first.
Does ABS work on ranges or just single cells?
ABS works on single numbers, but you can process ranges with SUMPRODUCT(ABS(range)), array formulas like =ABS(A1:A10) in modern Excel/Sheets, or by dragging the formula down to multiple cells.
When should I use ABS instead of other math functions?
Use ABS purely to remove the sign and get magnitude. Use ROUND to reduce decimal places, AGGREGATE for complex calculations with conditions, and SIGN to detect direction. Each function solves different problems.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17