Lottery check

Discussion in 'Microsoft Excel New Users' started by Guest, Sep 13, 2006.

  1. Guest

    Guest Guest

    We buy 35 tickets twice a week and always the same numbers. Can I make a
    spreadsheet that will check our tickets against the winning numbers?
     
    Guest, Sep 13, 2006
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    "Ron" wrote:
    > We buy 35 tickets twice a week and always the same numbers. Can I make a
    > spreadsheet that will check our tickets against the winning numbers?


    One way to set it up ..

    Assuming the winning numbers will be entered in A1:F1 in Sheet1,
    for example: 1, 15, 25, 38, 48, 49
    (The numbers within A1:F1 need not be sorted, can be in any order)

    And the picked #s are listed in Sheet2, in cols B to G, row1 down
    4 15 20 28 35 49
    1 15 25 38 47 48
    6 15 25 38 41 49
    etc

    (as before, the numbers within each row need not be sorted)

    Put in say, I1:
    =SUMPRODUCT(--ISNUMBER(MATCH(B1:G1,Sheet1!$A$1:$F$1,0)))
    Copy I1 down.

    Col I will return the desired # of matched numbers
    For the sample data, you'll get in I1:I3 : 2, 5, 4
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
     
    Guest, Sep 13, 2006
    #2
    1. Advertisements

  3. Guest

    Biff Guest

    Here's a file that does what you want.

    Ticket Checker.xls 84kb

    http://cjoint.com/?jneKqRchxY

    It has a sheet for 5 numbers and one for 6 numbers. There is one very simple
    macro that clears the number grid if you click the RESET button. It will
    highlight and count any matches. It uses data validation so you won't make
    any mistakes when you enter your numbers.

    Biff

    "Ron" <> wrote in message
    news:...
    > We buy 35 tickets twice a week and always the same numbers. Can I make a
    > spreadsheet that will check our tickets against the winning numbers?
     
    Biff, Sep 13, 2006
    #3
  4. Guest

    Guest Guest

    There are 7 numbers drawn so that checker won't work but thanks.

    "Biff" wrote:

    > Here's a file that does what you want.
    >
    > Ticket Checker.xls 84kb
    >
    > http://cjoint.com/?jneKqRchxY
    >
    > It has a sheet for 5 numbers and one for 6 numbers. There is one very simple
    > macro that clears the number grid if you click the RESET button. It will
    > highlight and count any matches. It uses data validation so you won't make
    > any mistakes when you enter your numbers.
    >
    > Biff
    >
    > "Ron" <> wrote in message
    > news:...
    > > We buy 35 tickets twice a week and always the same numbers. Can I make a
    > > spreadsheet that will check our tickets against the winning numbers?

    >
    >
    >
     
    Guest, Sep 13, 2006
    #4
  5. Guest

    Guest Guest

    We're in Singapore a few times a year! We teach at Ngee Ann Poly. How wierd...
    Thanks Max. I'll try this out and get back.

    "Max" wrote:

    > "Ron" wrote:
    > > We buy 35 tickets twice a week and always the same numbers. Can I make a
    > > spreadsheet that will check our tickets against the winning numbers?

    >
    > One way to set it up ..
    >
    > Assuming the winning numbers will be entered in A1:F1 in Sheet1,
    > for example: 1, 15, 25, 38, 48, 49
    > (The numbers within A1:F1 need not be sorted, can be in any order)
    >
    > And the picked #s are listed in Sheet2, in cols B to G, row1 down
    > 4 15 20 28 35 49
    > 1 15 25 38 47 48
    > 6 15 25 38 41 49
    > etc
    >
    > (as before, the numbers within each row need not be sorted)
    >
    > Put in say, I1:
    > =SUMPRODUCT(--ISNUMBER(MATCH(B1:G1,Sheet1!$A$1:$F$1,0)))
    > Copy I1 down.
    >
    > Col I will return the desired # of matched numbers
    > For the sample data, you'll get in I1:I3 : 2, 5, 4
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
     
    Guest, Sep 13, 2006
    #5
  6. Guest

    Don Guillett Guest

    Here is an idea I used on the Texas Lottery until I decided NOT to play
    anymore. Ripoff.

    conditional format using formula to highlight the cell
    =SUMPRODUCT(($B$5:$F$5=B9)*1)
    & copy across
    to count
    =IF(G9=0,"",SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5)))
    with conditional formatting cell value is >=4

    --
    Don Guillett
    SalesAid Software

    "Ron" <> wrote in message
    news:...
    > We buy 35 tickets twice a week and always the same numbers. Can I make a
    > spreadsheet that will check our tickets against the winning numbers?
     
    Don Guillett, Sep 13, 2006
    #6
  7. Guest

    Guest Guest

    Thanks Max. It works perfectly!
    And thank you to all who replied.
    This is GREAT!

    "Max" wrote:

    > "Ron" wrote:
    > > We buy 35 tickets twice a week and always the same numbers. Can I make a
    > > spreadsheet that will check our tickets against the winning numbers?

    >
    > One way to set it up ..
    >
    > Assuming the winning numbers will be entered in A1:F1 in Sheet1,
    > for example: 1, 15, 25, 38, 48, 49
    > (The numbers within A1:F1 need not be sorted, can be in any order)
    >
    > And the picked #s are listed in Sheet2, in cols B to G, row1 down
    > 4 15 20 28 35 49
    > 1 15 25 38 47 48
    > 6 15 25 38 41 49
    > etc
    >
    > (as before, the numbers within each row need not be sorted)
    >
    > Put in say, I1:
    > =SUMPRODUCT(--ISNUMBER(MATCH(B1:G1,Sheet1!$A$1:$F$1,0)))
    > Copy I1 down.
    >
    > Col I will return the desired # of matched numbers
    > For the sample data, you'll get in I1:I3 : 2, 5, 4
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
     
    Guest, Sep 13, 2006
    #7
  8. Guest

    Guest Guest

    Glad to hear that, Ron !
    Thanks for feeding back ..
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Ron" wrote:
    > Thanks Max. It works perfectly!
    > And thank you to all who replied.
    > This is GREAT!
     
    Guest, Sep 14, 2006
    #8
  9. Guest

    Guest Guest

    Guest, Sep 14, 2006
    #9
  10. Guest

    Guest Guest

    Hi Max,
    The formula works but only for approx. 20 lottery tickets and then it
    doesn't seem to check the rest. Am I doing something wrong? It's almost as if
    it needs to be told to check them all???
    Can anyone help?
    Ron

    "Max" wrote:

    > "Ron" wrote:
    > > .. How weird ...

    >
    > .. it's ok, cyberspace knows no boundaries <g>
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
     
    Guest, Sep 14, 2006
    #10
  11. Guest

    Guest Guest

    "Ron" wrote:
    > The formula works but only for approx. 20 lottery tickets and then it
    > doesn't seem to check the rest. Am I doing something wrong?
    > It's almost as if it needs to be told to check them all???


    Try these checks, in sequence ..
    1. Check calc mode is set to auto (Tools > Options > Calculation tab, Check
    "Automatic" > OK)
    2. Check that cells are not inadvertently pre-formatted as text (Format >
    Cells). If so, re-format the cell as General or Number, then re-confirm the
    formula (click inside the formula bar, press Enter) -- you need to re-confirm
    the formula before it'll work. Re-formatting alone will not trigger it.
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
     
    Guest, Sep 14, 2006
    #11
  12. Guest

    Guest Guest

    Just a further thought .. if there's the possibility of the source winning
    numbers and/or picked numbers being either "text-entered" -- ie numbers
    entered with a preceding apostrophe, or entered into cells pre-formatted as
    text (the pre-formatting as text unknown to you, of course), then use this
    slightly tweaked version instead:

    =SUMPRODUCT(--ISNUMBER(MATCH(B1:G1+0,Sheet1!$A$1:$F$1+0,0)))

    The "+0" to both source ranges will coerce the text numbers (if any) to real
    numbers for correct results.
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
     
    Guest, Sep 14, 2006
    #12
  13. Guest

    Guest Guest

    Some were text formatted. I dhanged them all and it all works as you said.
    Thanks again Max. We'll be in SQ Oct 22 thru Nov 7.
    Ron in Ottawa, Canada
    How's your Access?

    "Max" wrote:

    > Just a further thought .. if there's the possibility of the source winning
    > numbers and/or picked numbers being either "text-entered" -- ie numbers
    > entered with a preceding apostrophe, or entered into cells pre-formatted as
    > text (the pre-formatting as text unknown to you, of course), then use this
    > slightly tweaked version instead:
    >
    > =SUMPRODUCT(--ISNUMBER(MATCH(B1:G1+0,Sheet1!$A$1:$F$1+0,0)))
    >
    > The "+0" to both source ranges will coerce the text numbers (if any) to real
    > numbers for correct results.
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
     
    Guest, Sep 14, 2006
    #13
  14. Guest

    Guest Guest

    "Ron" wrote:
    > Some were text formatted.
    > I changed them all and it all works as you said.
    > Thanks again Max.


    Glad we got that bit nailed down ..

    > How's your Access?

    Very skimpy ..
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
     
    Guest, Sep 15, 2006
    #14
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tim

    Resize check box and have "x" instead of a check

    Tim, Feb 2, 2004, in forum: Microsoft Excel New Users
    Replies:
    1
    Views:
    7,769
    Wouter HM
    Feb 16, 2004
  2. Guest

    How do I make a lottery checker

    Guest, Mar 4, 2005, in forum: Microsoft Excel New Users
    Replies:
    2
    Views:
    352
    Don Guillett
    Mar 5, 2005
  3. Guest
    Replies:
    0
    Views:
    431
    Guest
    Apr 11, 2006
  4. Xray_Man

    How to calculate frequency of lottery numbers?

    Xray_Man, Jun 2, 2009, in forum: Microsoft Excel New Users
    Replies:
    23
    Views:
    1,096
    Xray_Man
    Jun 4, 2009
  5. Don Guillett

    Re: How to calculate frequency of lottery numbers?

    Don Guillett, Jun 3, 2009, in forum: Microsoft Excel New Users
    Replies:
    0
    Views:
    2,232
    Don Guillett
    Jun 3, 2009
Loading...

Share This Page