How to Use ABS, INT, and MOD in Excel

Introduction

Your forecast said 130 mugs and the actual order came in at 145. Another item was projected at 100 but only 87 shipped. Subtracting one from the other gives you 15 and -13, but the report just needs the size of the gap. ABS, INT, and MOD handle the math that regular arithmetic leaves messy.

The Dataset

Here's an order fulfillment log for a small distribution center:

	A	B	C	D
3	Item	Ordered	Per Case	Projected
4	Coffee Mugs	145	12	130
5	Notebooks	87	24	100
6	Pens	310	50	300
7	Stickers	200	36	180
8	Water Bottles	78	8	85

The warehouse manager needs to reconcile the order against last month's projection and figure out how to pack everything before the truck arrives.

ABS

ABS returns the absolute value of a number, stripping the sign so you're left with the magnitude. The syntax is:

=ABS(number)

• number: A value or expression.

To find the gap between the ordered quantity and the projection:

=ABS(B4-D4)

The result is 15. The mugs order came in 15 units above the projection. For notebooks, B5-D5 is -13, but ABS turns that into 13.

Item	Ordered	Projected	Difference	ABS
Coffee Mugs	145	130	15	15
Notebooks	87	100	-13	13
Pens	310	300	10	10
Stickers	200	180	20	20
Water Bottles	78	85	-7	7

Without ABS, the negatives make it look like notebooks had a smaller gap than water bottles. Notebooks were actually further off by 6 units.

Key takeaway: ABS measures the size of a difference without caring about the direction. Use it any time you're comparing actual vs. expected and only the magnitude matters.

INT

INT rounds a number down to the nearest integer. The syntax is:

=INT(number)

• number: A value to round down to an integer.

The warehouse packs items into cases. To find how many full cases each order fills:

=INT(B4/C4)

The result is 12. Dividing 145 mugs by 12 per case gives 12.08, and INT drops the .08 to return 12 full cases.

Item	Ordered	Per Case	Division	Full Cases
Coffee Mugs	145	12	12.08	12
Notebooks	87	24	3.63	3
Pens	310	50	6.20	6
Stickers	200	36	5.56	5
Water Bottles	78	8	9.75	9

INT always rounds down. That's different from ROUND, which would round 9.75 up to 10, and you can't ship a case you haven't filled.

Key takeaway: INT strips the decimal. Use it when a fractional result doesn't make sense, like partial cases, incomplete shifts, or groups that need to be whole numbers.

MOD

MOD returns the remainder after division. The syntax is:

=MOD(number, divisor)

• number: The value to divide.
• divisor: What to divide by.

After packing full cases, the warehouse needs to know how many loose units are left:

=MOD(B4, C4)

The result is 1. Out of 145 mugs, 12 full cases account for 144. One mug is left over.

Item	Ordered	Per Case	Full Cases	Loose
Coffee Mugs	145	12	12	1
Notebooks	87	24	3	15
Pens	310	50	6	10
Stickers	200	36	5	20
Water Bottles	78	8	9	6

INT and MOD split a division into its two useful parts. INT gives you the full groups, MOD gives you what's left.

Stickers have the most loose units at 20, which is more than half a case of 36. If the warehouse can combine partial cases across orders, that's the first one worth consolidating.

Key takeaway: MOD gives you the remainder after division. Pair it with INT to split any division into full groups and leftovers.

Other Uses

These three functions come up outside of warehouse math:

Goal	Formula	Result
Check if a number is even	=MOD(A1, 2)	0 if even, 1 if odd
Get whole hours from minutes	=INT(150/60)	2
Get remaining minutes	=MOD(150, 60)	30
Distance between two values	=ABS(A1-B1)	Always positive

MOD also shows up in alternating-row formulas. =MOD(ROW(), 2) returns 0 for even rows and 1 for odd rows, which you can feed into conditional formatting or helper columns.

Conclusion

ABS strips the sign, INT strips the decimal, and MOD gives you the remainder. If basic arithmetic gives you a number that's technically right but not useful, one of these three probably fixes it. For rounding to a specific number of decimal places instead of truncating, see our guide on ROUND, ROUNDUP, and ROUNDDOWN. For rounding to specific multiples like quarter hours or $5 increments, check out our guide on CEILING, FLOOR, and MROUND.