Explanation
In this example, the goal is to calculate the maximum value of every “nth” column in a row of data, where n is a variable entered in the named range M2 . This problem can be solved in several ways, as explained below. The explanation below also includes a formula that will work in older versions of Excel, before dynamic array formulas were introduced.
FILTER + MOD + SEQUENCE
A classic solution to “nth” problems is to use the MOD function to check a numeric array for a remainder of zero after dividing by n . This is the approach in the worksheet shown, there the formula in cell O5, copied down, is:
=MAX(FILTER(B5:M5,MOD(SEQUENCE(,COLUMNS(B5:M5)),n)=0))
At a high level, the FILTER function is configured to retrieve only the nth values of interest using logic created with MOD and SEQUENCE. The result from FILTER is returned to MAX, which returns the maximum value as a final result. Working from the inside out, the SEQUENCE function is configured to generate a numeric array like this:
SEQUENCE(,COLUMNS(B5:M5))
Because there are 12 columns in the range B5:M5, SEQUENCE returns an 1 x 12 array like this:
{1,2,3,4,5,6,7,8,9,10,11,12}
This array is returned to the MOD function as the number argument, with n provided as the divisor. The result from MOD is then compared to zero:
MOD({1,2,3,4,5,6,7,8,9,10,11,12},n)=0
With the number 3 in cell M2 for n , MOD returns an array like this:
{1,2,0,1,2,0,1,2,0,1,2,0}=0
Note that every 3rd value is zero. When this array is compared to zero, the result is an array of TRUE and FALSE values like this:
{FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE}
Notice that every 3rd value is now TRUE. This array is returned directly to the FILTER function as the include argument. With array given as B5:M5, FILTER returns every 3rd value in B5:M5 directly to MAX like this:
=MAX({17,1,8,10})
The MAX function then returns 17 as a final result.
CHOOSECOLS + SEQUENCE
An alternative approach is to use the CHOOSECOLS function with the SEQUENCE function like this:
=MAX(CHOOSECOLS(B5:M5,SEQUENCE(,COLUMNS(B5:M5)/n,n,n)))
This formula calculates the numeric indices of target nth values directly, then requests just those values with the CHOOSECOLS function. The SEQUENCE function is configured to return the numeric indexes of every nth value like this:
SEQUENCE(,COLUMNS(B5:M5)/n,n,n)))
The rows argument is left empty. The columns argument is calculated by dividing the number of columns by n . Both start and step are assigned the value n . With 12 columns in B5:M5, and the number 3 for n , this simplifies to:
SEQUENCE(,4,3,3))
The result is a numeric array of “target nth values” like this:
{3,6,9,12}
This array is returned directly to the CHOOSECOLS function as the second argument:
=MAX(CHOOSECOLS(B5:M5,{3,6,9,12}))
The CHOOSECOLS function then returns the values at these locations in an array to MAX:
=MAX({17,1,8,10})
The MAX function returns 17 as before.
All in one formulas
If desired, you can adapt the formulas above to single, all-in-one formulas based on the BYROW function like this:
=BYROW(B5:M16,LAMBDA(r,MAX(FILTER(r,MOD(SEQUENCE(,COLUMNS(r)),n)=0))))
=BYROW(B5:M16,LAMBDA(r,MAX(CHOOSECOLS(r,SEQUENCE(,COLUMNS(r)/n,n,n)))))
The first formula uses the SEQUENCE and MOD approach, the second formula uses the SEQUENCE and CHOOSECOLS approach. Both formulas will return the max values for nth columns for all rows at once.
Legacy Excel
In older versions of Excel without dynamic array formulas like FILTER and SEQUENCE, you can use an array formula like this:
{=MAX(IF(MOD(COLUMN(B5:M5)-COLUMN(B5)+1,n)=0,B5:M5))}
This is an array formula and must be entered with control + shift + enter in older versions of Excel.
At a high level, this formula uses the IF Function together with the MOD and COLUMN functions to “cancel out” values not in nth columns, then runs MAX on the result. The key is this snippet:
MOD(COLUMN(B5:M5)-COLUMN(B5)+1,n)
The COLUMN function is used to get a set of “relative” column numbers as explained in detail here . The result is a numeric array that starts with the number 1:
{1,2,3,4,5,6,7,8,9,10,11,12}
This array goes into the MOD function as the number argument:
MOD({1,2,3,4,5,6,7,8,9,10,11,12},n)=0
where n is the value to use for “nth”. The MOD function returns the remainder for each column number divided by n . When n = 3, MOD will return an array like this:
{1,2,0,1,2,0,1,2,0,1,2,0}
Note that zeros appear for columns 3, 6, 9 and 12, corresponding to every 3rd column. This array is compared to zero with the logical expression =0 to force a TRUE when the remainder is zero and a FALSE when not. These values go into the IF function as the logical test. The IF function then “filters” results so only values in the original range in nth columns make it into the final array. Other values become FALSE. The result is delivered to the MAX function:
=MAX({FALSE,FALSE,17,FALSE,FALSE,1,FALSE,FALSE,8,FALSE,FALSE,10})
The MAX function automatically ignores FALSE values and returns 17 as a final result.
Max of every other column
To get the maximum value in every other column, you can make a small adjustment to the formula when needed. To get the maximum value in even columns, use the original formula with 2 as n :
=MAX(FILTER(data,MOD(SEQUENCE(,COLUMNS(data)),2)=0))
To get the maximum number in odd columns use 2 for n and compare the result to 1:
=MAX(FILTER(data,MOD(SEQUENCE(,COLUMNS(data)),2)=1))
In older versions of Excel, you can use these generic formulas:
{=MAX(IF(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0,rng))} // even columns
{=MAX(IF(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1,rng))} // odd columns
These are array formulas and must be entered with control + shift + enter in older versions of Excel.
Explanation
In this example, the goal is to return the maximum value in a set of data while ignoring any errors that might exist. This problem can be solved with the AGGREGATE function or with the MAXIFS function, as explained below.
MAX with errors
The standard way to retrieve the maximum value in a range of data is the MAX function . However, if we try to use MAX like this:
=MAX(B5:B16) // returns #N/A
The MAX function returns #N/A as a result because the range B5:B16 contains errors. The problem with MAX is that it will return an error if the data contains errors.
AGGREGATE function
The AGGREGATE function is a useful function that can run an aggregate calculation like AVERAGE, COUNT, MAX, MIN, etc., while optionally ignoring errors. A total of 19 operations are available, specified by a function number provided as the first argument. The table here contains a complete list of available operations. In this case we want to use the number 4 for function_num , which specifies the MAX operation. For the second argument, options , we provide the number 6, which indicates “ignore all errors”. For array , we provide the range B5:B16. The final formula looks like this:
=AGGREGATE(4,6,B5:B16)
To recap, the number 4 specifies MAX, and the number 6 is an option to ignore errors. With these settings, AGGREGATE returns the maximum value in the range, which is 100.
Alternative with MAXIFS
Another way to solve this problem is with the MAXIFS function , which can calculate a max value after applying one or more conditions to filter out unwanted values. If values in the data set are known to be positive, a simple way to ignore errors is to check for values greater than zero like this:
=MAXIFS(data,data,">0")
This works because the “greater or equal to zero” expression effectively removes error values, and MAXIFS returns the maximum value from the remaining 10 values, 100. You can also use the MAXIFS function with more specific criteria like this:
=MAXIFS(data,data,"<>#N/A") // ignore NA errors
=MAXIFS(data,data,"<>#DIV/0!") // ignore DIV/0 errors
=MAXIFS(data,data,"<>#N/A",data,"<>#DIV/0!") // ignore both
All formulas above use the not equal to ("<>") operator . The first formula calculates a maximum after excluding #N/A errors. The second formula calculates a maximum after excluding #DIV/0! errors. The last formula uses two conditions to exclude both #N/A and #DIV/0! errors.
