Welcome Guest
Log In | Register )
You last visited January 20, 2017, 12:40 am
All times shown are
Eastern Time (GMT-5:00)

Excel Help Please...

Topic closed. 16 replies. Last post 11 years ago by LottoChica23.

Page 1 of 2
PrintE-mailLink
Bryan's avatar - Lottery-002.jpg
Mid-Missouri
United States
Member #644
August 31, 2002
4271 Posts
Offline
Posted: July 10, 2006, 7:01 pm - IP Logged

Howdy All,

Let me start by saying that I am completely lost when it comes to Excel but I am trying to learn. Any help is appreciated...
O.K. I have 2 questions. Here is what I am working on...

DateNumbersBox0123456789
             
7/3/060320231 11      
7/4/06936369   1  1  1
7/5/0601000121        
7/6/06821128 11     1 
7/7/060490491   1    1
7/8/06672267  1   11  
7/9/06499499    1    2

I need the formulas that can do this for me. When I put (032) in the first box (under Numbers) I would like the box number to be filled in automatically (under Box) and the tick sheet to the right to automatically make the appropriate marks.

Any and all help is appreciated.

Thanks,

Bryan  :)

    LottoChica23's avatar - Aquarius
    Fughedaboutit (NY)
    United States
    Member #8160
    October 26, 2004
    6777 Posts
    Offline
    Posted: July 10, 2006, 8:05 pm - IP Logged

    OK Bryan,

    I do know that this has to be done in a few parts. First you have to break the digit number into 3 separate digits, meaning that 032 would become 0  3  2. This would take up 3 additional columns.  Assuming that we are using the number in the second column (B) to do this and you are and putting the formula in column C the formula would look like this: =INT($B3/100)    In column D the formula would look like this =INT(($B3-(C3*100))/10)  In column E the formula would look like this  =INT(($B3-((C3*100)+(D3*10)))/1)  after you get the formulas right, you can copy to the other cells in each column.

    These formulas will isolate the 3 separate digits.  After that, you will have to write a formula to test each of the digits 0-9 against the 3 isloated digits to put a tick mark in the appropriate column. You should be able to do this using nested IF statements. Maybe somebody can shed some light on this or you can google working with nested IFs in excel.

     

    To get the box for each number you can use the isloated digits to form the various box numbers.  You would substitute each cell reference in the formula =C3*100+D3*10+E3 to make up the various boxes. eg. CED, DEC, DCE, ECD, ECD

     

    I hope this helps you accomplish what you want to do.

     

    Matrix Chart Instructions--> http://www.lotterypost.com/thread/191818 

    check out mysticwomyn Announcers --> http://www.lotterypost.com/thread/140695/673306

    You can judge the integrity of a man by the way he treats those that can do nothing for him...Thumbs Up

      Bryan's avatar - Lottery-002.jpg
      Mid-Missouri
      United States
      Member #644
      August 31, 2002
      4271 Posts
      Offline
      Posted: July 10, 2006, 8:13 pm - IP Logged

      Thank you for the info. I am printing this out for further reference. But Winsumloosesum has already fixed it for me.

      Thanks again. It is so nice to know that help is available around every corner.

      Best of Luck,

      Bryan  :)

        lottaloot's avatar - AvatarZ56
        Redford/MI
        United States
        Member #3396
        January 18, 2004
        4867 Posts
        Offline
        Posted: July 10, 2006, 10:42 pm - IP Logged

        This is for those that do not know how to change this draw result into box form: 

        In columns B:D, I have the draw result.  ex: 3|3|5

        Wherever you want the actual result (335) to end up is where you place this formula

        =CONCATENATE(B2,C2,D2)

        Then wherever you want the box form to end up is where you place this formula. 

        =CONCATENATE(SMALL(B2:D2,1),SMALL(B2:D2,2),SMALL(B2:D2,3))

         

         

        L ttaL   T

          Pogo's avatar - wfl
          NC
          United States
          Member #29378
          January 1, 2006
          552 Posts
          Offline
          Posted: July 10, 2006, 11:31 pm - IP Logged

          Here's your answer buddy... I hope it helps you in your quest:

           

           Number100's10's1's0123456789
          7/3/2006320321011000000
          7/4/20069369360001001001
          7/5/2006100102100000000
          7/6/20068218210110000010
          7/7/2006490491000100001

           

           

          LottoChica23 already showed you how to break the numbers down individually, so here's your equation you need. I made it for "F2" & then copied & pasted it into the rest of the cells as needed.

          =(COUNTIF($C2,F$1)+COUNTIF($D2,F$1)+COUNTIF($E2,F$1))

          -C2 is 0 in the 100's column, D2 is 3, E2 is 2

          -F1 through O1 are just representative numbers we're going to test our numbers against

          -F2 through O2 is where you copy and paste this formula to & as you notice I locked down the appropriate column aand/or row to prevent miscalculations as you copy & paste it through out the rest of your spreadsheet.

          -Finally, you can go to the "Format" menu & use conditional formatting to highlight any cells greater than 0 as I did with the light green cells...

          Again, I hope this helps you M8 & does what you want it to. Send us a reply when you crack "The Code"

          ;-) Pogo

            Virginia
            United States
            Member #41886
            June 24, 2006
            500 Posts
            Offline
            Posted: July 12, 2006, 11:33 pm - IP Logged

            Bryan, can you send me when you are finished  those two numbers came out in Va 639 then the next night 128 came out this  tool would be very helpful. I need lessons on excel also my computer has Quattro Pro and I really don't know how to set up anything.

            Thanks

              Fibonacci's avatar - Lottery-050.jpg
              New York, NY
              United States
              Member #39471
              May 16, 2006
              2698 Posts
              Offline
              Posted: July 13, 2006, 7:46 am - IP Logged

              Lottachiaca and Lottalott thanks for these Excel tips. Great info. I use Excel a lot (every day) but I have learned more on LP than the books i read and courses I took.

              $$$

                Avatar
                Richmond/Virginia
                United States
                Member #14154
                April 21, 2005
                20 Posts
                Offline
                Posted: July 13, 2006, 12:22 pm - IP Logged

                How can you write an Excel formulas, that will calculate what numbers are coming more frequently than others in this sheet, with/out counting each column yourself? Would possibly make choosing your 3 numbers to play easier.

                  LottoChica23's avatar - Aquarius
                  Fughedaboutit (NY)
                  United States
                  Member #8160
                  October 26, 2004
                  6777 Posts
                  Offline
                  Posted: July 14, 2006, 8:57 pm - IP Logged

                  You're welcome fibonacci.  In addition, when you have a number such as 095 and the leading zero does not appear in the cell, you can fix this by using a custom format (Format--> Cells--> Custom) of 000 to fix that problem. After doing this, all of your leading zeroes will show up.

                  Matrix Chart Instructions--> http://www.lotterypost.com/thread/191818 

                  check out mysticwomyn Announcers --> http://www.lotterypost.com/thread/140695/673306

                  You can judge the integrity of a man by the way he treats those that can do nothing for him...Thumbs Up

                    Bryan's avatar - Lottery-002.jpg
                    Mid-Missouri
                    United States
                    Member #644
                    August 31, 2002
                    4271 Posts
                    Offline
                    Posted: July 14, 2006, 9:15 pm - IP Logged

                    Thanks LC23,

                    I was wondering about that. I got to show up with formatting the cell to text. I didn't know if that would have any adverse effect or not. But the 0 stayed when I did that and that is all I was after...

                    Thanks to everyone for all the ideas and formulas.

                    It is very helpful,

                    Bryan  :)

                      Pogo's avatar - wfl
                      NC
                      United States
                      Member #29378
                      January 1, 2006
                      552 Posts
                      Offline
                      Posted: July 14, 2006, 11:15 pm - IP Logged

                      Bryan, it would be a good idea to reformat your numbers like LottoChica is saying, because having them in a text format will prevent them from showing up correctly when you decide to use formulas and the such. But if your not having problems with them formatted as text - Rock On!

                      Thanks for the tip on the leading 0 LottoChica - always bugged me that I didn't have a perfectly lined up columns of numbers...
                       
                       Later, Pogo

                        LottoChica23's avatar - Aquarius
                        Fughedaboutit (NY)
                        United States
                        Member #8160
                        October 26, 2004
                        6777 Posts
                        Offline
                        Posted: July 16, 2006, 8:28 am - IP Logged

                        Bryan, it would be a good idea to reformat your numbers like LottoChica is saying, because having them in a text format will prevent them from showing up correctly when you decide to use formulas and the such. But if your not having problems with them formatted as text - Rock On!

                        Thanks for the tip on the leading 0 LottoChica - always bugged me that I didn't have a perfectly lined up columns of numbers...
                         
                         Later, Pogo

                        You're welcome Pogo, you learn something new every day. I'm still learning a few excel tricks here and there.

                        I remember back in the day when Excel used to be called Lotus 123, my has it grown up! (That may be before some folk's time... LOL)

                        Matrix Chart Instructions--> http://www.lotterypost.com/thread/191818 

                        check out mysticwomyn Announcers --> http://www.lotterypost.com/thread/140695/673306

                        You can judge the integrity of a man by the way he treats those that can do nothing for him...Thumbs Up

                          LottoChica23's avatar - Aquarius
                          Fughedaboutit (NY)
                          United States
                          Member #8160
                          October 26, 2004
                          6777 Posts
                          Offline
                          Posted: July 16, 2006, 8:35 am - IP Logged

                          Thanks LC23,

                          I was wondering about that. I got to show up with formatting the cell to text. I didn't know if that would have any adverse effect or not. But the 0 stayed when I did that and that is all I was after...

                          Thanks to everyone for all the ideas and formulas.

                          It is very helpful,

                          Bryan  :)

                          You're welcome.  I know what you mean, I had that problem one day too and I knew there had to be a way to fix it.  Pogo is correct in saying that you may run into problems performing arithmetic computations on cells formatted as text. You will get unpredictable results.

                          Good luck with your worksheet and many hits to you.  Thumbs Up

                          Matrix Chart Instructions--> http://www.lotterypost.com/thread/191818 

                          check out mysticwomyn Announcers --> http://www.lotterypost.com/thread/140695/673306

                          You can judge the integrity of a man by the way he treats those that can do nothing for him...Thumbs Up

                            Raven62's avatar - binary
                            New Jersey
                            United States
                            Member #17843
                            June 28, 2005
                            51098 Posts
                            Offline
                            Posted: July 16, 2006, 8:51 am - IP Logged

                            Bryan, it would be a good idea to reformat your numbers like LottoChica is saying, because having them in a text format will prevent them from showing up correctly when you decide to use formulas and the such. But if your not having problems with them formatted as text - Rock On!

                            Thanks for the tip on the leading 0 LottoChica - always bugged me that I didn't have a perfectly lined up columns of numbers...
                             
                             Later, Pogo

                            You're welcome Pogo, you learn something new every day. I'm still learning a few excel tricks here and there.

                            I remember back in the day when Excel used to be called Lotus 123, my has it grown up! (That may be before some folk's time... LOL)

                            LOL! Lotus 123 is alive and well!

                              Avatar
                              Toronto
                              Canada
                              Member #5142
                              June 20, 2004
                              47 Posts
                              Offline
                              Posted: July 16, 2006, 9:02 am - IP Logged

                              Here are a couple of websites that I have found useful for excel ...the second of which deals with lottery information....and turn up your speakers for the 1st one.

                               

                              http://www.datapigtechnologies.com/ExcelMain.htm

                              http://www.mrexcel.com/tip092.shtml

                               

                              Some days I spell luck with an "F" ©2005