Random Number Generator

Did you look at my Bingo solution in your earlier post. I think that the methodology in that post will do what you need.

Another Option...

Please Login or Register  to view this content.

@Amy.... Would you be interested in a KISS solution that does not even require VBA?

See the attached Excel file.

In A2:A91, enter =RAND(). In B2:B91, enter formulas of the form =RANK(A2,$A$2:$A$91).

Press function key f9 to generate a set of 90 unique integers 1 to 90 in B2:B91.

Then "call" the numbers in order.

PS.... And we don't even need formulas in column B, if you are willing to select A2:B91, right-click and click Sort > Smallest to largest (or Largest to smallest; it does not matter) each time you want a new game. Preload B2:B91 with the numbers 1 to 90, just one time.

Originally Posted by torachan

@joeu2004, although remote, there is a need to check for duplicates as there is not guarantee of true uniques

Not if you follow __my__ instructions. It is unclear what your JPG is supposed to show. If there is a duplicate in column B, I missed it.

But as you say, the probability of a duplicate result from RAND or RANK for such a small sample (90) is so "remote" that I doubt that it can happen.

If you can provide an example, copy-and-paste-value in column A (only) so that the RAND results are "frozen", and attach an example Excel file, not an image, per the instructions in the bright yellow banner at the top of this web page. (Hard to miss, eh?)

That said, it is funny that you mention it. I did think of adding such a caveat, just for "completeness". If anyone is truly concerned (you should not be!!), you can add the following formula in a parallel column, starting in row 2, and look for "DUPLICATE!".

=IF(COUNTIF($B$2:$B$91,B2)>1,"DUPLICATE!","")

But I decided that it is a waste of time and an unnecessary complication.

Originally Posted by torachan

I ran your submission for approx 20 times before spotting a duplicate. It is there in the screen capture in rows 37 & 60

Thanks for pointing out the duplicate in your image.

But I suspect it was not generated according to __my__ methodology, at least not in rows 37 and/or 60.

That is why I asked you before -- and I ask you again -- to attach the Excel file that demonstrates the duplication.

UPDATE: Ignore my previous request to copy-and-paste-value column A. Better: Set Manual Calculation, and click File > Options > Formulas and uncheckmark (disable) Recalculate Before Saving.

It is nearly "impossible" for a duplicate to occur, using my methodology. See the explanation below.

Nevertheless, I would be very interested if you can provide an Excel file that demonstrates a duplication, again, using __my__ methodology.

FYI, I ran my methodology __200,000__ times without any duplication in each set of 90 bingo numbers. That takes less than 90 sec on my computer, which is the limit of my patience.

Arguably, this is a moot point, since Amy has not expressed any interest in the approach.

------

Explanation....

Since Excel 2010, Excel RAND uses a Mersenne Twister algorithm to generate non-negative pseudorandom values less than 1. The result is accurate to the full precision of 64-bit binary floating-point, which is more than the 15 significant digits that Excel displays.

