Excel Magic Trick 1145

Create Random Data Set with Formulas: 4 Unusual Formulas

DESCRIPTION

See how to:
(00:08) Overview of Different data Types
(01:02) FORMULA 1: Create Random Serial Number Dates with RANDBETWEEN and actual Serial Numbers
(02:10) Keyboards to Copy Formulas down 2000 rows quickly: Ctrl + C, F5, Type address, Shift + Enter, Ctrl + V
(03:30) FORMULA 2: Create ISO Dates from Serial Numbers using the functions: YEAR, TEXT, MONTH, DAY. Also see Custom Number Format to show Leading Zeros.
(05:46) Paste Special Values with Right-Click Mouse Trick
(06:18) FORMULA 3: Formula to create random Numbers From Two Different Distributions (Uniform and Normal) using the functions: CHOOSE, RANDBETWEEN, ROUND, NORM.INV and RAND.
(10:18) 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.
(13:21) Screen Tip for Negative Dates / Times
(13:48) Add Text Number Format to ISO Dates.

WORKBOOKS