summing up random results in dependance on their values

Hello,

I'm picking at random a whole number from between 1 and 10, but if I pick a 10, I'm to make another pick and add up both results. This procedure repeats until 10s stop appearing (every time a 10 shows up, I'm adding up the result of another pick). Is there any way of putting it into a formula other than with multilevel recurrent IFs? (I'm aware that no matter how many subbranches I'll put there, my current formula returns exact results only to some extent, i.e. until the number of subsequent 10s doesn't exceed the number of IFs, so I'm seeking a more universal one).

Thanks

Hi

Try this formula for up to 14 consecutive 10's:

=SUMPRODUCT(MID(TEXT(RAND(),"0.00000000000000E+00"),{1,19},{1,2})+{0,-1},{1,10})



Caution: this formula will give you a wrong number on average every 10^15 times.

This means that if you recalculate the formula every second you'll experience an invalid value every 30 million years.

Hi again

This seems to be equivalent and much simpler:

=RANDBETWEEN(1,9)-10*(1+INT(LOG(1-RAND())))

Please test

Thank you very much, I think that was what I sought. The formula seems to be valid, I've made 100 random picks, it returned the rarest result with frequency 0.05 and the most frequent one 0.17 (not counting in the results following 10s).

You're welcome. Thanks for the feedback.

Originally Posted by canis01

The formula seems to be valid, I've made 100 random picks, it returned the rarest result with frequency 0.05 and the most frequent one 0.17 (not counting in the results following 10s).

I also did a quick test for the second formula, 1 million picks 10 times (using columns in the worksheet) and the results are what I expected:

Between 0 and 10, Expected: 900,000. Test average: 899805.3
Between 10 and 20, Expected: 90,000. Test average: 90186.4
Between 20 and 30, Expected: 9,000, Test average: 8999.7
Between 30 and 40, Expected: 900. Test average: 909.4
Between 40 and 50, Expected: 90. Test average: 89.4
Between 50 and 60, Expected: 9. Test average: 8.7
Between 60 and 70, Expected: 1. Test average: 1.1

Seems OK.