# =1-2*rand()

#### kikitinoq

##### New Member
In Gerard Verschuuren's book on excel monte carlo simulations, he uses the above formula to generate brownian motion. Can someone explain what this does and its signficance? Thanks.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
RAND() returns a value that is roughly between zero and 1. So 1-2*RAND() returns a value roughly between 1 and -1, uniformly distributed.

Last edited:
Pretty bad simulation. More accurate for 1D motion would be =NORM.S.INV(RAND()), which is normal rather than uniform.

shg - I'm just curious, would you care to expand on why you think a normal distribution is better for this application than a uniform distribution ?
I'm not an expert in this area at all.

I guess whatever the reason, it's important for the OP to satisfy themselves that the solution is right for their application.

Almost nothing in nature has a uniform distribution, which has hard-edged limits, and all the examples I see use normal distributions. See, for example, http://cr.middlebury.edu/Watters/public_html/pages/simulations.htm, which describes a simulation as
To move each particle, two random numbers are drawn: one is used to update the x-axis position, the other to update the y-axis position. The random numbers themselves are produced from an algorithm that calculates its output according to a bell-shaped (Gaussian) curve. The peak probability of this Gaussian distribution is set at zero, with decreasing probability of obtaining either a positive or negative value further and further from zero. For the simulations used here, the Gaussian distribution for single particles, lipids and water molecules has a standard deviation (SD) of 5 units, while the Gaussian distribution for the movement of larger ion/water assemblies and proteins is much more tightly clustered around the mean, having an SD of 2 units. As with all Gaussian distributions, +/- 1 SD accounts for about two-thirds of all the values; +/- 3 SD includes 99% of all possible values.

Last edited:
Almost nothing in nature has a uniform distribution

Conversely, not every natural behavior can be described by a normal distribution, much less the standard normal distribution.

I don't know anything about Verschuuren, but his wikipage describes him as scientist in the fields of biology and genetics, having written a thesis on a statistical analysis of genetic variation. I suspect that he knows more about statistical simulation in his field than any of us. Alternatively, he might have used a uniform distribution in his examples simply to KISS.

The wikipage about Brownian motion includes an image that depicts the ``characteristic bell-shaped curves of the diffusion of Brownian particles. [.... F]or increasing times they become flatter and flatter until the distribution becomes uniform in the asymptotic time limit``.

Last edited:
Thank you for the replies. In his simulation for stock price variation, he did use =NORM.S.INV(RAND())

Replies
4
Views
453
Replies
9
Views
832
Replies
11
Views
816
Replies
4
Views
393
Replies
4
Views
2K

1,196,308
Messages
6,014,572
Members
441,828
Latest member
cofracr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back