Author: Oscar Cronquist Article last updated on July 25, 2017

Question: How do I create a repeating list of numbers from 1 to Nth value? In my case from 1 to 4 repeating?

Answer: Nth value in D1.

Formula in A1: =(IF(MOD((ROW()-ROW(List_start)+1)/$D$1, 1)=0, 1, MOD((ROW()-ROW(List_start)+1)/$D$1, 1)))*$D$1 Copy formula down to A20.


Named ranges
List_start A1
What is named ranges?

Type a  value in D4 and watch the list automatically change.

How to customize this formula to your workbook
Change the named range to the starting reference of your list

Download excel example filecreate-a-numbered-list.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

MOD(number, divisor) returns the remainder after a number is divided by a divisor