=1-2*rand()

kikitinoq

New Member
Joined
Dec 9, 2005
Messages
4
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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:
Upvote 0
Pretty bad simulation. More accurate for 1D motion would be =NORM.S.INV(RAND()), which is normal rather than uniform.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top