In the example shown, generate a value of 60 every 3 months. The formula in B8 is: Which returns 60 every 3rd month and zero for other months. In this case, the number is created with the COLUMN function, which return the column number of cell B8, the number 2, minus 1, which is supplied as an “offset”. We use an offset, because we want to make sure we start counting at 1, regardless of the actual column number. The divisor is hardcoded as 3, since we want to do something every 3rd month. By testing for a zero remainder, this expression will return TRUE at the 3rd, 6th, 9th, and 12th months: Finally, IF simply evaluates the MOD expression and returns value in B5 (coded as an absolute reference to prevent changes as the formula is copied) when TRUE and zero when FALSE.
Working with a date
If you need to repeat a value every n months, and you are working directly with dates, see this example.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
