Countif

[tomacz]

Dear All,

I would like to ask you for a help with some dynamic formula. I dont know the solution so that's why I am posting this question and I would be glad for any answer.

I have a spreadsheet that simulates bet on roulette with 50% odds of winning. There is a bunch of ones and zeros that represent win or loss (1-win, 0 loss). I would like to create a formula that would follow this logic bet=10*2^number of preceding losses. Therefore I need to calculate number of losses after last win and before any future gain. So I need to count all zeros between ones and to add 1 to the upcoming win. I am aware of the fact that my explanation might be sort of misleading so please find an example below from which the logic is apparent.

<table style="border-collapse: collapse; width: 128pt;" width="170" border="0" cellpadding="0" cellspacing="0"><col style="width: 24pt;" width="32"> <col style="width: 52pt;" width="69" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 24pt;" width="32" height="20">Line</td> <td class="xl65" style="width: 52pt;" width="69">Count</td> <td class="xl66" style="width: 52pt;" width="69">Probablity</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">1</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">2</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">3</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">4</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">5</td> <td class="xl68">0</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">6</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">7</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">8</td> <td class="xl68">0</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">9</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">10</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">11</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">12</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">13</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">14</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">15</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">16</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">17</td> <td class="xl68">0</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">18</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">19</td> <td class="xl68">1</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">20</td> <td class="xl68">2</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">21</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">22</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">23</td> <td class="xl68">0</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">24</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">25</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">26</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">27</td> <td class="xl68">1</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">28</td> <td class="xl68">2</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">29</td> <td class="xl68">3</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">30</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">31</td> <td class="xl68">1</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">32</td> <td class="xl68">2</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">33</td> <td class="xl68">3</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">34</td> <td class="xl68">4</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">35</td> <td class="xl68">5</td> <td class="xl67">1.00</td> </tr> </tbody></table>
Thank you very much for your help.

Kind regards,
Tomas

 

[Andrew Fergus]

Hi and welcome to MrExcel

I'm not sure I understand the question 100%, but how about something like this to calculate the number of consecutive losses:

   A        B      
 1 Win/Loss Losses 
 2 0        1      
 3 1        0      
 4 0        1      
 5 1        0      
 6 0        1      
 7 0        2      
 8 1        0      
 9 0        1      
10 0        2      
11 1        0      
12 0        1      
13 1        0      
Sheet2
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
B2      =(A2=0)*1
B3:B13  =IF(A3=1,0,B2+1)
[Table-It] version 09 by Erik Van Geit

Notice the formula in cell B2 is different to that in cell B3 - B3 is to be copied down.

Andrew