Excel MOD Function

If like me, you probably never thought you'd use modular arithmetic once leaving high school. Surprisingly, Excel's MOD function brings that idea to life, solving everyday problems with ease. Let's explore 6 practical examples of how you can use the MOD function in your spreadsheets!

Watch the MOD Video

 

Get the MOD Example File

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!