Occasional errors in guassian random numbers

Post Reply
User avatar
JezShed
Posts: 88
Joined: Sun Jun 14, 2009 3:55 pm

Occasional errors in guassian random numbers

Post by JezShed »

I'm using a formula that generates a gaussian distribution (normal) of random numbers.

My formula is:

=100+NORM.INV(RAND(),0,1/3)

That will effectively give a number centred around 100, with a standard deviation of 0.3333. So that effectively means a 3-sigma of ±1.

Anyway, I have been working on a spreadsheet that uses 2000 of these expressions (for a monte-carlo analysis) and some of the expressions occasionally produce a crazy crazy number or sometimes a #NUM! error.

I have attached a simplified test spreadsheet that contains lots of that expression. Pressing F9 a few times (maybe 10-20 times) and you'll get a cell with an error. You can catch this by observing the cell in the top left corner which calculates the average of all the cells.

The same spreadsheet doesn't fail in Excel.

Can you reproduce this error?

Thank you!
Jez
Attachments
gaussian distribution random numbers - occasional errors.xlsx
(19.9 KiB) Downloaded 3 times
occasional-gaussian-random-number-error.png
Post Reply

Return to “PlanMaker for Windows”