Welcome Guest
Log In | Register )
You last visited December 4, 2016, 9:25 pm
All times shown are
Eastern Time (GMT-5:00)

Excel help: a formula or macro to count digit followers

Topic closed. 14 replies. Last post 9 months ago by alchemist7.

Page 1 of 1
PrintE-mailLink
Avatar

United States
Member #65711
October 4, 2008
39 Posts
Offline
Posted: March 14, 2016, 10:17 am - IP Logged

I'm looking for away to count the frequency of 0,1,2,3....etc that follows a particular number such as 4 with in a column.

 

Ex:

col 1  the number count for the digit 6; digits 7,4,2 all have 1 and every other has 0.

4928
93110
910110
2511
6968
7136
6556
4278
6732
2938

 

 

I did a search found a few members have something close to what i'm looking for but no formula for excel. Here is the best i've found so far.

https://www.lotterypost.com/thread/231778

 

Thanks for any help.

Alchemist

    Avatar

    United States
    Member #41846
    June 23, 2006
    459 Posts
    Offline
    Posted: March 14, 2016, 12:30 pm - IP Logged

    see if this is what you wanted. copy and paste your database if it suits you.  you can adjust how many games to include currently set to 500

     

    https://www.dropbox.com/s/pxxenycr7rjw9xh/follower%20freq.xlsm?dl=0

      Avatar

      United States
      Member #65711
      October 4, 2008
      39 Posts
      Offline
      Posted: March 14, 2016, 1:48 pm - IP Logged

      see if this is what you wanted. copy and paste your database if it suits you.  you can adjust how many games to include currently set to 500

       

      https://www.dropbox.com/s/pxxenycr7rjw9xh/follower%20freq.xlsm?dl=0

      Hello Phileight,

       

      This is what i was looking for! Thanks for you help!

       

      Achemist

        SergeM's avatar - slow icon.png
        Economy class
        Belgium
        Member #123700
        February 27, 2012
        4035 Posts
        Offline
        Posted: March 16, 2016, 3:08 pm - IP Logged

        Which state? I have NY and CA at the moment. I am working on CA, next on BE.

          Avatar

          United States
          Member #65711
          October 4, 2008
          39 Posts
          Offline
          Posted: March 17, 2016, 8:00 am - IP Logged

          Which state? I have NY and CA at the moment. I am working on CA, next on BE.

          Hi Serge,

          Its for SC. Phileight gave me what I was looking for.  I like that I could change the number of draws that I want to analyze. That would be great if you include that state on your site and thanks for asking.

            CARBOB's avatar - FL LOTTERY_LOGO.png
            ORLANDO, FLORIDA
            United States
            Member #4924
            June 3, 2004
            5893 Posts
            Offline
            Posted: March 17, 2016, 8:15 am - IP Logged

            see if this is what you wanted. copy and paste your database if it suits you.  you can adjust how many games to include currently set to 500

             

            https://www.dropbox.com/s/pxxenycr7rjw9xh/follower%20freq.xlsm?dl=0

            How do you adjust the game count?

              Avatar

              United States
              Member #65711
              October 4, 2008
              39 Posts
              Offline
              Posted: March 17, 2016, 12:05 pm - IP Logged

              How do you adjust the game count?

              Hi Carbob,

              You would change the number under #games.  Just keep in mind that the number refers to the row not the actual number of games. If you have a particular number of games in mind then you would have to add that number to 6.

                SergeM's avatar - slow icon.png
                Economy class
                Belgium
                Member #123700
                February 27, 2012
                4035 Posts
                Offline
                Posted: March 17, 2016, 12:34 pm - IP Logged

                Hi Serge,

                Its for SC. Phileight gave me what I was looking for.  I like that I could change the number of draws that I want to analyze. That would be great if you include that state on your site and thanks for asking.

                Easier asked for than done!

                  Avatar

                  United States
                  Member #41846
                  June 23, 2006
                  459 Posts
                  Offline
                  Posted: March 17, 2016, 5:40 pm - IP Logged

                  Hi Carbob,

                  You would change the number under #games.  Just keep in mind that the number refers to the row not the actual number of games. If you have a particular number of games in mind then you would have to add that number to 6.

                  I had seen that while I was working on it and had intended to make that correction, just forgot.  any way the corrected version is at  he same link.

                    Avatar
                    Lincoln, California
                    United States
                    Member #167130
                    June 27, 2015
                    256 Posts
                    Offline
                    Posted: March 17, 2016, 6:04 pm - IP Logged

                    Here is what I would do.  Say your data is set up with the Game or date in column A  Column B is the first number drawn and the one we will check.  Columns C and D are the other 2 drawn numbers.  In column E in Row 2 enter this formula =B2&B3.  that will give the new number combined with the previous number.  Now all you have to do is count the Sequences that match the pattern you want.  So to find how many times 0 comes after 4 write this formula =countif(E1:E200,"04"). 

                    This setup would be for 200 games.  If you run less than 200 the count is still good.  If you run more than 200 you will need to adjust the Range in the formula to include all of the rows.

                    Hope this helps.

                      Avatar

                      United States
                      Member #41846
                      June 23, 2006
                      459 Posts
                      Offline
                      Posted: March 17, 2016, 7:06 pm - IP Logged

                      Here is what I would do.  Say your data is set up with the Game or date in column A  Column B is the first number drawn and the one we will check.  Columns C and D are the other 2 drawn numbers.  In column E in Row 2 enter this formula =B2&B3.  that will give the new number combined with the previous number.  Now all you have to do is count the Sequences that match the pattern you want.  So to find how many times 0 comes after 4 write this formula =countif(E1:E200,"04"). 

                      This setup would be for 200 games.  If you run less than 200 the count is still good.  If you run more than 200 you will need to adjust the Range in the formula to include all of the rows.

                      Hope this helps.

                      That would work. I am partial to macro's, they are usually faster, and easier for me to set up.  Long lines of formulas are a pain, having to store intermediate results tend to clutter the display.  I never try to hide or protect the code, so if anyone wants to modify it they can.

                        Avatar
                        Lincoln, California
                        United States
                        Member #167130
                        June 27, 2015
                        256 Posts
                        Offline
                        Posted: March 17, 2016, 7:51 pm - IP Logged

                        That would work. I am partial to macro's, they are usually faster, and easier for me to set up.  Long lines of formulas are a pain, having to store intermediate results tend to clutter the display.  I never try to hide or protect the code, so if anyone wants to modify it they can.

                        So run the data, summarize it, erase the columns and just run the combined number for a new game and update your history.  That is where you use the macro.  Trying to do all of this with a macro is way beyond me.  You could record a macro for the update.

                          CARBOB's avatar - FL LOTTERY_LOGO.png
                          ORLANDO, FLORIDA
                          United States
                          Member #4924
                          June 3, 2004
                          5893 Posts
                          Offline
                          Posted: March 18, 2016, 5:09 am - IP Logged

                          Hi Carbob,

                          You would change the number under #games.  Just keep in mind that the number refers to the row not the actual number of games. If you have a particular number of games in mind then you would have to add that number to 6.

                          I missed that, getting old, thanks.

                            Avatar

                            United States
                            Member #41846
                            June 23, 2006
                            459 Posts
                            Offline
                            Posted: March 18, 2016, 7:31 am - IP Logged

                            I missed that, getting old, thanks.

                            That's my excuse. seem to use it more every day.  47 was so loooong  ago.    the revised version fixed having to add 6.  It's just my habit I like to leave a few lines at top for future use.  with minor changes to the macro  the data file could easily start on line 1. 

                            It could be incorporated into an existing spreadsheet. it could copy and paste an existing file from an open spreadsheet. it could select the midday or evening file etc. none of these things were in Alchemist7's request. 

                            if there is any interest I can show the steps and put detailed comments in the macro so you know what to change.

                              Avatar

                              United States
                              Member #65711
                              October 4, 2008
                              39 Posts
                              Offline
                              Posted: March 18, 2016, 12:55 pm - IP Logged

                              Hi to all,

                              Thanks AllenB for another way to solve my problem.  I may use it in my original work book if it can't handle all of the macros that are already in it. I didn't know how to start either way Macro or cell formula. I was thinking along your line but just couldn't figure a starting point. So thanks again.

                              Phileight thanks for the revision, I wasn't complaining or anything I just needed a starting point as I stated above I saw how you wrote so I adapted it for a pick 4 draw. I'm not great at any of it, macro or formulas. I like to figure things out for myself or make an attempt, so i google and search the forum, but i got stuck.

                              Thanks again to all.

                              Alchemist