Ever use Excel’sMOD function?
The MOD function performs themodulo operation.
It takes a number and a divisor, does the division, and gives you back the remainder.
Unless you’re a programmer, this might seem way too nerdy.
What can the average person do with that?
As it turns out a lot!
Note: MOD turns up in many compact, elegant formulas in Excel.
You want something like this:
Simple, right?
But how to begin…what kind of formula does something every 3 months?
Believe it or not, the MOD function is the key.
Earlier, I mentioned the remainder.
In other words, when MOD returnszero.
Think about it when the remainder is zero, it means the divisor goes into the number evenly.
Hmmm…we can use that!
We want to do something every 3 months.
Let’s do it.
First, let’s add month numbers above our table so we have something to work with right away.
Think of this as rapid prototyping.
Don’t optimize prematurely!
Now, with the month numbers in place, we’ll enter a simple MOD formula.
Now we have 60 at every 3rd month and a zero in between.
We use an absolute reference for B4 so we can copy the formula across without it changing.
How can we do it?
There are two ways we can deal with this.
One method uses the COLUMN function, and one uses the MONTH function.
(See how knowingmore Excel functionscan be useful?)
When you don’t give COLUMN a reference, it returns the column number of thecurrent cell.
The first number starts with 2, not 1, because the first formula sits in the second column.
So this throws off our repeating values.
Now COLUMN generates the month numbers we need, and we can update our formula.
We only have 12 months isn’t there some way to use the month numbers directly?
Yes, in fact, there is.
With a little tweak, we can build a more logical formula by using actual dates.
with full dates like 1/1/2016, 2/1/2016, 3/1/2016, etc.
At first, this looks silly people don’t want to see actual dates for months.
Here’s a list of examples you could use for inspiration.
