Exact Probability

indygo

Board Regular
Joined
Dec 2, 2013
Messages
126
Office Version
  1. 365
Platform
  1. Windows
hi all,

If we use Randbetween function (1,0) it should give us 50/50 in a long run. But it's accuracy has been questioned before:

Since this is the top result in Google for "how good is Excel's RAND() function" it is worth updating the answers for later versions of Excel
This paper by Guy Melard "On the accuracy of statistical procedures in Microsoft Excel 2010" tested the RAND() function in Excel 2010 and found it to be substantially improved over 2007 or 2003. Microsoft switched from an incorrect Wichmann and Hill generator (2007/2003) to the Mersenne Twister algorithm which has a much, much greater cycle length.
The authors of that paper ran it through "Small Crush", "Crush" and "Big Crush" tests for randomness and it passed nearly all of the tests.
So while it certainly isn't the same as True random numbers, the RAND() function in Excel 2010, and presumably newer versions, can no longer be considered terrible.
It should be noted however, that Excel 2010 still uses two completely different algorithms for the VBA random number generator, and the RNG that is in the data analysis tool-kit. According to Melard, both of those are still terrible, and in fact the VBA uses the same seed number each each time so produces the same numbers.
My biggest complaints with the random numbers in Excel are

  • You can't set the seed, so the numbers are not reproducible
  • The random numbers update every time you press enter/delete, and even if you set calculation options to Manual, they still update when you save the Excel file


So my question is this - using Excel 2016 how I can create accurate probability distribution? For example having in mind the Law of Large Numbers I would like to create function which would return specific probability - let's say 63% to 37%

Any ideas how to do it?
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I gues you coudl create a N rows with =RAND()*63*2 and avg the results, as the number of rows increases, the actual avg of outcomes will converge on the theoretical, or expected, ratio of outcomes


avg 63

try val1
1 40
2 78
3 55
4 117
5 46
6 53
7 67
8 36
9 123
10 98

Cheers
Sergio
 
Upvote 0
I'm sorry I'm not following

why *2?

Can you explain it again?
 
Upvote 0
I'm sorry I'm not following

why *2?

Can you explain it again?

You requested a function: "Randbetween function (1,0)"
Where the mean between 0 and 1 is 0.5 that is what 50/50 means, that is to say 0.5
And 0.5 * 2 * 63 is 63 the value you also requested
Cheers
Sergio
 
Upvote 0
You requested a function: "Randbetween function (1,0)"
Where the mean between 0 and 1 is 0.5 that is what 50/50 means, that is to say 0.5
And 0.5 * 2 * 63 is 63 the value you also requested
Cheers
Sergio


ok thanks
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

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