The most pointless combination of functions

[cornflakegirl]

Glad we got that one sorted!

 

[Lewiy]

=IF(RANDBETWEEN(1,3)=1,1,IF(RANDBETWEEN(1,3)=2,2,IF(RANDBETWEEN(1,3)=3,3,RANDBETWEEN(1,3))))

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
For those of you who are interested in the statistics, I created a list of 5000 of the above formula and counted the frequency of 1s, 2s and 3s. I repeated this 20 times and got the following results:
<o> </o>
Average percentage of 1s: 43.143%
Average percentage of 2s: 32.184%
Average percentage of 3s: 24.673%
<o> </o>
Standard Deviation of 1s: 0.006262
Standard Deviation of 2s: 0.006368
Standard Deviation of 3s: 0.005044
<o> </o>
Total variation of percentages (highest percentage minus lowest percentage)
1s: 2.12%
2s: 2.52%
3s: 2.12%
<o> </o>
Conclusion……This seems to be a fairly consistently distributed random function!!!

 

[barry houdini]

Barry - can I be a geek, and ask how you worked out the distribution of 1s, 2s and 3s, please. Because I just spent 10 minutes creating a nice little tree, and I'm sure there must be a better way. But A level stats was a long time ago... Only if you haven't taken your tablet yet, obviously

I've only taken the little blue one so I'm OK for now.......

I think Gerald's done it for you but obviously I used 81 to keep to whole numbers.

Run the formula 81 times and 27 times the 1st Randbetween generates 1, formula over

For the 54 times that 1st RB generates 2 or 3, 2nd RB is brought in to play and 54/3 = 18 2s, formula over

For the remaining 36 times that RB #3 runs we get 36/3 = 12 3s, formula over

Then 24 times RB 4 is called there are 8 each of 1 , 2 and 3

so the totals are

1 - 27+8 =35 = 43.21%
2 - 18+8 =26 = 32.10%
3 - 12+8 =20 = 24.69%

Edit: which almost exactly matches the distribution that Lewiy experienced

 

[kgkev]

Its now clear that I have generated a pointless explaination of a pointless distibution generated by a pointless statement.

Sorry.

Thanks for explaining though - Its all a bit cleared now and i will try to avoid calling rand() functions multiple times within the same function.

 

[cornflakegirl]

Yeah, I realised when Gerald posted that that method wasn't actually as involved as I had thought when I gave up on it

 

[cornflakegirl]

kgkev - I, at least, enjoyed the discussion. And I don't think you need to apologise for inanities in the Lounge - I'm in trouble otherwise!

 

[barry houdini]

Its now clear that I have generated a pointless explaination of a pointless distibution generated by a pointless statement.

Congratulations!

.......and i will try to avoid calling rand() functions multiple times within the same function.

I enjoyed it, at least, took my mind off work for ½ an hour.........

 

[Oorang]

/me hands Cindy Ellis the cookie

 

[Norie]

Is this pointless?

=0

Is it a formula?

 

[framednlv]

Is this pointless?

=0

Is it a formula?

SUM(B1:H1)^0
Just trying to one up you!

Chris