Fixed value every N months

Fixed value every N months

Generic formula 

=IF(MOD(COLUMN(A1)-offset,N)=0,amount,0)

Related formulas 

Highlight every other row

Sum every nth column

Explanation

To generate a fixed value every N months (for example, a fixed expense every 3 months, a fixed payment every 6 months, etc.) you can use a formula based on the MOD function.

In the example shown, generate a value of 60 every 3 months. The formula in B8 is:

=IF(MOD(COLUMN(B8)-1,3)=0,$B$5,0)

Which returns 60 every 3rd month and zero for other months.

How this formula works

The core of this formula is the MOD function. MOD takes a number and pisor, and returns the remainder after pision, which makes it useful for formulas that need to do something every nth time.

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 pisor 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:

MOD(COLUMN(B8)-1,3)=0

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 use a date in row 7 instead of a text value you can build a more logical formula. Just adapt the formula to extract the month number and use that instead of the column number:

=IF(MOD(MONTH(B7),3)=0,$B$5,0)

You can still display "Jan", "Feb", etc. by using the custom date format "ddd". 

Fixed value every N months

Generic formula 

=IF(MOD(COLUMN(A1)-offset,N)=0,amount,0)

Related formulas 

Highlight every other row

Sum every nth column

Explanation

To generate a fixed value every N months (for example, a fixed expense every 3 months, a fixed payment every 6 months, etc.) you can use a formula based on the MOD function.

In the example shown, generate a value of 60 every 3 months. The formula in B8 is:

=IF(MOD(COLUMN(B8)-1,3)=0,$B$5,0)

Which returns 60 every 3rd month and zero for other months.

How this formula works

The core of this formula is the MOD function. MOD takes a number and pisor, and returns the remainder after pision, which makes it useful for formulas that need to do something every nth time.

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 pisor 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:

MOD(COLUMN(B8)-1,3)=0

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 use a date in row 7 instead of a text value you can build a more logical formula. Just adapt the formula to extract the month number and use that instead of the column number:

=IF(MOD(MONTH(B7),3)=0,$B$5,0)

You can still display "Jan", "Feb", etc. by using the custom date format "ddd".