Build random numbers in column A

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,360
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi Jason, yes, that will work great. Thank you for your time.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Hi Jason,

Sorry about that.

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.:confused:
 
Upvote 0
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).
 
Upvote 0
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)))
 
Upvote 0
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.

:)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top