Count event occurance in craps.

[torch0084]

I am looking for an equation that would to count the number 7 rolled before the point is established in a series of craps rolls

For example, the following rolls occurred

Roll 1 3 7 11 12 3 7 4 6 4

Roll 2 7 7 8 9 10 9

I want to count the number of 7 rolled before the point is established. A point is any of the following numbers 4, 5, 6, 8, 9, or 10

The result for that data would be 4/8 50% of the time. Can anyone help?

 

[Domenic]

The following uses two helper columns to help arrive at the desired result. One column is used to give the number of sevens before the point is established for each row. The other gives you the total number of rolls required before the point is established for each. Then SUM can be used to sum these amounts, etc. Assuming that A2:J10 contains the data, try the following...

L2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=COUNTIF(A2:INDEX(A2:J2,MATCH(TRUE,ISNUMBER(MATCH(A2:J2,{4,5,6,8,9,10},0)),0)),7)

M2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(N(L2),COUNT(A2:INDEX(A2:J2,MATCH(TRUE,ISNUMBER(MATCH(A2:J2,{4,5,6,8,9,10},0)),0)))-1,0)

Hope this helps!

 

[torch0084]

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CESC%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <o>Thank you for the reply. I tried the equation , but got the result of =N/A
</o>
<o>Here is what I did</o>
<o>
</o>
My data (is array the correct term for my data arrangement?) is located within cells k7:ai36 (meaning this particular data set has 30 row {aka - come out rolls}.)
<o> </o>
I have set the result to appear in cell g7 and I changed the equation as follows to match my data location
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]=COUNTIF(K7:INDEX(K7:AI36,MATCH(TRUE,ISNUMBER(MATCH(K7:AI36,{4,5,6,8,9,10},0)),0)),7)<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]But my result is N/A<o></o>[/FONT]

 

[Domenic]

The formula should be...

G7, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=COUNTIF(K7:INDEX(K7:AI7,MATCH(TRUE,ISNUMBER(MATCH(K7:AI7,{4,5,6,8,9,10},0)),0)),7)

Hope this helps!

 

[torch0084]

That worked. After I copied the formula into the sheet. I had to click at the end of the formula and do the control+shift+return. Then it gave me the correct results for each row.

Thanks

 

[Domenic]

You're very welcome!

 

[torch0084]

For the second equation I have

=IF(N(L7),COUNT(L7:INDEX(L7:AJ7,MATCH(TRUE,ISNUMBER(MATCH(L7:AJ7,{4,5,6,8,9,10},0)),0)))-1,0)

For the data in this row, the shooter threw a 6 on the come out roll and a 6 on the second roll. Using this equation, the result is a 0, it should be a 1. I changed the red highlighted number to +1 and did control+shift+return and the result is a 2.

After reviewing the data, I need to add a 1 count to each row because the throw the shooter makes when they establish a point is considered a come out roll yet.

What does the control+shift+return do exactly?

Thanks

 

[Domenic]

Remove the IF condition and the '-1' bit from the formula...

=COUNT(L7:INDEX(L7:AJ7,MATCH(TRUE,ISNUMBER(MATCH(L7:AJ7,{4,5,6,8,9,10},0)),0)))

With regards to CONTROL+SHIFT+ENTER, it signifies to Excel that we're dealing with an array formula. For additional information, have a look at Excel's help file under 'array formula'.

 

[torch0084]

Awesome!!!

That did it. Thanks a lot for the help. That was a very advanced formula and it worked.


Now I can crunch some data.

Thanks Again

 

[Domenic]

You're very welcome!