## Create a repeating list of numbers from 1 to Nth value

**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 file**create-a-numbered-list.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**IF(**logical_test;[value_if:true];[value_if_false]**)
**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

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]

Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]

Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]

The quotient function returns the integer portion of a division. Example, 5/2 = 2.5. The integer is 2. Excel Function […]

Rearrange cells in a cell range to vertically distributed values

The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct […]

Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Color even months Conditional formatting formula: =NOT(MOD(MONTH($B6),2)) Color odd years Conditional formatting formula: […]

### 2 Responses to “Create a repeating list of numbers from 1 to Nth value”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

wow! thanks!! awesome! perfect! no joke that just saved me hours...

Andrew,

This is an old post, there is an easier way.

1. Enter 1,2,3,4 in cell A1:A4.

2. Select A1:A4

3. Click and hold black dot in A4 with right mouse button

4. Drag down as far as needed

5. Release button

5. Click "Copy Cells"