Also note that RANK uses the full precision of 64-bit binary floating-point when comparing values, not rounded to 15 significant digits as Excel does for some other comparisons (notably, comparison operators like =, <>, >= etc and functions like COUNTIF and SUMIF.

Your JPG displays only 6 decimal places in column A. That is not enough precision for us to recognize any duplication of RAND results per se. And we do not know the precision of the duplicate integers(?) in column B, much less how they are generated. Contrary to conventional wisdom, "a picture is __not__ worth 1000 words (or KB)". (wink)

According to the wiki page (not always correct), the Mersenne Twister used by Excel should have a period (distance between duplicates) of about 2^19937 (-1!). But 64-bit binary floating-point can store only 2^1076 distinct values. So it does seem that there are 2^18861 MT values for each of the 2^1076 BFP values.

However, we don't know if the Excel implementation is capable of generating all 2^19937 unique values. That would require multiprecision arithmetic. More likely, Excel uses the precision of internal 80-bit floating-point registers in Intel-compatible CPUs. In that case, the best MT period would be about 2^16447; and there are 2^15371 MT values for each of the 2^1076 BFP values.

In either case, I believe that the calculation of the probability of a duplicate in a set of 90 is similar to the birthday problem. I was never able to wrap my head around that.

Originally Posted by torachan

please explain how I may do this - replicating a random event may take some time

Oh, I would agree -- if it can be done at all.

But previously, you wrote: ``I ran your submission for approx 20 times before spotting a duplicate``. I read that as an indication of just how easily you thought duplicates could arise.

In contrast, I ran __200,000__ sets of 90 with no duplicates in each set. I think that demonstrates just how unlikely it is.

(I suspect that the duplication in your JPG is the result of human error. It happens to all of us.)

Originally Posted by torachan

I take that as your acceptance that it is possible

Oh, sure. I acknowledged that "acceptance" in my first response to you.

But you wrote previously: ``although remote, there is a need to check for duplicates as there is not guarantee of true uniques``.

That is where we disagree. I claim that the possibility is __so_remote__ that it is not worth the complication.

For example, most of us write INT(20*RAND()+1) to derive a random number between 1 and 20. But in Excel, there is some theoretical possibility for that expression to return 21(!). To demonstrate, replace RAND() with 0.999999999999998. So, the careful implementation is MIN(20, INT(20*RAND()+1)).

But while I might inform someone of the possibility, I would not say there is "a need" to handle that quirk of Excel, because the probability is so infinitesimal.

(BTW, VBA Int does not have that problem. And while I did encounter the problem using RAND in Excel 2003, I don't know if it can happen using RAND in Excel 2010 and later.)

Originally Posted by torachan

with fifty plus years of experience I have a modest insight, at my advanced age I have forgotten more than most people have learned

Funny! I might say the same thing about me, word-for-word. I would only add that computer arithmetic and numerical quirks of Excel is something of a speciality of mine.

For example (TMI for most people)....

Originally Posted by torachan

RANK handles 17 digits - RAND only handles 15 digits

That is incorrect. As I stated previously, both use "the full precision of 64-bit binary floating-point", not rounding to 15 (or 17) significant decimal digits.

To demonstrate, enter =RAND() into A1, and enter =SUM(A1,-(A1&"")) into B1 formatted as Scientific.

(The expression A1&"" returns the value of A1 (as text) rounded to 15 significant digits.)

Most of the time, B1 displays a non-zero value. That demonstrates that A1 has more precision than the 15 significant digits that Excel displays.

(FYI, I use SUM instead of =A1-(A1&"") to work-around a quirk of Excel.)

-----

As for RANK, enter =RAND() into A1, and enter =A1+2^(INT(LOG(A1,2))-52) into A2. Then, enter =RANK(A1,$A$1:$A$2) into C1, and copy C1 into C2.

(A2 adds 1 to the least-significant bit of the 64-BFP value in A1.)

When formatted to display 15 significant digits (e.g. Scientific with 14 decimal places), A1 and A2 appear to be identical.

But C1 displays 2, and C2 displays 1.

That demonstrates a difference between A1 and A2.

And it demonstrates that RANK relies on more precision than the 15 significant digits that Excel displays.

-----

I know: but you said "17 digits" for RANK.

And we cannot prove or disprove that, because 17 significant decimal digits is the necessary and sufficient approximation to distinguish two different binary values, according to the IEEE 754 standard.

That is, if two binary values are different, so are their approximations with 17 significant decimal digits, and in the same relative order.

And RANK is only concerned with the order of any difference, not with the amount of the difference.

But we can demonstrate the difference between 17 significant decimal digits and "the full precision" of 64-BFP. And I assert that it is not likely that RANK would go to the trouble of rounding to a 17-digit approximation instead of using the exact binary value.

Enter =0.327606113778243-3.33E-16 into A1; that is an exact value returned by RAND(). And enter =A1+2^(INT(LOG(A1,2))-52) into A2, as before. Then enter =A1+A2 into A3.

Save the file as book1.xlsx, rename it to book1.zip, and open book1.zip\xl\worksheets\sheet1.xml to see the 17-digit approximations.

We see that A1 is 0.32760611377824267, and A2 is 0.32760611377824272.

So we might expect A3 to be 0.65521222755648539, if Excel used the 17-digit approximations.

But A3 appears to be 0.65521222755648534. The exact decimal representation of the full precision of the binary value is 0.655212227556485,33819794465671293437480926513671875.

(I use period for the decimal point and comma to demarcate the first 15 significant digits.)

The point is: Excel does not use the 17-digit approximation, any more than it uses the 15-digit approximation (with some exceptions).

Instead, it uses the "full precision" of the 64-BFP values.

(But Excel does use the 17-digit approximation to represent 64-BFP values in XML files, which includes "xlsx" and "xlsm" files (zip archives).)

Originally Posted by torachan

At this point we must agree to differ

I agree (wink). But I think we agree more than we differ. We agree on the facts, just not on the "need" to address them -- a judgment call.

In any case, I hope the details have been interesting.

(With apologies to Amy for the digression.)