Quite often I am asked to create dashboards without having the actual data. When I am presented with this dilemma, I will create dummy data to use in lieu of the real data.
To generate numeric and date data, I often will use the =RANDBETWEEN formula.
The different between Excel’s RAND formula and RANDBETWEEN is that you can determine the start and end points for the random number to be generated.
=RANDBETWEEN(Starting Number, Ending Number)
RANDOM NUMBERS
In this example I use the RANDBETWEEN formula to generate numbers between 100 and 500.
Formula | Results |
=RANDBETWEEN(100,500) | 350 |
=RANDBETWEEN(100,500) | 217 |
=RANDBETWEEN(100,500) | 491 |
=RANDBETWEEN(100,500) | 183 |
RANDOM DATES
The RANDBETWEEN formula only returns integers so you can’t directly generate dates. However you can add a random number generated with this formula to a date column to generate a date. In this example I generate a random number and add that to the start data of my criteria (1/1/2012). The random number increments the date by the number of days that the random number represents.
Formula for Random Number | Random Number | Starting Date | Random Date | Random Date Formula |
=RANDBETWEEN(100,365) | 114 | 1/1/2012 | 4/24/2012 | =C2+B2 |
=RANDBETWEEN(100,365) | 268 | 1/1/2012 | 9/25/2012 | =C3+B3 |
=RANDBETWEEN(100,365) | 328 | 1/1/2012 | 11/24/2012 | =C4+B4 |
=RANDBETWEEN(100,365) | 142 | 1/1/2012 | 5/22/2012 | =C5+B5 |
You can also use the following formula to pick a random date keyed off of the current date. In this example I am looking for a day within 30 days of the present date:
=TODAY() + RANDBETWEEN(0,30)
=TODAY() - RANDBETWEEN(0,30)
Using the same logic, you can select a date in or within the last year you could use the following formula:
=TODAY() + RANDBETWEEN(0,365)
=TODAY() - RANDBETWEEN(0,365)
RANDOM LETTERS
So how do we generate letters using the RANDBETWEEN formula? Return the Character specified by the code number from the character set on your computer.
=CHAR(RANDBETWEEN(65,90)) - returns one uppercase letter
=CHAR(RANDBETWEEN(97,122)) - returns one lowercase letter
=CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) - returns two uppercase letters
FINAL THOUGHTS
I would also like to point out that the number generated is not truly random but for most cases, can be used for generating generic data.
So how can you use the formula =RANDBETWEEN to help you complete your job?
No comments:
Post a Comment