Dummy Data with Dependency

[Mikael]

Hi

There are some webpages that do random dummy data to test with, but I need the data to be related/depend on each other, for instance if I have two fields a start and stop date, it must make sense relative to each other.

Does anyone knows a tool where I can define an interval and the relations?

Or perhaps someone have an idea how generate it in Excel?

 

[Steve Prevette]

Hi

There are some webpages that do random dummy data to test with, but I need the data to be related/depend on each other, for instance if I have two fields a start and stop date, it must make sense relative to each other.

Does anyone knows a tool where I can define an interval and the relations?

Or perhaps someone have an idea how generate it in Excel?

You are basically entering the arena of Monte Carlo simulations, which can be easily done in Excel. There is a rand() function in Excel which will give you a uniformly distributed number between 0 and 1. You then run that through the inverse of the distribuiton you want. Excel has several inverse distributiones including that for Normal.

If you wanted to do the start date stop date example, you could randomly generate start dates (probably sticking to a uniform distribution) then add to that some Normally distributed random variable for the cycle time.

You can also simulate serially-dependent data by generating the next random number from some combination of the previous value, and the new random number.

 

[Miner]

To expand on Steve's comment, you can add RAND() into any distribution function that uses a probability term.

For example: NORM.INV(probability, mean, standard_dev). Substitute RAND() for probability, and you have NORM.INV(RAND(), mean, standard_dev). This will give you random data from a normal distribution.

LOGNORM.INV(RAND(), mean, standard_dev) will give you random data from a lognormal distribution, which is commonly used for time data.

 

[Mikael]

Thx you both , though I am not sure how to use the random() correct.


I created 2x date field and time, so I need to add the random minutes, this works:
=SUM(D14+TIME(0;(RANDBETWEEN(1;59));0))

But if I try to add:
LOGNORM.INV(RANDbetween(), mean, standard_dev)

I cannot make it work, how important is that?

 

[Steve Prevette]

Thx you both , though I am not sure how to use the random() correct.


I created 2x date field and time, so I need to add the random minutes, this works:
=SUM(D14+TIME(0;(RANDBETWEEN(1;59));0))

But if I try to add:
LOGNORM.INV(RANDbetween(), mean, standard_dev)

I cannot make it work, how important is that?

Try this: =LOGNORM.INV(RAND(),1,2)

When you use the inverses, the random number must be uniform between zero and 1, which is what RAND() gives you. Then you can apply the mean and standard deviation you want to the Lognorm.inv.

 

[Mikael]

Thx, that works and of course it does not makes sense to define the interval there, but can I then define that it has to be minutes from 1 to 59 or sometimes hours 0 -23 ? Maybe it is a stupid question because I don't understand what it does, can you help me a little on why I need lognorm.inv?

 

[Steve Prevette]

The Log Normal distribution is frequently used to model times because it has the beneficial property of not generating negative numbers. Basically looks like the bell curve (the "Normal Distribution") except it terminates at zero rather than running into negative numbers. There are other distributions with this property, but the log normal is the simplest (mathematically) to use.

Excel stores dates and times as numbers, starting with January 1, 1900. So you really don't need to worry about generating 0 to 23 for hours and 0 to 59 for minutes. Generate a random number, then format the cell to a date/time or just time format. It may take some trial and error to get the correct ranges to use, but is probably the easiest way to go. Plus, if you want to add the cycle time to the start date to get a finish date, you need to go this route.