# Excel Formula

#### bloubul

##### Member
Hi Guys

=IF(COUNTIF(\$C4:\$G4,Q\$1)>=1,0,Q3+1), this formula returns a "0" for a win by a number, I need this formula to return a "1" for a win of a number and allocate a "0" to all other numbers that has not won. Will any one help me please.

BlouBul

#### Frank

##### Member
I don't have access to a PC at the moment as I'm away from home using an IPad, so I can't test this.

Try this:- =IF(COUNTIF(\$C4:\$G4,Q\$1)>=1,1,0)

Let me know how you get on.

#### bloubul

##### Member
Hi Frank

Thanks it works, but if I add Q3+1 than it makes a Circular error.

BlouBul

#### Frank

##### Member
But why are you adding it? You don't need it to do what you asked for it to do. As usual you expect me to be a mind reader! What is in cell Q3 and why do you want to add 2 to it? FULL explanation please. Otherwise I might have to become a clairvoyant and tell your fortune instead!

#### jack

##### Member
trios 60/6

trios 6 digit distint ex= 12,34,59= 1,2,3,4,5,9
separate draws60/6
http://www.mediafire.com/view/t8bpg712kd4sa3b/estudo_dos_trios.xlsx

#### bloubul

##### Member
Hi Frank

Got it going. Thank You.

BlouBul :coo:

#### Frank

##### Member
Good. You probably realised that one formula can only return one answer, not two. Hopefully you created a second formula to do the Q3+1 part.

#### bloubul

##### Member
Hi Frank

Yes that's what I did.

Tx

BlouBul

#### bloubul

##### Member
Hi Any One

I hope that I express myself correctly with the following information for you.
Row1. A1 to DZ1 contains some headers.
Row2. A2 to DZ2 contains headers.
Column A3 to A1510 is draw number. (Oldest to latest)
Column B3 to B1510 is the Draw Date.
Column C3 to I1510 contains the draw numbers and Bonus number.
Column J3 to BF1510 contains the last draw the number was drawn.
Column BG3 to BL1510 is the Occurrences formula of a number in 3 draws and that "Values" must be copied to the 3 Draws sheet.
Now the last 9 Occurrence "Values" must be copied to the 3 Draws sheet.
A New Lotto draw is added all the time. Yes draws are added, every Wednesday and Saterday.

This is the current macro that I use for powerball, can it be modify to to cater for the above.
Sub copy_occurrences()

Dim numrows, numcolumns, row, nr, nc, i, x As Integer

Sheets("3 Draws").Select
row = Range("B6").row
x = Range("maxdrawno").Value

Sheets("powerball").Select
Range("BB3").Select
Selection.Offset(x, 0).Select

nr = Selection.row
nc = Selection.Column

For i = 0 To 8

Sheets("powerball").Select
Cells(nr + i, nc).Select 'first row of results to copy
numrows = Selection.Rows.Count
numcolumns = Selection.Columns.Count
Selection.Resize(numrows, numcolumns + 4).Select 'select all the occurrences in the row.

Application.CutCopyMode = False
Selection.Copy

Sheets("3 Draws").Select
Cells(row + 5 * i, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Next

End Sub

BlouBul

#### Frank

##### Member
Well, now I can run Excel again, I can look at this..

Bloubul, for the record you did not supply enough information for anyone else other than me to deal with this request. After all , I wrote the macro you supplied. The macro refers to a named range on your sheet which you never mentioned. You do not tell us what lottery you are analysing (hence the logical name for the page where the results are), the existing macro assumes the results are stored on a page called "powerball" and will only work when that page name is used. You do not say where on the 3 draws page you want to put the results.

I see you have made the following structural changes to your data set from the last version designed for powerball.

1. The results have six balls (not five) to be analysed.
2. The occurences formulas have been moved further to the right from the previous version.
3. You have more results to analyse
4. You wish the 9 last ocurrence formulas to be exactly lined up with the last 9 results.

To achieve this, the macro has had to be amended to recognise all these changes.

Sub copy_occurrences_sixball()

Dim numrows, numcolumns, row, nr, nc, i, x As Integer

Sheets("3 Draws").Select
row = Range("B6").row
x = Range("maxdrawno").Value

Sheets("powerball").Select
Range("BG3").Select 'datum point for pasting results lower down
Selection.Offset(x - 9, 0).Select ' find first ocurrence to copy

nr = Selection.row
nc = Selection.Column

For i = 0 To 8 ' copy nine times row by row

Sheets("powerball").Select
Cells(nr + i, nc).Select 'first row of results to copy
numrows = Selection.Rows.Count
numcolumns = Selection.Columns.Count
Selection.Resize(numrows, numcolumns + 6).Select 'select all results on this row

Application.CutCopyMode = False
Selection.Copy

Sheets("3 Draws").Select
Cells(row + 6 * i, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True 'paste the row of occurrences
Application.CutCopyMode = False
Next 'row

End Sub

Check this out and see if it does what you want it to do. Warning, if you change the name of the page where the results are, you will also have to change the name mentioned in the macro ....Sheets("powerball").Select to match the new name (in two places).
The macro will fail if you have less than 9 results in your table.

#### bloubul

##### Member
Hi Frank

Once again I tip my hat to you sir. Thank you it works

BlouBul