I Need Help: Draw Skips With Excel

[febausa]

Draw Skips are the number of drawings or intervals between draws when the PowerBall - White Ball was NOT picked.
The following table gives you the last time the PowerBall - White Ball was drawn (draws ago) and the Skips or intervals between previous draws.
Some numbers appear to go for long Skips before being drawn again. You should examine the Skips to see if it is one of those long Skips or whether the PowerBall - White Ball is approaching its high Skip maximum and will likely be drawn.

 

[Icewynd]

Hi febausa,

I'm not quite sure what your question is. Do you want to learn how to calculate number skips in your lottery game using Excel?

If so, set up an Excel worksheet as follows:

Row 1
Column A1: "Draw Date"
Columns B-F: "Draw Numbers" (White Balls)
Columns H-BN: type in the numbers 1-59, one per column

Row 2
Column A: date of the first draw
Columns B-F: the white ball numbers picked in that draw
Columns H-BN: in each column type a "zero" for each of the 5 winning numbers, and a "one" in each of the remaining 54 columns.

Row 3
Column A: date of the second draw
Columns B-F: the white ball numbers picked in that draw
Columns H-BN: type the following formula in cell H3 and copy across
=IF(COUNTIF($B3:$F3,H$1),0,H2+1)

For each additional draw copy the formula in cells H3:BN3 down to the next row.

