1

I need simulate classic play cube (values 1-6) by Rnd function. My solution is Round(5 * Rnd() + 1, 0) , but when I generate 1 milion values, I have not good results (by picture). RESULTS HERE I think, that problem is in bad use of ROUND function, but I do not know where. Thank you for your answers...

alf.cz
  • 59
  • 6
  • Please correct me if I am wrong. But this post seems to be a duplicate of [Is Excel VBA's Rnd() really this bad?](http://stackoverflow.com/questions/38891165/is-excel-vbas-rnd-really-this-bad) (with an excellent answer). Yet, I can't be sure as I am failing to see a question in your post. Also, note that `Rnd()` must be initialized first: http://stackoverflow.com/questions/26281011/excel-vba-rnd-not-actually-random – Ralph Dec 01 '16 at 11:29

2 Answers2

5

Let's look at this piece-by-piece.

Rnd() gives a number between 0 and 1.

So 5 * Rnd() + 1 gives a number between 1 and 6.

We can think of this number as being equally likely to be in one of 10 half-integer areas: [1,1.5], [1.5,2]...[5.5,6].

One of these will round to 1 - [1,1.5].

Two of these will round to 2 - [1.5,2] and [2,2.5].

3, 4 and 5 similarly have two of these which will round to them.

One of these will round to 6 - [5.5,6]

This explains the distribution you're seeing.

Therefore Round(6 * Rnd() + 0.5, 0) should give you what you're looking for as it extends the area by half in both directions, so gives 1 and 6 the correct weightings.

bobajob
  • 1,192
  • 6
  • 12
1

Have you tried randbetween() ?? worked fine for me...

Solar Mike
  • 7,156
  • 4
  • 17
  • 32