Excel Function of the Week - MOD

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

The MOD function is very useful in certain circumstances.

The function returns the integer remainder after a number has been divided by a divisor. For example if you divide 10 by 3, it goes in 3 times with one remaining.

image

I have used this function numerous times when I am looking for values that have a decimal value in a list of data. For example If I have 20,000 rows of data that has been sucked from an accounting system and is supposed to be whole values, and when I sum them I get a value that has decimal places I know that one or more of the numbers has decimal places. Sometimes you get a number that looks like this 58.0000001 or 47.999999992 and if the column of data is showing values with no decimal places these are rounded up or down accordingly for the view in the cell. Adding a column beside the data and putting a formula like=MOD(A1,1) and then copying it to the bottom of the range will give me a list of zeros. Any values that have a decimal place (Eg are not a whole number and do not divide evenly by 1) will display the remainder. Filtering the dataset by this column where it does not equal zero will find these values for you.

Of course MOD has many other uses than this. It can be used to find the whole number remainders from any number.

If you have a unique way of using MOD feel free to comment and let me know.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Bart's picture

We work a schedule that has a

We work a schedule that has a 2 week pay period, with 3 different possible work rotation and repeats every 6 weeks (42 days).
I use the MOD function to assign which rotating work schedule is applied to the pay period. I start by using the begin day of the period and subtract off the base beginning time the schedule was implemented. Using the MOD function on that result will give me one of 3 answers. Zero if we are at the start of the cycle, 14 if we are in the 2nd period or 28 if we are starting the last. Then a simple "IF" statement assigns the correct time card hours.

jethro's picture

sounds like a skillful

sounds like a skillful application of the MOD function - thanks for commenting!

Anonymous's picture

I use the MOD function to

I use the MOD function to identify multiples of a number. For example, looking for whole pallet configurations vs. partials (if I know how many cases make up a pallet, and want to determine if I have full or partial pallets shipping).