where data is the named range D5:D16. With n hardcoded into the formula as 3, FILTER function returns every 3rd row in the data. Working from the inside out, the first step is to generate a set of row numbers. This is done with the SEQUENCE function like this: The ROW function returns the count of rows in the named range data. Using the count of rows, SEQUENCE returns an array of 12 numbers in sequence: This array is returned directly to the MOD function as the number argument, with the number 3 hardcoded as the divisor. MOD is set up to test if row numbers are divisible by 3 with a remainder of zero The result from MOD is an array or TRUE and FALSE values like this: Note TRUE values correspond with every 3rd row in the data. This array is delivered directly to the FILTER function as the include argument. FILTER returns every 3rd row in data as a final result.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
