Fixed Value Every N Columns? Formula & Examples
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This calculator works out a fixed value that repeats every N columns in a spreadsheet or table, such as a repeating label, cost, or coefficient. You enter the column position you are interested in, the cycle length N, and the offset (which column the first fixed value starts on), and you get back the position of that value within the cycle and the actual fixed value from your list. The result is given as a column number and a value, so you can use it directly in formulas like INDEX or MOD.
Formula
The core formula is a modular arithmetic expression:
Position in cycle = ((column – offset) mod N) + 1
Where:
- column = the column number you are evaluating (e.g., 7 for column G).
- offset = the column number where the first cycle starts (often 1, but can be 0 or any other start).
- N = the cycle length, i.e., after how many columns the pattern repeats.
- mod = the modulo operation, which returns the remainder after division.
The result is a number from 1 to N. If you have a list of fixed values, you can pick the value at that position (e.g., using INDEX in Excel). If you just need the repeating pattern of numbers, the position itself is your answer.
Worked Example
Using the default values from the calculator above:
- Column = 7
- N = 3
- Offset = 1
- Fixed values = 100, 200, 300
Step 1: Subtract the offset from the column: 7 – 1 = 6.
Step 2: Apply modulo with N: 6 mod 3 = 0.
Step 3: Add 1: 0 + 1 = 1.
So the position in the cycle is 1, which corresponds to the first fixed value: 100.
The calculator outputs: position = 1, value = 100. This matches the formula result.
When to Use This Pattern
Repeating values every N columns appear in many real-world tables:
- Financial models with quarterly or monthly columns, where a fixed annual rate repeats every 12 columns.
- Project plans with 5-day work weeks, where a status label repeats every 5 columns.
- Data entry sheets where a category label repeats every 3 columns (e.g., product, price, stock).
- Calendar layouts where weekday names repeat every 7 columns.
Instead of typing the same value many times, you can generate it with a formula. This also makes the table easier to update: change the list of fixed values once, and all columns update.
How to Implement in Spreadsheets
In Excel, Google Sheets, or LibreOffice Calc, you can use the MOD function directly:
=INDEX(list, MOD(column – offset, N) + 1)
For example, if your fixed values are in cells A1:A3 (100, 200, 300), and you want to fill columns C through H (columns 3 to 8) with a repeating pattern starting at column C, you would write in cell C1:
=INDEX($A$1:$A$3, MOD(COLUMN()-3, 3)+1)
Then drag the formula to the right. The COLUMN() function returns the current column number, and the offset 3 is subtracted to align the start.
If you don’t have a list, you can generate the repeating number directly: =MOD(COLUMN()-offset, N)+1.
Common Mistakes and Pitfalls
- Off-by-one errors: Forgetting to add 1 after MOD gives you positions 0 to N-1 instead of 1 to N. This is the most frequent mistake.
- Wrong offset: If your pattern starts at column 1, offset is 1. If it starts at column 0 (like in some programming languages), offset is 0. Mixing these shifts the pattern.
- Modulo with negative numbers: In some programming languages, MOD of a negative number returns a negative result. In spreadsheets, MOD always returns a non-negative result, but in languages like Python or JavaScript, you need to handle negatives manually (e.g.,
((a % n) + n) % n). - Non-integer N: N must be a positive integer. If you use a decimal, the pattern will not repeat cleanly.
Limitations and Variations
The formula assumes the pattern repeats exactly every N columns without gaps. If your table has skipped columns (e.g., merged cells or blank columns), you need to adjust the column reference or use a different approach.
For more complex patterns, like repeating blocks of different lengths (e.g., 2 columns of type A, then 3 columns of type B), you would need a more advanced formula, often using nested IFs or lookup tables.
Also, this formula works for columns, but the same logic applies to rows. Just replace COLUMN() with ROW() in spreadsheet formulas.
Frequently Asked Questions
What does MOD mean in this context?
MOD is the modulo operation, which gives the remainder of a division. For example, 7 mod 3 = 1 because 7 divided by 3 is 2 with remainder 1. In the repeating pattern formula, it tells you where you are within the cycle.
How do I get a repeating value instead of a number?
Use the INDEX function with your list of values. The position from the MOD formula becomes the index number. In Excel: =INDEX($A$1:$A$3, MOD(COLUMN()-1, 3)+1).
What if my pattern starts at column 0?
Set the offset to 0 in the formula. For example, if your first column is A (column 1) but you want the pattern to start at column 0 conceptually, you would subtract 1 more. The offset is simply the column number of the first occurrence of the first value.
Can I use this for rows instead of columns?
Yes, the same formula works for rows. Replace COLUMN() with ROW() in spreadsheet formulas, and use the row number instead of the column number.
What if I have a non-integer N?
N must be a whole number for a repeating pattern. If you need a pattern that repeats every 2.5 columns, that’s not a simple repeating cycle; you would need a different approach, such as a lookup table.
Why does my formula return #VALUE! in Excel?
This usually happens when the list of fixed values is not the same size as N, or when the list contains text instead of numbers. Make sure your list has exactly N items and that they are all numbers (or all text) if you’re using INDEX.
Related
Formula used
in col | Column number | - | 7 | 1 in n | Cycle length (N) | - | 3 | 1 in offset | First column of cycle | - | 1 | 1 in vals | Fixed values (comma separated) | - | 100, 200, 300 | 0 out pos | Position in cycle | - | ((col - offset) mod n) + 1 | 0 out val | Fixed value | - | 0 | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.