In this tutorial we are going to write a formula that allows us to increase the dates in a column by one every time a block of unique values repeats.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a list of three unique items that repeats five times:
I want to enter a date in cell A1, and using a formula, copy it down so that every time the block of three unique values repeats, I want the date in column A to increase by a value of one, like this:
I am going to enter the date 1/1/2017 in cell A1, and the formula I am going to enter into cell A2 and copy down is:
The MOD function has two arguments in its syntax – number and divisor. The result of a MOD function is the remainder when the number is divided by the divisor. So in the formula above, the IF statement says IF ROW(A1) (which will return a 1) divided by three (the number of unique items in the list) has a remainder of zero, then take the date above and add one to it. IF not, just give me the date above.
Now, what if you don’t want to hard code the number of unique values into the formula, but want another formula to calculate the number of unique values in the list? The formula for that would be:
Here is how that works. First, when you take the COUNTIF function for the list of items as both the criteria and range, it will return the number of times each value is in the list. In this case, each of the three items appears 5 times.
Now, take 1 divided by 5 and you get 0.2. SUMPRODUCT now wants to add those fifteen instances of 0.2 together. 0.2 times 15 = 3, which is the number of unique values in our list.
So, if you substitute the SUMPRODUCT formula for the hard coded 3 in our original formula, you get this:
Take a few minutes and watch the video to see two examples of how this formula works. I think you will find it useful in some areas of your work!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!