# Dummy Data with Dependency

#### Mikael

##### Quite Involved in Discussions
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?

Elsmar Forum Sponsor

#### Steve Prevette

##### Deming Disciple
Staff member
Super Moderator
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

##### Forum Moderator
Staff member
Admin
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

##### Quite Involved in Discussions
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?

Last edited:

#### Steve Prevette

##### Deming Disciple
Staff member
Super Moderator
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

##### Quite Involved in Discussions
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

##### Deming Disciple
Staff member
Super Moderator
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.