Understanding the MOD function

After weeks of struggling to get this blog upgraded to the latest version of WordPress, it’s finally up and going again. I’ve had to constantly engage in hand-to-hand combat with WordPress over the last few months. I think I’ve beat it into submission, but who knows what nonsense will pop up next. I’ve come very close to completely closing up shop and becoming a monk.

But enough complaining. Back to business.

Erin (One of my 12 fans) asked me to explain the MOD function. She says:

“I’ve see MOD used in one form or another and I just take for granted that it works. But what does it exactly do?”

 

The Basic Concept behind MOD

MOD is actually Excel’s name for a mathematical term – Modulo. A Modulo is the integer-based representation of the remaining number of units after performing a division operation.

Let’s use some Excel shape art to help illustrate.

Say that I have 8 pieces of bacon. If wake up only one of my kids for breakfast

, he’ll get all eight pieces. There are no pieces of bacon remaining, so the modulo is zero. In Excel, you could write this as =MOD(8,1).


 

If I woke up two of my kids for breakfast, they would share the 8 pieces of bacon – each kid gets 4 pieces. Again, all eight pieces are evenly distributed so there are zero pieces of bacon remaining. In Excel, the formula =MOD(8,2) would indeed return 0.

 

Now let’s say I woke up three of my children, forcing them to share 8 pieces of bacon. In this case, I can’t evenly distribute the bacon. Two children could have three pieces each, but that leaves a modulo of two pieces for the last monster. Anyone who has more than one child knows that this scenario is the start of a damned fight. So no…I don’t think I’ll invite that that third child to breakfast. MOD(8,3) = 2

 

At the risk of beating the point to the ground, let’s do one more. Inviting five children to breakfast ensures that each child gets one piece of bacon, leaving a modulo of three pieces.

Those three pieces go to me. After all, I’m the one that cooked the stupid bacon.

 

Understanding the Math

Although you can use the pre-canned MOD function in Excel, it is useful to know the math behind MOD.

A commonly used alternative expression for getting to a modulo is:

INT(Numerator/Denominator)*Numerator-Denominator

So….

=INT(8/5)*(8-5)

Will give the same result as

=MOD(8,5)

Clever Uses for the MOD Function

I’ve see the MOD function is used in all kinds of clever ways over the years.

You’ll often see this inconspicuous function in formulas, conditional formatting, and even VBA.

Here is just a sampling of nifty things you can do with the MOD function.

Count cells that contain odd numbers

Calculate which date Easter falls on

Conditionally Format or highlight every Nth row

Calculate Total Time Worked across AM and PM hours

Calculate Payroll Penalty for Every 15 Minute Increment Late

Check if a value is a Prime Number

 

So there you go. I hope this quick view into the MOD function changed your life. Or at least gave you a craving for bacon.

6 thoughts on “Understanding the MOD function

  1. Jeff Weir

    Hmmm….let’s see. You served up 8 bits of bacon to 5 kids, and got three for yourself. But there were actually 30 slices of bacon in the pack, and that pack was unopened when you started the day. And now the empty bacon wrapper is in the trash.

    Mrs Pig then wakes up, smells the bacon, hears your math lesson explaining why she’s out of luck, sees the empty wrapper, does some math of her own, and gets mighty angry.

    If I was the kids, I’d get you to whistle while you cook in future.

  2. Doug Glancy

    Hey Mike, welcome back. Sorry to hear about your WordPress hassles.

    I wrote a post about MOD a while back, specifically how to increment the results up by 1 and eliminate the zeroes when using it to “count off” items in a list. The thing I like best about the post is the groovy illustration of the Fab Four (a bit after their mod era): http://yoursumbuddy.com/mod-zeroes/

  3. Alan

    Checking your math on this one

    =INT(8/5)*(8-5) works because =INT(8/5) is 1

    I think what you want is =8-(INT(8/5)*5)

  4. Juanma

    The formula to calculete MOD is wrong INT(Num / Div) * (Num – Div)

    INT(28/3) * (28 – 3) = 255

    The right formula is: Num – ( INT( Num / Div) * Div)

    28 – ( INT( 28 / 3 ) * 3) = 1

Leave a Reply

Your email address will not be published. Required fields are marked *