Countif

tomacz

New Member
Joined
Aug 10, 2010
Messages
1
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.

moz-screenshot.png
moz-screenshot-1.png
<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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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:
Code:
   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
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,522
Messages
5,837,836
Members
430,517
Latest member
chessypack86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top