Max of Every Nth Column: Formula & Examples

This calculator finds the maximum value in every Nth column of a data range, such as the highest value in every 3rd column. You enter the data range, the column interval (N), and optionally a row filter, and it returns the max for each selected column group, plus the overall maximum. The calculation uses a simple array formula that checks each column position and extracts the maximum from the columns that match your interval.

Formula

The core formula to get the maximum of every Nth column is an array formula:

=MAX(IF(MOD(COLUMN(range)-COLUMN(range_first)+1, N)=0, range))

In words: for each cell in the range, check if its column position (relative to the first column of the range) is a multiple of N. If yes, include that cell in the max calculation; otherwise ignore it. The COLUMN() function returns the absolute column number, so we subtract the first column’s number to get a relative position starting at 1. The MOD() function tests divisibility by N.

Variables:

• range – the data range you want to search (e.g., A1:Z10).
• N – the interval; every Nth column (e.g., 3 means columns 3, 6, 9, …).
• COLUMN(range) – returns an array of column numbers for each cell in the range.
• COLUMN(range_first) – the column number of the first cell in the range.

In Excel and Google Sheets, you must enter this as an array formula (Ctrl+Shift+Enter in classic Excel, or just Enter in Excel 365 and Google Sheets).

Worked Example

Suppose you have a small dataset in cells A1:C3:

• A1=10, B1=20, C1=30
• A2=15, B2=25, C2=35
• A3=5, B3=40, C3=45

You want the max of every 3rd column, so N=3. The relative column positions are 1,2,3 for columns A,B,C. Only column C (position 3) is a multiple of 3. The formula checks each cell: it includes C1=30, C2=35, C3=45, and ignores all others. The maximum of those is 45. The calculator returns 45 as the max for every 3rd column, and the overall max of the whole range is 45 (since 45 is also the largest value). If you had a larger range, say A1:Z10, the formula would look at columns C, F, I, L, O, R, U, X (positions 3,6,9,…) and return the highest value among those columns.

When to Use This Formula

You would use this when your data is organized in repeating blocks of columns, and you need to compare only the values in a specific column of each block. Common scenarios include:

• Monthly or quarterly data where each period has several columns (e.g., plan, actual, variance) and you want the max of the ‘actual’ column across all periods.
• Survey results where each respondent has multiple answer columns and you need the max of the first answer column across all respondents.
• Financial models where each scenario occupies a set of columns and you want the worst-case (max) value from a specific scenario column.

The formula works for any range size and any N, as long as N is a positive integer. It ignores text and empty cells automatically because MAX() only considers numeric values.

How to Choose N and the Range

Choosing N and the range correctly is critical. N is the number of columns in each repeating group. For example, if your data has three columns per month (plan, actual, variance), then N=3. The range should cover all the columns you want to consider, but it does not need to start at the first column of a group – the formula uses the first column of the range as position 1. So if you want to start counting from a specific column, set the range’s first column to that column.

For instance, if your data starts in column B and you want every 2nd column starting from B, set the range to B1:Z10 and N=2. The formula will treat B as position 1, C as 2, D as 3, etc., and pick columns B, D, F, … (positions 1,3,5…). If you instead set the range to A1:Z10, it would pick columns C, E, G, … because A is position 1. Always align the range’s first column with where you want the count to begin.

Common Mistakes and Limitations

• Forgetting to enter as an array formula – In older Excel versions, if you don’t press Ctrl+Shift+Enter, you’ll get a #VALUE! error or a single wrong result. In Excel 365 and Google Sheets, dynamic arrays handle it automatically.
• Using absolute column numbers incorrectly – If you use MOD(COLUMN(range), N)=0 without subtracting the first column, the formula only works when the range starts at column A. For any other start, the pattern shifts.
• N=0 or negative – N must be a positive integer. If N=0, MOD returns #DIV/0!.
• Non-numeric data – MAX ignores text, but if you have errors (like #N/A), they will propagate. Use IFERROR to handle them.
• Large ranges – Array formulas over huge ranges (e.g., A1:XFD1048576) can slow down your sheet. Limit the range to the actual data area.

The formula also only works on a single contiguous range. If your data is split across multiple ranges, you’ll need to apply the formula to each range separately or combine them.

Alternatives and Variations

If you don’t want to use an array formula, you can use a SUMPRODUCT-based approach, but it’s less straightforward. Another alternative is to use a helper column that flags which columns to include, then use MAXIFS (in Excel 365) or a pivot table. For Google Sheets, you can use QUERY with a filter, but that requires a different structure.

Variations of the formula include:

• Min of every Nth column – replace MAX with MIN.
• Sum of every Nth column – replace MAX with SUM.
• Average of every Nth column – replace MAX with AVERAGE, but note that AVERAGE ignores text and empty cells.
• Max of every Nth row – use ROW() instead of COLUMN().

For example, to get the max of every 2nd row in A1:A10, use =MAX(IF(MOD(ROW(A1:A10)-ROW(A1)+1,2)=0, A1:A10)).

Frequently Asked Questions

Does this work in Google Sheets?

Yes. In Google Sheets, you don’t need to press Ctrl+Shift+Enter; just type the formula and press Enter. The array formula works the same way. For example, =MAX(IF(MOD(COLUMN(A1:Z10)-COLUMN(A1)+1,3)=0, A1:Z10)) will give the max of every 3rd column.

What if my range doesn’t start at column A?

That’s fine. The formula uses the first column of the range as position 1. So if your range is B1:Z10, the formula treats B as column 1, C as 2, etc. Just make sure you subtract the first column’s number correctly.

Can I use this to get the max of every Nth row?

Yes, replace COLUMN with ROW. For example, =MAX(IF(MOD(ROW(A1:A100)-ROW(A1)+1,5)=0, A1:A100)) gives the max of every 5th row in column A.

Why do I get a #VALUE! error?

This usually happens when you don’t enter the formula as an array formula in older Excel. Press Ctrl+Shift+Enter after typing it. Also check that N is a positive number and that the range doesn’t contain errors.

How do I handle blank cells or text?

MAX automatically ignores blank cells and text. If you have errors, wrap the range in IFERROR, like =MAX(IF(MOD(COLUMN(range)-COLUMN(first)+1,N)=0, IFERROR(range,0))).

Can I use this with a condition, like only rows where a certain column equals a value?

Yes, you can add an additional IF condition. For example, to get the max of every 3rd column only for rows where column A is “Yes”, use =MAX(IF((MOD(COLUMN(B1:Z10)-COLUMN(B1)+1,3)=0)*(A1:A10="Yes"), B1:Z10)). This is an array formula, so enter it with Ctrl+Shift+Enter in older Excel.

Related

• How to Scan for Rootkits with a Bootable Rescue Disk
• Mortal Shell 2: 8 Tips for Your First Playthrough
• Siri: How to Show Calendar Meetings

in range | Data range (e.g., A1:Z10) | - | A1:Z10 | 0
in n | Column interval (N) | - | 3 | 1
out max_each | Max of every Nth column | - | MAX(IF(COLUMN(range)=N, range)) | 2
out overall | Overall max | - | MAX(range) | 2