In mathematics, this is considered the “balls in boxes” problem - 32 balls are randomly lowered onto 32 bins. You can list the possible patterns and calculate their probabilities to determine the distribution. The naive approach will not work, because the number of templates is huge: (63!) / (32!) (31!) It is an “almost” quintillion.
You can solve at least if you recursively create a solution and use conditional probabilities.
Look at the article titled “The exact distribution of the maximum, minimum, and range of multidimensional / dirchl and multidimensional hypergeometric frequencies” by Charles J. Corrado.
In the future, we start from the leftmost bucket and calculate the probabilities for each number of balls that could fall into it. Then we move one to the right and determine the conditional probabilities of each number of balls that can be in this bucket, given the number of balls and buckets that have already been used.
Apologies for the VBA code, but VBA was all that I had when I was motivated to answer :).
Function nCr#(ByVal n#, ByVal r#) Static combin#() Static size# Dim i#, j# If n = r Then nCr = 1 Exit Function End If If n > size Then ReDim combin(0 To n, 0 To n) combin(0, 0) = 1 For i = 1 To n combin(i, 0) = 1 For j = 1 To i combin(i, j) = combin(i - 1, j - 1) + combin(i - 1, j) Next Next size = n End If nCr = combin(n, r) End Function Function p_binom#(n#, r#, p#) p_binom = nCr(n, r) * p ^ r * (1 - p) ^ (n - r) End Function Function p_next_bucket_balls#(balls#, balls_used#, total_balls#, _ bucket#, total_buckets#, bucket_capacity#) If balls > bucket_capacity Then p_next_bucket_balls = 0 Else p_next_bucket_balls = p_binom(total_balls - balls_used, balls, 1 / (total_buckets - bucket + 1)) End If End Function Function p_capped_buckets#(n#, cap#) Dim p_prior, p_update Dim bucket#, balls#, prior_balls# ReDim p_prior(0 To n) ReDim p_update(0 To n) p_prior(0) = 1 For bucket = 1 To n For balls = 0 To n p_update(balls) = 0 For prior_balls = 0 To balls p_update(balls) = p_update(balls) + p_prior(prior_balls) * _ p_next_bucket_balls(balls - prior_balls, prior_balls, n, bucket, n, cap) Next Next p_prior = p_update Next p_capped_buckets = p_update(n) End Function Function expected_max_buckets#(n#) Dim cap# For cap = 0 To n expected_max_buckets = expected_max_buckets + (1 - p_capped_buckets(n, cap)) Next End Function Sub test32() Dim p_cumm#(0 To 32) Dim cap# For cap# = 0 To 32 p_cumm(cap) = p_capped_buckets(32, cap) Next For cap = 1 To 32 Debug.Print " ", cap, Format(p_cumm(cap) - p_cumm(cap - 1), "0.000000") Next End Sub
For 32 balls and buckets, I get the expected maximum number of balls in buckets of about 3,532,941.
Output for comparison with ahmad's:
1 0.000000 2 0.029273 3 0.516311 4 0.361736 5 0.079307 6 0.011800 7 0.001417 8 0.000143 9 0.000012 10 0.000001 11 0.000000 12 0.000000 13 0.000000 14 0.000000 15 0.000000 16 0.000000 17 0.000000 18 0.000000 19 0.000000 20 0.000000 21 0.000000 22 0.000000 23 0.000000 24 0.000000 25 0.000000 26 0.000000 27 0.000000 28 0.000000 29 0.000000 30 0.000000 31 0.000000 32 0.000000