ABS function
Quick answer
ABS returns a number without its sign, so -250 becomes 250 and 250 stays 250.
- Syntax
=ABS(number)- Most common use
=ABS(C2-B2)returns 65 (Under forecast by 65, shown as a size: 65)
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.
Answers that use ABS
In other languages
ABS is ABS in Spanish Excel and ABS in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .