Number Counts

[cetizio]

Hello I am trying to get the formula that would count the number of skips in a particular colume. I would like to know how many skips before the number 0 came in and the number of skips before the number 1 came in and so on.
Here is part of my list. Have tried count, counta, countf and nothing is working like I want. Thank You

<table border="0" cellpadding="0" cellspacing="0" width="59"><col style="width: 44pt;" width="59"> <tbody><tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt; width: 44pt;" height="27" width="59">6</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">5</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">6</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">7</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">5</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">3</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">7</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">7</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">3</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">1</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">6</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">7</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">3</td> </tr> <tr style="height: 20.25pt;" height="27"> <td class="xl64" style="height: 20.25pt;" height="27">8</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">2</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">8</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">3</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">5</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">5</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">7</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">4</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">7</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">2</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">5</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">7</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">4</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">3</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">2</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">9</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">4</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">5</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">2</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">9</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">9</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">6</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">4</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">6</td> </tr> </tbody></table>

 

[bjurney]

Not sure what you mean by the number of skips. What is the desired outcome that you would like to have? Are you just asking how many rows are between each time an instance of 1 and/or 0 occur?

 

[cetizio]

No what I am trying to do is have excel count the number of time from my list of numbers that number 1 has skipped from that list then number 2 how many times it has skipped before hitting and so up to 9, hope this helps.

 

[bjurney]

What is the desired outcome? If I have these numbers in Column A, what do you want displayed?

<TABLE cellSpacing=0 cellPadding=0 width=59 border=0><COLGROUP><COL style="WIDTH: 44pt" width=59><TBODY><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="WIDTH: 44pt; HEIGHT: 20.25pt" width=59 height=27>6</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="HEIGHT: 20.25pt" height=27>5</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="HEIGHT: 20.25pt" height=27>6</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="HEIGHT: 20.25pt" height=27>7</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="HEIGHT: 20.25pt" height=27>5</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="HEIGHT: 20.25pt" height=27>3</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="HEIGHT: 20.25pt" height=27>7</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="HEIGHT: 20.25pt" height=27>7</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="HEIGHT: 20.25pt" height=27>3</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="HEIGHT: 20.25pt" height=27>1</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD class=xl64 style="HEIGHT: 20.25pt" height=27>6</TD></TR></TBODY></TABLE>

 

[T. Valko]

No what I am trying to do is have excel count the number of time from my list of numbers that number 1 has skipped from that list then number 2 how many times it has skipped before hitting and so up to 9, hope this helps.

Is this a lottery thing?

You want to know how many draws there have been since a specific number was last drawn?

1
5
3
9
2
4

Number of "skips" for 1 = 5 (or 6 depending on how you count it)

Is that what you want?

 

[cetizio]

yes that is what I am trying to get out of my list from colume a

 

[T. Valko]

yes that is what I am trying to get out of my list from colume a

Here's one way...

Let's assume your numbers are in column A starting in cell A2.

C2 = the number to check for

=IF(COUNTIF(A2:A100,C2),LOOKUP(1E100,A2:A100,ROW(A2:A100))-LOOKUP(2,1/(A2:A100=C2),ROW(A2:A100)),"")

Use a reasonable end of range A100 or, better yet, use a dynamic range.

What version of Excel are you using?

 

[T. Valko]

P.S.

You didn't say what result you wanted if the number has never been drawn (if that's even possible) so I wrote the formula to return a blank in that case.

 

[cetizio]

I am using excel 2010, I will try this out and let you know I would never have gotten this formula looking at it, Thank you so much.

 

[T. Valko]

I am using excel 2010, I will try this out and let you know I would never have gotten this formula looking at it, Thank you so much.

OK, I don't have Excel 2010 but to create a dynamic range it should be the same as it is in Excel 2007.

• Goto the Formulas tab
• Defined Names>Define Name
• Name: enter some descriptive name. Maybe something like Draws.
• Refers to: =$A$2:INDEX($A$2:$A$1000,COUNT($A$2:$A$1000))
• OK out

Then, the formula using the dynamic range would be:

=IF(COUNTIF(Draws,C2),LOOKUP(1E100,Draws,ROW(Draws))-LOOKUP(2,1/(Draws=C2),ROW(Draws)),"")