Fixed value every N columns

To generate a fixed value every N columns (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.

April 14, 2025 · 1 min · 85 words · Steven Hart

Table of Contents

Explanation

The core of this formula is the MOD function.

The divisor is hardcoded as 3, since we want to do something every 3rd month.

For example, MOD(10,3) = 1.

Excel formula: Repeat fixed value every 3 months

The result of MOD carries the same sign as the divisor.

COLUMN Function

The Excel COLUMN function returns the column number for a reference.

For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet.

Excel formula: Highlight every other row

When no reference is provided, COLUMN returns the column number of the cell which contains the formula.

Excel formula: Sum every nth column

Excel formula: Copy value from every nth row

Excel MOD function

Excel COLUMN function