ABS function
ABS returns a number without its sign, so -250 becomes 250 and 250 stays 250. Use it when only the size of a difference matters, such as how far a forecast missed, whichever direction it missed in.
- Available in
- Every current version
- Category
- Math functions
- Returns
- The number as a positive value (zero stays zero).
- Google Sheets
- Works the same in Google Sheets.
Syntax
=ABS(number)
| Argument | Required? | What it is |
|---|---|---|
number | Required | The number or calculation whose size you want. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =ABS(C2-B2) | 65 | Under forecast by 65, shown as a size: 65 |
| D3 | =ABS(C3-B3) | 62 | Over forecast also comes out positive: 62 |
| D5 | =MAX(ABS(C2:C4-B2:B4)) | 65 | The biggest miss across all stores in one formula |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Store | Forecast | Actual | Miss |
| North | 1200 | 1135 | 65 |
| South | 980 | 1042 | 62 |
| East | 1500 | 1488 | |
| 65 |
Practice ABS
Graded instantly, then re-checked on changed data.
Forecast error
D2, use ABS to show how far each month's actual sales landed from the forecast, ignoring direction. Fill down to D6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| The direction is gone | ABS throws the sign away, so you can't tell over from under. Keep the plain difference in another column, or use SIGN to get the direction. |
| #VALUE! with =SUM(ABS(A2:A10)) | In Excel 2019 and earlier this array formula needs Ctrl+Shift+Enter. =SUMPRODUCT(ABS(A2:A10)) works in every version without it. |
| #VALUE! | The cell holds text, such as a dash typed for zero. Replace it with 0 or wrap the reference in IFERROR. |
Tips
- Average absolute error: =SUMPRODUCT(ABS(C2:C13-B2:B13))/COUNT(B2:B13).
Questions
How do I make negative numbers positive in Excel?
Use =ABS(A2). To change the values in place, multiply by -1 with Paste Special, but ABS keeps the original data intact.
How do I sum absolute values in Excel?
Use =SUMPRODUCT(ABS(A2:A20)). It works in every version without Ctrl+Shift+Enter.
How do I find the difference between two numbers regardless of order?
Use =ABS(A2-B2). It returns the same result whichever number is bigger.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.