Add Modulo Magic to your Formulae
Become a Modulo Magician and add this trick to your arsenal…
What is it and how do I use it?
Do you remember doing division back at school? Numbers which wouldn’t divide neatly into each other were always a pain. Before you got onto the tricky stuff of long division and writing out all those fiddly decimal places you probably tried it with whole numbers. Something like “9 divided by 4 = 2 remainder 1”?
Modulo is all about the remainder.
Depending on whether you’re using Excel, C#, SQL and so on it might look a little different. For this article we’ll use the % sign for now. Try to forget it is also used to mean percentage. In this article it means “divide by and give me the remainder ONLY”. Here’s an example formula with it:
= 9 % 4
This translates as “Give me the remainder from 9 divided by 4”. In this case, 9 divided by 4 is 2 remainder 1, so that formula would produce the number 1. Let’s try another:
= 8 % 4
This translates as “Give me the remainder from 8 divided by 4”. In this case, 8 divided by 4 is 2 remainder nothing, so that formula would produce the number 0. Written slightly differently we see that:
x % y = the remainder of x / y
Ok I get that but why is this even remotely useful?
Learning new operators and functions isn’t in itself useful. You need to understand what category of problem it is used to solve, and how to recognise that category of problem. Once we can recognise a problem which Modulo is suited for we can understand the problem better and employ a well suited tool in solving it.
The essence of problems which Modulo can solve is the requirement to split up a list into groups. Specifically, for mapping any cyclical pattern to a sequence of numbers. This might sound a bit abstract, so here are some real examples:
- Determining if a number is odd or even
- Evenly assigning a list of people into one of three groups
- Determining whether a square on a Chessboard, numbered 1—64 in this case, is black or white
Notice that they’re all about breaking a list up according to a repeating pattern. This means if there’s a list and some repeating pattern, Modulo might be the tool to use.
Doing it without even thinking
So you’ve recognised a problem which Modulo may be a candidate to solve. Here’s a little trick to be able to write the formula without even working out the results in your head or with a calculator:
- Normalise your “list” so that it’s zero-based if needed. In Excel for example, instead of a reference to cell A1, which is a list starting from 1, you’re going to deduct 1 so it starts from zero, which would be A1-1.
- Normalise your “groups” so that they’re also a zero-based list if needed. Say you had categories “Red”, “Yellow” and “Green” then they would be 0, 1 and 2 respectively.
- Write the formula as A % B format. No thinking required here. A is the index number of the list item, B is the number or COUNT of the groups, e.g. there are 3 groups, “Red, Yellow & Green” so B would be 3.
- Denormalise your zero-based results if needed. Your results If you had to change your group numbers from 1-based to 0-based in step 2 by subtracting 1, then just add one back onto the answer to turn it back into a one-based list.Easy!
The best advice now is just for you to “have a play”. Try a couple of examples out. Once you’ve done it a couple of times it will become automatic. You are then a fully qualified Modulo Magician.
What Modulo looks like in different technologies:
Platform | Syntax
Excel | Mod(A1, B1)
SQL | A % B
C# | A % B
VB | A Mod B
- Before you go, careful not to ever write x % 0. It’s dividing by zero but doesn’t necessarily look like it. If your formula is A % B then make sure B can’t be zero first by using IF.
- Please only use your newfound Modulo Magic powers for good.