Excel Function of the Week - MOD
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.
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.
Site Construction and Maintenance by Jethro Consultants
Gidday
Glad to have you stop in.
I write about my family, the internet, technology and gadgets, productivity with Microsoft Office applications Excel Tips and more.
Instagram Feed
Find Us On...
Subscribe by RSS
Recent comments
- ill try to resolve that
10 years 37 weeks ago - When I initially commented I
10 years 37 weeks ago - What I would really like to
10 years 39 weeks ago - sure @spyjournal
10 years 39 weeks ago - Howdy! Do you use Twitter?
10 years 39 weeks ago - You really make it appear
10 years 39 weeks ago - Oh my goodness! Awesome
10 years 39 weeks ago - I think you have to do it via
10 years 39 weeks ago - When I originally commented I
10 years 39 weeks ago - go to hell india
10 years 39 weeks ago
Copyright Notice. All documents and text contained in this web site www.spyjournal.biz and sub pages is copyright material of Timothy Miller © unless noted as being copyright material of someone else. No public reproduction of the content on this site in any form is permitted without express written permission. Privacy Policy
Comments
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.
sounds like a skillful
sounds like a skillful application of the MOD function - thanks for commenting!
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).