Excel Magic Trick 1145: Create Random Data Set with Formulas: 4 Unusual Formulas

See how to:
1) (00:08 min mark) Overview of Different data Types
2) (01:02 min mark) FORMULA 1: Create Random Serial Number Dates with RANDBETWEEN and actual Serial Numbers
3) (02:10 min mark) Keyboards to Copy Formulas down 2000 rows quickly: Ctrl + C, F5, Type address, Shift + Enter, Ctrl + V
4) (03:30 min mark) FORMULA 2: Create ISO Dates from Serial Numbers using the functions: YEAR, TEXT, MONTH, DAY. Also see Custom Number Format to show Leading Zeros.
5) (05:46 min mark) Paste Special Values with Right-Click Mouse Trick
6) (06:18 min mark) FORMULA 3: Formula to create random Numbers From Two Different Distributions (Uniform and Normal) using the functions: CHOOSE, RANDBETWEEN, ROUND, NORM.INV and RAND.
7) (10:18 min mark) FORMULA 4: Select Random Names from two separate lists that contain First and Last names. See the INDEX function and a Join Array Operation in the array argument of the INDEX function that does not require Ctrl + Shift + Enter. See the functions, INDEX and RANDBETWEEN.
8) (13:21 min mark) Screen Tip for Negative Dates / Times
9) (13:48 min mark) Add Text Number Format to ISO Dates.

[OzduSoleilDATA]

Excellent! Great methods for generating random data.

[josephgoldberger]

Thanks Mike and glad to have you back!

[krn]

Thanks Mike. Neat screen tip about the long or negative values. I have never seen that before, gotta be new with 2013.

[excelfan]

ExcelIsFun Thank you again. This video has a sense of Excel Is Fun about it. It creates this world of possibility for other fun things that can be done in excel. great work

[minshanhuang]

it's good to have you back, keep it going and thanks!

[jay-arsuarez]

i like that copy as values only. Thanks

[Alus]

Excellent idea on how to create data specially for testing purposes :)

[excelisfun]

Excel Magic Trick 1145: Create Random Data Set with Formulas: 4 Unusual Formulas
See how to:
1) (00:08 min mark) Overview of Different data Types
2) (01:02 min mark) FORMULA 1: Create Random Serial Number Dates with RANDBETWEEN and actual Serial Numbers
3) (02:10 min mark) Keyboards to Copy Formulas down 2000 rows quickly: Ctrl + C, F5, Type address, Shift + Enter, Ctrl + V
4) (03:30 min mark) FORMULA 2: Create ISO Dates from Serial Numbers using the functions: YEAR, TEXT, MONTH, DAY. Also see Custom Number Format to show Leading Zeros.
5) (05:46 min mark) Paste Special Values with Right-Click Mouse Trick
6) (06:18 min mark) FORMULA 3: Formula to create random Numbers From Two Different Distributions (Uniform and Normal) using the functions: CHOOSE, RANDBETWEEN, ROUND, NORM.INV and RAND.
7) (10:18 min mark) FORMULA 4: Select Random Names from two separate lists that contain First and Last names. See the INDEX function and a Join Array Operation in the array argument of the INDEX function that does not require Ctrl + Shift + Enter. See the functions, INDEX and RANDBETWEEN.
8) (13:21 min mark) Screen Tip for Negative Dates / Times
9) (13:48 min mark) Add Text Number Format to ISO Dates.

[BillSzysz]

Thanks for many tricks :-)))
You can use date directly inside RANDBETWEEN, but as text. I mean something like this (date format in my country)

=RANDBETWEEN("2013-01-01", "2014-12-31").

[Poprasstriga]

Great as usual.
But why don't use a simple =TEXT(A5;"YYYYMMDD") to convert the dates in column A?

[diptisharma]

Hi,

I want to generate random time in excel with =Text(Rand(9:16666-8.3333)/24+8.3333/24, "HH:MM")

And the same formula I am applying for getting random time between 1:55 to 2:10 .
but if I am trying to calculate the working hours between these two time it's showing only

Can you please help me, how can I calculate this, why this error is showing????

[Hendmejd]

how to do random number with todays data ? 2017-07-05/22258 this is example.