This will give a chart with a "zero" for each hit followed by a count of how many draws have elapsed since the last hit (eg. 1 means the number hit 1 draw ago, 13 indicates that the number hit 13 draws ago.

You can set up a separate sheet in the same manner for the Powerball.

Hope that is what you were looking for.

Good luck!

 

[cdrake]

Tried this formula Icewynd but when i copied the formula to the other cells all I got was "0s". Disregard what I just said I found my error. Just have to slow down a little bit on the typing.

 

[Winalot2007]

I created these 2 Excel files (2007, 2010, and 2013 versions) for Skips and Hits for the PowerBall Game

Here are the download links:

https://app.box.com/s/x3pqvdl0riv3e2wy7vfk

https://app.box.com/s/c033vybz7toq25wr5y7c

Instructions:

Save both Excel files to a new folder. You can name the folder anything you want.

Open the Excel file PowerBall.xlsm. You can add a new game by just clicking the Add New Draw Toolbar. Add new drawing in cell E20 through J20. All draws will appear on line 20. Date is automatically updated.

Open the 2nd Excel file NumbersSkip****s. There are 2 worksheet tabs at the bottom. Once for the white balls 1 through 59 and a second worksheet for just the PowerBall number 1 through 35.

The Hits and Skips are calculated using all the draw history so you can adjust the number of past draws by entering the draw in cell

The Matrix changed after January 15, 2012 to a 5/59 1/35 game so you can enter 296 in cell B2 or either the worksheets.

 

[bloubul]

Hi Winalot2007

Very impressive....

I must change it as I'm playing a 5 / 45 with 1 / 20 powerball. Where must I change the sheets please.

BlouBul

 

[bloubul]

Hi Winalot2007

Very impressive....

I must change it as I'm playing a 5 / 45 with 1 / 20 powerball. Where must I change the sheets please.

BlouBul

 

[febausa]

Hi Icewynd :

Thank you , I like your formula. I want modify your formula , prefer the number hit no show X.
=IF(COUNTIF($B2:$F2,H$1),"X","")

Please recommend formula for working skips in lottery game.

 

[Icewynd]

Hi Icewynd :

Thank you , I like your formula. I want modify your formula , prefer the number hit no show X.
=IF(COUNTIF($B2:$F2,H$1),"X","")

Please recommend formula for working skips in lottery game.

Excel needs you to differentiate between a hit and "not a hit". The =IF is telling Excel "if it meets the criterion put this (X), otherwise put that ("")". You can use any numbers or letters you wish. However, be aware that if you mark a skip with an "X" you will not be able to have the skip count -- your formula gives X or blank, but you lose the information on how long a number has been out.

You might want to do it both ways, one chart with X's for visual scanning, and one with zeros and counts to provide information on how long the number has been out.

I generally use 0 (zero) to indicate a hit and 1,2,3,4...to indicate the skips so the formula would become:
=IF(COUNTIF($B3:$F3,H$1),0,H2+1)

Note that I moved the cells down a row -- if H1 is your column heading (i.e. lottery number in questions) then you want to have an empty row just below so that you can start calculating your skips from zero. Otherwise the first skip will add 1 to the column heading.

Hope this helps. If not please don't hesitate to ask for clarification.

Good luck!

 

[febausa]

Excel needs you to differentiate between a hit and "not a hit". The =IF is telling Excel "if it meets the criterion put this (X), otherwise put that ("")". You can use any numbers or letters you wish. However, be aware that if you mark a skip with an "X" you will not be able to have the skip count -- your formula gives X or blank, but you lose the information on how long a number has been out.

You might want to do it both ways, one chart with X's for visual scanning, and one with zeros and counts to provide information on how long the number has been out.

I generally use 0 (zero) to indicate a hit and 1,2,3,4...to indicate the skips so the formula would become:
=IF(COUNTIF($B3:$F3,H$1),0,H2+1)

Note that I moved the cells down a row -- if H1 is your column heading (i.e. lottery number in questions) then you want to have an empty row just below so that you can start calculating your skips from zero. Otherwise the first skip will add 1 to the column heading.

Hope this helps. If not please don't hesitate to ask for clarification.

Good luck!

Your formula working very well; thank you Icewynd.
Please help me in add the skips. I want show color in row 1 columns H: BN, according following range:
I want color green when counts in column pass range 0-10
I want color yellow when counts in column pass range 0-15
I want color orange when counts in column pass range 0-20
I want color red when counts in column pass range > 25

 

[Icewynd]

You want to use conditional formatting. In my Excel 2010 it is on the "Home" menu tab. Click on Conditional formatting, then on "Highlight Cells" and select "greater than" You want to type 25 into the box, then format the cell as you wish. Then you can add other formats using "Between". Once you have finished formatting your cell, you can copy that formatting to all the other cells in your range.

Good luck!

 

[febausa]

Help skips

Hi Icewynd:

I want send handmade example but the limit in attach, I unable to send it.

Please tell me, How send attachments.

=IF(COUNTIF($B2,D$1),0,D2+1)
Skips for red ball .
Above show this formula but it do not working. Please fix the this formula.
Thank you for your help.

 

[Icewynd]

Hi Febusa,

You can't send attachments here. You will have to open an account with a file sharing service (Mediafire, Dropbox), upload your file there and then share the link in your message so that we can download the file.

Not sure why the formula is not working.

Try this: =IF($B2=D$1,0,D2+1)

 

[febausa]

skips help

Hi Febusa,

You can't send attachments here. You will have to open an account with a file sharing service (Mediafire, Dropbox), upload your file there and then share the link in your message so that we can download the file.

Not sure why the formula is not working.

Try this: =IF($B2=D$1,0,D2+1)

Hi Icewynd:

=IF(COUNTIF($B2,D$1),0,D2+1) ( no working)
=IF($B2=D$1,0,D2+1) (no working)

Above show this formula but it do not working. Please fix the this formula.

I send attach handmade example .

http://www.mediafire.com/view/m5w89m767u3bsmf/skips_example.xlsx

Thank you for your help.

 

[bloubul]

febausa

Use this formula, it works for me:=IF(COUNTIF($C5:$G5,Q$1)>=1,0,Q4+1), replace the red colors with your range.

BlouBul

 

[Icewynd]

OK, here you go Febusa.

https://www.mediafire.com/?19n37kca3k32f1w

The formula wouldn't work when I cut & pasted it, but when I typed it in it worked fine.

Let me know if this is what you needed.

Good luck!