Syntax
ABS(number)
- number—The number of which you want the absolute value
Sample usage
ABS(Difference@row) or ABS(Actual@row-Estimated@row)
Examples
The ABS function converts any number to its absolute value, which is a positive number reflecting how far that number is from zero.
• For numbers that are already positive, the value remains unchanged.
• For negative numbers, the function removes the negative sign without altering the actual numeric value.
Numeric example
Let's assume you have a sheet with three numeric columns:
- Budget
- Actual Cost
- Variance (Actual Cost - Budget)
You want to create a new column labeled Variance - % to compare the actual cost to the budgeted amount. This value should always be positive because, for this analysis, you’re not concerned about whether the project overspent or underspent; you simply want to assess how closely the actual costs align with the budget.
| Row | Task | Budget | Actual cost | Variance | Variance - % |
|---|---|---|---|---|---|
| 1 | Event A | $1,500 | $1,400 | -$100 | 6.7% |
| 2 | Event B | $2,000 | $2,150 | $150 | 7.5% |
| 3 | Event C | $5,000 | $5,000 | $0 | 0% |
In the Variance - % column, you'd use the following formula:
=ABS(Variance@row/Budget@row)
How it works:
- Variance@row/Budget@row: This section of the formula determines the percentage of the initial budget represented by the ending variance.
- For Row 1, this would be -100/1500, which equals -0.067.
- For Row 2, this would be 150/200, which equals 0.075.
- ABS(...): The ABS function then takes the result of the division and converts it to the absolute value, ensuring it’s always positive, even if the variance was negative.
- -0.067 becomes 0.067, displayed as 6.7%.
- 0.075 remains 0.075, displayed as 7.5%.
The final result is a clean, positive number that indicates the magnitude of the variance, making it ideal for analysis, grouping, and other reporting purposes where the over/under aspect isn’t relevant.
Date example
Let's assume you have a sheet with two date columns:
- Estimated Date
- Actual Date
You want to create a new column called Difference to show how far off from the estimated date each task was actually completed. It’s not important whether it finished ahead of time or behind schedule, simply how many days are between the two date values.
| Row | Task | Estimated date | Actual date | Difference |
|---|---|---|---|---|
| 1 | Task A | Oct 20, 2025 | Oct 18, 2025 | 2 |
| 2 | Task B | Nov 1, 2025 | Nov 4, 2025 | 3 |
| 3 | Task C | Nov 10, 2025 | Nov 10, 2025 | 0 |
In the Difference column, you'd use the following formula:
=ABS([Actual Date]@row-[Estimated Date]@row)
How it works:
- [Actual Date]@row-[Estimated Date]@row: This section of the formula calculates the number of days between the two dates.
- For Row 1, the Actual Date is two days before the Estimated Date, which equals -2
- For Row 2, the Actual Date is three days after the Estimated Date, which equals 3
- ABS(...): The ABS function then takes the result of the subtraction and converts it to the absolute value.
- -2 becomes 2
- 3 remains 3
ABS ensures we have a consistent, positive number that indicates the overall size of the variance, indifferent to its early or late status, making it ideal for at-a-glance reporting and creating dashboards.
Still need help?
Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.