Build random numbers in column A

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 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

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
Joined
Dec 30, 2008
Messages
12,602
Office Version
  1. 365
Platform
  1. Windows
Have you tried using the RANDBETWEEN function?
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
Hi Jason, yes, that will work great. Thank you for your time.
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,602
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
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:
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,602
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
No, unfortunately I'm still on 2016
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,602
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
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.

:)
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top