# Build random numbers in column A

#### FryGirl

##### Well-known Member
Hello all,

In column A starting with A2, I would like to fill down with 1, 2, 3, 4, 5, 6, etc., but I would like to get random numbers between 3 and 8.

So, for example, see below. I will then fill in column B with dates. In the end, I will drag this down as far as today's date.

Budget.xlsm
AB
1NumbersDate
211-Jan-20
311-Jan-20
411-Jan-20
511-Jan-20
611-Jan-20
722-Jan-20
822-Jan-20
922-Jan-20
1022-Jan-20
1122-Jan-20
1222-Jan-20
1322-Jan-20
1422-Jan-20
1533-Jan-20
1633-Jan-20
1733-Jan-20
1844-Jan-20
1944-Jan-20
2044-Jan-20
2144-Jan-20
Sheet4
Cell Formulas
RangeFormula
B7:B21B7=IF(A6<>A7,MAX(\$B\$6:B6)+1,B6)

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### jasonb75

##### Well-known Member
Have you tried using the RANDBETWEEN function?

#### FryGirl

##### Well-known Member
Hi Jason, yes, that will work great. Thank you for your time.

#### FryGirl

##### Well-known Member
Hi Jason, at first I thought that might work, but not seeing how RANDBETWEEN is going to make a consecutive list, 1, 2, 3, 4, 5, 6, etc., while keeping those random between 3 and 8?

Can you explain a little further what you were thinking?

#### jasonb75

##### Well-known Member

make a consecutive list
That was an important detail not included in your original post, I had suspected that there would be more to it than first suggested.
consecutive list, 1, 2, 3, 4, 5, 6, etc., while keeping those random between 3 and 8?
Could you explain why 1 and 2 are in a list of numbers that should be between 3 and 8? Is that an error in the post, or is there another detail that I'm not seeing?

From what you are now saying, it seems that you don't want random numbers between 3 and 8 (as you asked for) but rather a random number of rows to be filled with all numbers from 3 to 8.

Often the answers we provide are only as good as the questions you ask, errors in the question (even simple ones) often result in errors in the answers.

#### FryGirl

##### Well-known Member
Hi Jason,

I would like, starting with 1, list numbers down column A (1, 2, 3, 4, 5, 6, …365), but each one of those numbers (1, 2, 3, 4, 5, 6, …365) will only be listed randomly between 3 to 8 times.

I added the 365 in the list to say, I'm going to use 365 days worth. If everything was equal at 8 rows, then I would end up with 2,920 rows. Hopefully this is not as clear a mud.

#### jasonb75

##### Well-known Member

Makes sense now. I think you might want 2928 rows though (allowing for leap years).

Are you still using excel 2016? It can be done with 2016, but would be much easier if you have the SEQUENCE function (requires an up to date version of office 365).

#### FryGirl

##### Well-known Member
No, unfortunately I'm still on 2016

#### jasonb75

##### Well-known Member
I've tried to make this as compact as possible while still meeting your requirement. To do that, I've removed the number list in column A and used a separate column to generate a running total or randomised values. This is a bit of a messy set up, but unfortunately there is no easy way to do what you asked for (except perhaps with vba). Hopefully it will make some sense.

One of the problems with RANDBETWEEN is that it is a volatile function, this means that the random numbers produced will change every time you do something in excel while this sheet is open. In order to keep the random values the same once they have been calculated, it is necessary to use a circular reference. You will need to change an excel setting before you are able to use this.

To do this, you need to go to File > Options > Formulas, then tick the box that says 'Enable iterative calculation' ***Do this before entering the formulas***

I've only shown the first 25 rows of my test sheet. The formula in column A needs to be filled down to A2929 (absolute maximum number of rows that will be needed in the unlikely event that all 366 days of a leap year are randomised to 8 rows).

The formula in column C needs to be filled down to C367 (366 formulas plus heading).

While D2 is empty, the random values can be recalculated by pressing the f9 key. Once a start date is entered into D2, the current random values will be locked into place and the dates filled into column A.

If you look at the randomised values, you will see that they are not between 3 and 8 as you originally asked, but the difference between each value and the previous one is between 3 and 8 (the first value is always 1) . The values set the row where the date increases rather than the number of rows for each date.

Cell Formulas
RangeFormula
A2:A25A2=IF(ROWS(A\$2:A2)>MAX(\$C\$2:\$C\$367),"",\$D\$2-1+MATCH(ROWS(A\$2:A2),\$C\$2:\$C\$367))
C2:C25C2=IF(ROWS(C\$1:C1)=1,1,IF(ROWS(C\$1:C1)>365+(DAY(DATE(YEAR(\$D\$2),2,29))=29),"",IF(AND(C2>N(C1),\$D\$2<>""),C2,RANDBETWEEN(3,8)+C1)))

#### FryGirl

##### Well-known Member
Hi Jason,

Not sure how you guys do this, but this will work great. I especially like the fact I can put a start date in D2 and change the range of dates if required. Thanks again for your generous time. VBA would have been a fine fix also, but this surely works.

Replies
3
Views
174
Replies
11
Views
285
Replies
4
Views
157
Replies
9
Views
395
Replies
3
Views
275

1,129,410
Messages
5,636,130
Members
416,900
Latest member
Mokor

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back