+ Reply to Thread
Results 1 to 28 of 28

Lottery Draw History Question.

  1. #1
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Lottery Draw History Question.

    Greetings to all with the hopes of a wonderful Christmas Holiday!;

    Here is my current situation. I am tracking our WA 6/49 lotto drawings and have recorded all of 2016's draws so far. Here is what I am seeking to
    accomplish as I don't remember the function in excel to make this happen;

    Draw Date B1 B2 B3 B4 B5 B6 - (This is the header row)

    Imagine if you will, drawings totaling 153 draws of past lottery data. Numbers drawn for each lottery ball (obviously). Here's what is tripping me up.

    There are 153 numbers each, B1-B6. I would like to find the top 10 numbers by popularity, for each ball. I'm sure this is doable, I just can't remember the

    sequence of formula to use.....And thank you to all of you for your help in advance...

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Lottery Draw History Question.

    Hey Shanaga,

    It sounds like you want to us a CountIf function or perhaps the Frequency function...???

    https://support.office.com/en-us/art...7-FD9EA898FDB9
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    MarvinP, thanks for checking out my post. Was looking at those functions you mentioned, but did not know if that was going to give me the ranking of the
    top 10 numbers for each ball. I guess my next question would be this; How do you get the rank function to look at all of the numbers in one column, rank the
    top 10, as opposed to just 1 number out of the entire column?

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lottery Draw History Question.

    Here's one way to do it...

    This example is limited in scope due to space restrictions!

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    Date
    ---
    ---
    ---
    ---
    ---
    ---
    ---
    Number
    Count
    ---
    Top
    5
    2
    1/1/2016
    1
    2
    3
    4
    5
    6
    1
    3
    Count
    5
    3
    1/2/2016
    7
    8
    9
    10
    11
    12
    2
    1
    ---
    4
    1/3/2016
    1
    21
    24
    27
    33
    42
    3
    2
    1
    3
    5
    1/4/2016
    8
    9
    12
    17
    22
    33
    4
    1
    3
    2
    6
    1/5/2016
    5
    27
    28
    29
    30
    31
    5
    2
    5
    2
    7
    1/6/2016
    14
    18
    22
    25
    30
    38
    6
    1
    8
    2
    8
    1/7/2016
    16
    17
    22
    29
    30
    33
    7
    1
    9
    2
    9
    1/8/2016
    1
    3
    27
    40
    41
    49
    8
    2
    10
    1/9/2016
    17
    20
    28
    30
    31
    35
    9
    2
    11
    10
    1


    In column I list all the numbers.

    Enter this formula in J2 and copy down as needed:

    =COUNTIF(B$2:G$10,I2)

    Enter the top N number in cell M1. In this example 5 means a top 5. A top N list may have more than N entries that meet the criteria due to ties/duplicates.

    Enter this formula in M2. This will return the count of items that meet the top N criteria.

    =COUNTIF(J2:J11,">="&LARGE(J2:J11,M1))

    Now, generate the top N list...

    Enter this formula in M4:

    =IF(ROWS(M$4:M4)>M$2,"",LARGE(J$2:J$11,ROWS(M$4:M4)))

    Enter this array formula** in L4:

    =IF(ROWS(L$4:L4)>M$2,"",INDEX(I:I,SMALL(IF(J$2:J$11=M4,ROW(J$2:J$11)),COUNTIF(M$4:M4,M4))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Select L4:M4 and copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    Outstanding! I'm almost there. In your diagram above, starting with column B, and going through column G, would be lottery balls 1-6 in my spreadsheet. Here is my question; Is there a formula that will give me the top 10 numbers for each ball based on their draw frequency from their past draws? I.E. the more draws that they show up in the higher their ranking. And thank you once again for all that you've done, it means a lot...

    Steve

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lottery Draw History Question.

    If I understand what you're asking you can use a dynamic range.

    For example...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    Date
    ---
    ---
    ---
    ---
    ---
    ---
    2
    1/1/2016
    1
    2
    3
    4
    5
    6
    3
    1/2/2016
    1
    8
    9
    10
    11
    12
    4
    1/3/2016
    1
    14
    13
    14
    17
    18
    5
    1/4/2016
    19
    20
    21
    22
    23
    24
    6
    1/5/2016
    1
    26
    27
    28
    29
    30


    To count how many times number 1 was drawn in the LAST 3 draws...

    =COUNTIF(OFFSET(A2,COUNT(A:A)-1,1,-3,6),1)

  7. #7
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    Well, kinda. From all of the numbers produced for Ball 1, which is currently 154 draws, I need to find out what the top 10 numbers for Ball 1 are currently by their popularity or the most times they have shown up in those draws. I will be applying that formula actually for each ball. So, when the formula is applied, I should have a total of 60 of the top 10 numbers. 10 for each ball. I apologize for not explaining this more clearly. And thanks once again for all of your help and direction with helping me to solve this....Hopefully you had a wonderful Christmas as well....

    Steve

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lottery Draw History Question.

    OK, now I understand.

    I'm going out to lunch in a few minutes but I'll get back to you later this afternoon.

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Lottery Draw History Question.

    Hi,

    See if this does what you want..

    Lotto 6 by 49 for skanaga.xlsm

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lottery Draw History Question.

    Extract the unique numbers from column B and get the counts of those numbers.

    Enter this formula in I2:

    =MIN(B:B)

    Enter this array formula** in I3:

    =IFERROR(1/(1/MIN(IF(B2:B10>I2,B2:B10))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.

    Enter this formula in J2:

    =IF(I2="","",COUNTIF(B:B,I2))

    Copy down until you get blanks.

    Then, use the formulas in reply #4 to generate the top N list.

    Repeat this process for the other numbers.

  11. #11
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    Well, I think we're getting closer! That program I downloaded that you put together is phenomenal! Except I don't understand how it calculates the top 10 numbers
    for each ball i.e. B1 B2 B3 B4 B5 B6. I understand the layout, I think, but just don't quite grasp the concept of how to put this all together so it functions for all 6 balls. What am I not seeing?

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lottery Draw History Question.

    I think its time we see your data and expected results.

    Post a SMALL sample file, about 20 rows worth of data, and show us what results you expect for a top 3 application.

  13. #13
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    Sorry for all of this confusion over this, but here is the sample that you have requested. Again, I'm looking to get the top 10 most
    popular numbers for each ball position by their popularity or most times that they have been drawn so far. I have this idea of it
    looking like this;

    Rank Ball 1 Rank Ball 2 Rank Ball 3 Rank Ball 4 Rank Ball 5 Rank Ball 6.

    Something like that. I might even be forgetting an extra column in there to help with the formula, but hopefully you get the idea.
    Attached Images Attached Images

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lottery Draw History Question.

    I can't see the attached image.

    Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

    If you need to post an image post it in the *.jpg format.

    Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

  15. #15
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Lottery Draw History Question.

    Hey Skanaga,

    Did you open my attached file and type in your own numbers? My example will grow or shrink based on how many rows of data you have in the*left table. The right table will show the most frequently drawn number.

    Let me know you downloaded and tried my example.

  16. #16
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    Correct. I opened your program and was amazed at how it worked, but it appeared to only show
    the frequency of just 1 number? I needed something similar to this, but only to show the top 10 numbers by their draw frequency for each ball. (i.e. 1-6) Is this even doable? Or am I asking too much of excel?
    I also can't send another snippet of the program I am using until tonight when I get home around 7pm.
    Hope the above helps somewhat though...

  17. #17
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    o.k., let's try this again, only in JPG format. Hopefully this will help clear up any questions that may remain.

    Trying to get the top 10 most frequent balls drawn for each ball number, i.e. (B1,B2,B3,etc.) Then rank each of those top 10
    balls 1-10, for each ball. Hopefully I am not asking excel to do something it is not designed to do? And as always, thank you for
    your help in advance..

    Steve
    Attached Images Attached Images

  18. #18
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lottery Draw History Question.

    The formulas I suggested will do what you want.

    What's not working?

    Or, do you not want to use formulas?

  19. #19
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    No, formulas are just fine, and I am thankful for your hard work with this, so it's clearly me then as I don't understand or see how the example you sent me is
    coming up with the top 10 numbers for each ball. Or do I just have to keep working with it adding additional draw numbers for the program to work?

  20. #20
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    Marvin, yes, and thank you for your work on this wonderful program! A question does come to mind though. I am looking for it to show the top 10 numbers for each
    ball. I.E. B1,B2,B3, etc. will this program do that? Or do I have to modify something or change the row titles perhaps? Thanks again...

  21. #21
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lottery Draw History Question.

    Here's a small sample for a top 3.

    Data Range
    B
    C
    D
    E
    F
    G
    H
    4
    Ball 1
    ------
    Ball 1
    Count
    ------
    Top
    3
    5
    1
    1
    2
    Count
    3
    6
    19
    2
    4
    ------
    ------
    7
    3
    3
    3
    2
    4
    8
    2
    4
    3
    3
    3
    9
    4
    8
    1
    4
    3
    10
    10
    10
    2
    11
    12
    12
    1
    12
    3
    13
    1
    13
    1
    14
    1
    14
    8
    18
    1
    15
    3
    19
    1
    16
    4
    17
    18
    18
    4
    19
    2
    20
    13
    21
    2
    22
    14
    23
    2
    24
    10


    Enter this formula in D5:

    =MIN(B5:B24)

    Enter this array formula** in D6 and copy down until you get blanks:

    =IFERROR(1/(1/MIN(IF(B$5:B$24>D5,B$5:B$24))),"")

    Enter this formula in E5 and copy down as needed:

    =IF(D5="","",COUNTIF(B$5:B$24,D5))

    In H4 enter the desired top N number. In this example I'm doing a top 3 so I enter 3 in H4. You would enter 10 in H4.

    Enter this formula in H5:

    =COUNTIF(E5:E16,">="&LARGE(E5:E16,H4))

    This will return the count of records that fall within the top N. Remember, there can be more than N records that meet the criteria.

    Enter this formula in H7:

    =IF(ROWS(H$7:H7)>H$5,"",LARGE(E$5:E$16,ROWS(H$7:H7)))

    Enter this array formula** in G7:

    =IF(ROWS(G$7:G7)>H$5,"",INDEX(D:D,SMALL(IF(E$5:E$16=H7,ROW(E$5:E$16)),COUNTIF(H$7:H7,H7))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Select G7:H7 and copy down until you get blanks.

    Repeat this process for each of the other balls.

  22. #22
    Registered User
    Join Date
    12-26-2016
    Location
    Thurmont, MD
    MS-Off Ver
    2010
    Posts
    15

    Re: Lottery Draw History Question.

    Would something like RANK work for you? Make a list of the numbers 1 - x and then Rank them.
    https://support.office.com/en-us/art...c-c279cf99f723

  23. #23
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    Tony, your formulas and idea is over the top and awesome as usual. However, I think I just backed myself into a corner with this sheet, trying to get the layout
    so it isn't so confusing looking. If you look at post #17, is there a way to lay this out like so; just to the right of the draw date, skipping a column, is there a way
    to make it look like this and yet function properly;

    Count B1 Rank B1 Top 10 B1 Count B2 Rank B2 Top 10 B2 Count B3 Rank B3 Top 10 B3......and so on up to Ball number 6....

    And please forgive me if it's already in the formulas that you sent me earlier, I just can't seem to wrap my head around that particular layout and make it
    work properly. Clearly a handicap of my own. But most of all, thank you for your diligence in putting this scenario together for me, as I would not be as far
    as I am without your help. All of you actually.....I am truly grateful to all of you for your help....

    Steve

  24. #24
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lottery Draw History Question.

    How about making up a SMALL sample file that shows us what you want and where you want it.

    20 rows worth of data is plenty. Remember, it's just a SAMPLE file to give us an idea of what you're looking for.

  25. #25
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    o.k., so I've been working on this spreadsheet all week trying to rank the top 10 numbers for each ball separately. I have some of the formulas in place thanks to the input and
    advice of you good people on this forum. I am also including the latest spreadsheet so you can see what is left for me to do, (I think). I have each ball counted and in place as the
    given formula has provided. I now just need to find out how to rank the top 10 numbers for each ball and insert the formula. Can someone check this out for me to make sure that I
    am not forgetting a column for this to take place? And thank you once again for everyone's help with this..

    Steve
    Attached Files Attached Files
    Last edited by skanaga99022; 01-01-2017 at 01:25 PM. Reason: Updated the current spreadsheet

  26. #26
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Lottery Draw History Question.

    Hi Steve,

    If you were to do 6 Pivot Tables, one for each ball draw, it would solve your problem. Pivots allow the top 10 to show only. No formulas needed. If you wanted to add more*rows to your data, you should have a dynamic*named range for your data. Study about pivot tables a bit and keep asking. I'll do your problem if you get back to me tomorrow.

  27. #27
    Registered User
    Join Date
    11-22-2016
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    17

    Re: Lottery Draw History Question.

    Marvin. Thanks for getting back to me. I completely forgot about pivot tables regarding this sheet. You are the best and I truly appreciate your help with this.
    I also clicked and added your reputation star regarding all of your help. Just thought of something though. If I do a pivot table for the top 10 numbers for each ball,
    isn't that going to throw the draw data and dates off of the chronological order?

  28. #28
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Lottery Draw History Question.

    OK skanaga,

    Find the attached where I've defined a*Dynamic Named Range*called DrawTable that will grow or shrink depending on how many rows of data you have. Then I created 6 pivot tables, one for each ball, sorted from largest to smallest and only showing the top 10 count*values. NOTE - after changing any of the numbers, you will need to REFRESH the pivot tables so they show updated*values/counts. Is this what you want?

    Lotto 6 by 49 for skanaga using Pivots.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Last time out in lottery
    By 1legin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2015, 06:03 AM
  2. Lottery Simulation
    By gzhelev in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2015, 01:20 PM
  3. Help me win the Lottery...
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-15-2014, 07:56 AM
  4. Help with lottery
    By ilikemax in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-20-2014, 08:22 PM
  5. Check repeated draw numbers on Florida lottery History > 2000 rows :(
    By miariver in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-28-2013, 07:35 PM
  6. lottery
    By heramiah in forum Excel General
    Replies: 14
    Last Post: 02-23-2010, 07:27 AM
  7. Random Draw Question
    By Chadk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2007, 05:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1