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.

<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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,891
Messages
5,514,003
Members
408,980
Latest member
Naomi_

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top