VBA/ Excel Query on Random Numbers and dates

dipkchopra

New Member
Joined
Nov 19, 2013
Messages
10
I was stuck with two problems involving generating random values and was wondering if anybody on this forum can help me with a solution

Problem 1:
I am trying to generate random set of dates between two dates. Say between 01-Jan-2020 and 31-Dec-2021 I wish to generate 15 random dates.
I used Randbetween formula and dragged it down 15 rows and did get my random set of 15 dates.
My problems:
  • I need the dates to be spread evenly across the year. Random I noticed although random is not even
  • Dates cannot repeat
  • Can there be a spill function solution where in I key formula in just one cell to populate the random dates. The number of dates I generate is dynamic.
Problem 2:
I need to generate random set of numbers between two values.
Say I have to generate 15 random numbers between 1000 and 1500. My challenge is
  • Each number generated should be in multiples of 15
  • Total of all 15 Random numbers should be sum x - say 19000 (or its closest multiple of 15)
A VBA solution to the above problem is also welcome.
Thank is advance
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
Random I noticed although random is not even
These two concepts are mutually exclusive.
If you want dates spread evenly then clearly they are not random.
If you want random dates then you have no control over the 'evenness' of their spread.
 

Forum statistics

Threads
1,136,799
Messages
5,677,807
Members
419,721
Latest member
StuckInWork

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