The MOD Function in Excel

by Gregory on August 17, 2010

The MOD Function

The MOD Function simply returns a remainder, or fractional part, after a number has been divided. To understand the MOD Function better, a look at how the INT Function can be used may provide some insight.

First, the calculation example is 274 seconds converted to 4:34.  The INT Function was used in a previous post, so we know that INT(274/60) = 4.

Since 4 minutes equals 240 seconds, the following three equations are true.

274 – 240 = 34

274 – 60 x 4 = 34

274 – 60 * INT(274/60) = 34

This is essentially the MOD Function, which has two arguments: Number and Divisor.

If n = 274, and d = 60, then the MOD Function is:

MOD(n,d) = n – d*INT(n/d)

Thinking about this a different way, 274/60 = 4.5667. If I subtract 4 from 4.5667, I get the fractional part 0.5667, which I can multiply by 60 to get the number of seconds: 34.

So the MOD Function is going to give the remainder. In this case the number of fractional seconds left over after dividing by 60. In my example:

MOD(274,60) = 34

MOD Function Arguments Dialog Box

The next post will look at the TIME Function.

Related Posts Plugin for WordPress, Blogger...
michelle.simms September 6, 2012 at 12:24 pm

I am trying to convert minutes into seconds. Need to know the formula for that.
Please provide via email. Thanks-

Gregory September 6, 2012 at 9:25 pm

If you have a cell with a general number, which happen to be minutes, then you multiply by 60 to get the seconds. Example would be the number 1 in cell A1, and the formula in cell B1 would be =A1*60 to get 60 seconds.

If you have a cell that has Time formatting, example would be 0:01:00, for 1 minute, or maybe 1:00 for 1 minute. Further assume this time value is in cell A2. In cell B2 you can use the formula =MINUTE(A2)*60 to get the number of seconds. However you then have to change the cell formatting to General so you can see the number.

Another way to see the 1:00 time of 1 minute is to multiply by 86,400 (the number of seconds in a day) and then change the cell formatting to General to see the number. Same thing as the second example.

Comments on this entry are closed.

Previous post:

Next post: