On a message board I frequent someone commented that he hopes Miguel Cabrera is not back on the bottle during his first 0 for 21 streak. Being a math teacher I focused neither on Cabrera’s mental and physical health, nor how his drinking might affect the Tigers. Instead I thought that it was pretty unlikely he would go 0 for 21.
I ran 15 simulations where Cabrera had 600 at bats with a presumed 0.300 average. In only one of the 15 seasons did he have an 0 for 21. He had an 0 for 23 stretch that year. In the other 14 the longest hitless streaks ranged from 12 to 16. So what was the chance that he would go 0 for 21 if he got hits in 30% of his at bats?
You can calculate this with recursion- referring back to prior answers. If Cabrera got 0, 1, 2, 3, 4, 5, or any number less than 21 at bats there is no chance. If he hit 21 times the probability is 0.721. For any larger number of at bats the chance that he has a streak of length 21 in the season is the chance he already had one, or the chance that this at bat is the one that creates the streak. So add the prior at bats probability to the chance he hadn’t had such a streak 22 at bats ago times the probability he got a hit 22 at bats ago and then made 21 straight outs. Spreadsheets handle recursion well so I quickly found out Cabrera has about a 9.3% chance of such a hitless streak if he is a 0.300 hitter.
It was easy to change the average. It was easy to change the number of at bats. But, if I changed the streak length I had to reprogram the cells because the first few had to be 0. One had to be the out probability raised to the length of the streak and the rest had to refer to a cell several rows above and this reference varied with streak length. After a quick internet search I learned that the indirect function in Excel is made for these circumstances. Basically it turns strings into cell references. You can run your own internet search to find out the syntax of indirect().
The command I eventually needed looks like this: =IF(ROW()-10<$B$9,0,IF(ROW()-10=$B$9,$B$3^$B$9,INDIRECT(“$B”&ROW()-1)+$A$3*$B$3^$B$9*(1-INDIRECT(“$b”&ROW()-$B$9-1)))).
The row () command was new to me as well. Also concatenation using & automatically changes numbers to strings.
The original spreadsheet is here. Use beisbol as a password to unprotect it if you wish to change anything other than the batting average and the streak length.
Baseball and Not Baseball 