What Is the MOD Function?
The MOD function in Excel calculates the remainder after dividing one number by another. Its syntax is simple:
=MOD(number, divisor)
Let's dive into six everyday applications.
1. Splitting Cupcakes: Calculating Leftovers
Imagine you're managing a bakery. You have 10 cupcakes, but each box holds 4 cupcakes. How many are left after boxing them?
In the screenshot below, you can see
that MOD takes the total cupcakes in the number argument and the cupcakes per box quantity in the divisor argument and returns 2:
You can also calculate how many boxes you can fill using:
=INT(C5 / D5)
INT rounds the division result down to the nearest whole number.
2. Extracting Decimal Parts
Need to isolate the decimal part of a number, like .45 from 5.45? In the
screenshot below, you can see 5.45 takes up the number argument, and with 1 in the divisor argument, MOD returns the decimal:
Remember, the MOD function returns the remainder after dividing one number by another. In this case, we're dividing the number in B5 by 1. When you divide 5.45 by 1, the whole number part—5—fits perfectly, leaving a remainder of .45.
3. Extracting Time from Date-Time Values
Excel stores date-time values as
serial numbers. You can see the equivalent serial number in column C for the date-time in column B of the screenshot below. The whole number represents the date, while the decimal represents the time.
In column D I can use MOD to extract the time portion of the date-time value in column B.
Tip: You can use =INT(B5) to extract the date.
4. Calculating
Shift Hours Across Midnight
Handling shift times that cross midnight can be tricky. MOD is an elegant way to ensure time calculations remain positive and within a 24-hour cycle.
In the screenshot below we use two MOD functions to calculate the hours worked either side of the break:
The MOD function prevents negative results and keeps the time difference
accurate.
5. Banded Rows with Conditional Formatting
The banded row formatting in Excel Tables makes them super easy to read, especially if they're
wide.
However, when working with dynamic array formulas that spill, formatting your data in an Excel table isn't an option without encountering SPILL errors!
However, we can get around this using the MOD function in a conditional formatting rule that applies the banded row
format.
Steps:
1. Select your table rows.
2. Go to Home > Conditional Formatting > New Rule
> Use a formula.
3. Enter:
=MOD(ROW(), 2) = 1
This formula applies formatting to odd rows.
Bonus tip: link the format to a checkbox and toggle the formatting on and off with the click of a button and modified formula:
=MOD(ROW(),2)*$C$3
6. Analyzing Sales Every Nth Day
Let's say you run a promotion every third day and want to analyze its impact on sales.
Steps:
1. Use SEQUENCE to generate a series of days and identify every third day with MOD:
=MOD(SEQUENCE(30), 3) = 0
2. Combine with FILTER to extract sales data for those days:
=FILTER(B2:B31,
MOD(SEQUENCE(30), 3) = 0)
3. Wrap with AVERAGE or MAX to calculate metrics:
=AVERAGE(FILTER(B2:B31, MOD(SEQUENCE(30), 3) = 0))
Take Your Excel Skills Further
The MOD function is a versatile tool for simplifying complex calculations. Want to learn more about Excel's hidden gems? Check out our Advanced Excel Formulas course.
Master Excel, one function at a time!
Learn With Us
