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

Excel Help

Topic closed. 28 replies. Last post 4 years ago by SergeM.

Page 1 of 2
PrintE-mailLink

United States
Member #124493
March 14, 2012
7023 Posts
Offline
Posted: January 5, 2013, 2:13 pm - IP Logged

Ok this is kind of a beginners question, I have tried to tackle the problem, but with only moderate success.

I do have some other ideas, such as outputting to another sheet, then re-inputting, but I figured, If anybody can do it easier, then I would gladly like to "cheat" off them.

Basically where the  question mark sign is I would like the output of odd/even in the format of

3O/2E, 2O/3E, etc.

So here is my table.

1/4/13

6

9

24

25

34

2

14

0

8

?

 6 9 24 25 34 are the results,  1/4/13, the date,  2 14 0 8 are the spaces in between.

Not a impossible problem, I just have been putting it off until I get a nice flash of insight, but it doesn't seem to want to come.

Thanks in advance!

    Greenfox's avatar - IMAG01562
    Burnsville
    United States
    Member #107244
    March 4, 2011
    853 Posts
    Offline
    Posted: January 5, 2013, 2:42 pm - IP Logged

    Hey LottoBoner,

    I don't know how to do this, but love the idea. I think i see where your going with it.

    For my stuff when i couldn't figure something out myself, i went to excelforum. The people there helped me everytime. And fast. One guy was even going to put a full day into my problem. I wouldn't let him though. But if you run into a wall with something, they are real nice there also.

    But i really like this!!! Especially the spaces inbetween versus the addition or subtraction. Very nice!!! Very, very nice idea!!!

    You can't steal second and keep your foot on FIRST!!!

    “Strength does not come from winning. Your struggles develop your strengths.
    When you go through hardships and decide not to surrender, that is strength”.

    -Arnold (Ahnald) Schwarzenegger-


      United States
      Member #124493
      March 14, 2012
      7023 Posts
      Offline
      Posted: January 5, 2013, 3:07 pm - IP Logged

      Hey LottoBoner,

      I don't know how to do this, but love the idea. I think i see where your going with it.

      For my stuff when i couldn't figure something out myself, i went to excelforum. The people there helped me everytime. And fast. One guy was even going to put a full day into my problem. I wouldn't let him though. But if you run into a wall with something, they are real nice there also.

      But i really like this!!! Especially the spaces inbetween versus the addition or subtraction. Very nice!!! Very, very nice idea!!!

      Thanks Greenfox!

      I will definitely check out that excel forum.

      Yes the space in between helps, if you can focus on a spacing you want or one that you believe is due.  Of course it doesn't really purely apply to pick 3 since there are no "real" spacings between numbers because of the three different machines.

      Yeah I like spacing instead of difference because I love to include the almighty 0.

      ÔΗΜHippy

        Greenfox's avatar - IMAG01562
        Burnsville
        United States
        Member #107244
        March 4, 2011
        853 Posts
        Offline
        Posted: January 5, 2013, 3:15 pm - IP Logged

        Thanks Greenfox!

        I will definitely check out that excel forum.

        Yes the space in between helps, if you can focus on a spacing you want or one that you believe is due.  Of course it doesn't really purely apply to pick 3 since there are no "real" spacings between numbers because of the three different machines.

        Yeah I like spacing instead of difference because I love to include the almighty 0.

        ÔΗΜHippy

        They've sure helped me out a plenty.

        It's a great idea. It shows it in a completely different way that's for sure. And yes, it does bring the 0 into effect. In more ways than just the 24 to 25 from what i just saw.

        Very nice indeed!!!!

        You can't steal second and keep your foot on FIRST!!!

        “Strength does not come from winning. Your struggles develop your strengths.
        When you go through hardships and decide not to surrender, that is strength”.

        -Arnold (Ahnald) Schwarzenegger-


          United States
          Member #124493
          March 14, 2012
          7023 Posts
          Offline
          Posted: January 5, 2013, 3:33 pm - IP Logged

          They've sure helped me out a plenty.

          It's a great idea. It shows it in a completely different way that's for sure. And yes, it does bring the 0 into effect. In more ways than just the 24 to 25 from what i just saw.

          Very nice indeed!!!!

          Thumbs UpType

            SergeM's avatar - slow icon.png
            Economy class
            Belgium
            Member #123700
            February 27, 2012
            4035 Posts
            Offline
            Posted: January 5, 2013, 4:20 pm - IP Logged

            Ok this is kind of a beginners question, I have tried to tackle the problem, but with only moderate success.

            I do have some other ideas, such as outputting to another sheet, then re-inputting, but I figured, If anybody can do it easier, then I would gladly like to "cheat" off them.

            Basically where the  question mark sign is I would like the output of odd/even in the format of

            3O/2E, 2O/3E, etc.

            So here is my table.

            1/4/13

            6

            9

            24

            25

            34

            2

            14

            0

            8

            ?

             6 9 24 25 34 are the results,  1/4/13, the date,  2 14 0 8 are the spaces in between.

            Not a impossible problem, I just have been putting it off until I get a nice flash of insight, but it doesn't seem to want to come.

            Thanks in advance!

            There are indeed several ways.Maybe you should do them all.

              notmyday's avatar - 8ball
              florida
              United States
              Member #136668
              December 16, 2012
              331 Posts
              Offline
              Posted: January 5, 2013, 4:26 pm - IP Logged

              7 goes in the question mark

                Avatar
                Pennsylvania
                United States
                Member #109275
                April 9, 2011
                1495 Posts
                Offline
                Posted: January 5, 2013, 5:22 pm - IP Logged

                Most likely going to have to be done in VBA script and should be pretty simple. I can write the code if you feel confident implementing it. Will it always be 5 #'s and do you want the VBA code to also figure out the spaces between each #?. Do you also want the odd/even of just the lotto #'s or the spaces in between also?


                  United States
                  Member #124493
                  March 14, 2012
                  7023 Posts
                  Offline
                  Posted: January 5, 2013, 5:31 pm - IP Logged

                  Most likely going to have to be done in VBA script and should be pretty simple. I can write the code if you feel confident implementing it. Will it always be 5 #'s and do you want the VBA code to also figure out the spaces between each #?. Do you also want the odd/even of just the lotto #'s or the spaces in between also?

                  Thanks omiller.

                  Well the spacing is easy.  just (CELLB - CellA )-1

                  I am not confident in VBA yet.  My trouble is that my sheet is already a mess, then when i go to VBA code i am likeScaredSkepticalEekShifty

                  If i was to build from bottom up, then I may be able to catch on.

                  I do need to build from bottom up again anyway, so I can prioritize my paramaters and variables.

                  Yes always five numbers for Pick5, but i also play pick6 so same problem

                    Avatar
                    Pennsylvania
                    United States
                    Member #109275
                    April 9, 2011
                    1495 Posts
                    Offline
                    Posted: January 5, 2013, 5:50 pm - IP Logged

                    Thanks omiller.

                    Well the spacing is easy.  just (CELLB - CellA )-1

                    I am not confident in VBA yet.  My trouble is that my sheet is already a mess, then when i go to VBA code i am likeScaredSkepticalEekShifty

                    If i was to build from bottom up, then I may be able to catch on.

                    I do need to build from bottom up again anyway, so I can prioritize my paramaters and variables.

                    Yes always five numbers for Pick5, but i also play pick6 so same problem

                    Here is the VBA code for odds/even assuming we are talking about entries in A1 and result in B1. To do multiple rows, we would just implement it into a DO loop with variables til it reached the end and factor in multiple columns. Will gladly help if you want. Honestly shouldnt be more than about 10-20 lines of code. 

                    Forgot to add the counter. This would then allow you to post the odd/even results at the end of the row. We can go 1 step further and concatenate it to a 2E/3O like you mentioned into a single cell. 

                     

                    dim odd as long, even as long 

                    If (Cells(1, 1) And 1) = 1 Then
                    Cells(1, 2) = "odd"

                    odd = odd +1
                    Else
                    Cells(1, 2) = "even"

                    even = even +1
                    End If

                      Avatar
                      bgonçalves
                      Brasil
                      Member #92564
                      June 9, 2010
                      2122 Posts
                      Offline
                      Posted: January 6, 2013, 5:21 am - IP Logged

                      Hello, omiller, can also add, high / low = 0,1,2,3,4 low
                        High = 5,6,7,8,9
                      And also jumps

                        Avatar

                        United States
                        Member #41846
                        June 23, 2006
                        458 Posts
                        Offline
                        Posted: January 6, 2013, 6:02 am - IP Logged

                        Ok this is kind of a beginners question, I have tried to tackle the problem, but with only moderate success.

                        I do have some other ideas, such as outputting to another sheet, then re-inputting, but I figured, If anybody can do it easier, then I would gladly like to "cheat" off them.

                        Basically where the  question mark sign is I would like the output of odd/even in the format of

                        3O/2E, 2O/3E, etc.

                        So here is my table.

                        1/4/13

                        6

                        9

                        24

                        25

                        34

                        2

                        14

                        0

                        8

                        ?

                         6 9 24 25 34 are the results,  1/4/13, the date,  2 14 0 8 are the spaces in between.

                        Not a impossible problem, I just have been putting it off until I get a nice flash of insight, but it doesn't seem to want to come.

                        Thanks in advance!

                        to get exactly what you asked for would require some VBA.  If output in two columns would be acceptable you can combine sumproduct and mod functions to count even and odd.   if we assume that the 6 is cell b1 and the 34 is cell f1 and the question mark is cell k1 and it will be count of even # l1 will be count of odd# the formula in k1 would be:

                        =sumproduct((mod(b1:f1,2)=0)+0)                count even # adjust b1:f1 to suit your range

                        in l1

                        =sumproduct((mod(b1:f1,2)<>0)+0             count odd # adjust as above

                        copy and paste down the page as needed

                          Avatar
                          Pennsylvania
                          United States
                          Member #109275
                          April 9, 2011
                          1495 Posts
                          Offline
                          Posted: January 6, 2013, 9:53 am - IP Logged

                          Hello, omiller, can also add, high / low = 0,1,2,3,4 low
                            High = 5,6,7,8,9
                          And also jumps

                          What do you mean by jumps? Below is for high low

                           

                          Yeah. Would just add

                          If Cells(1,1) < 5 then

                          low = low +1

                          else

                          high = high +1

                          I like to use counters and loops and would look like this.

                           

                          Sub highligh()
                          Dim high As Long, low As Long
                          Dim row As Long, column As Long
                          row = 1: column = 1 '(if you have a header row change to 2 or if your #'s start in column 2, then change column to 2

                          Do Until Cells(row, 1) = "" ' this is the main loop to run until it doesn't see anything in column A
                          high = 0: low = 0

                          Do Until Cells(row, column) = "" ' this one to hit all #'s on a row

                          If Cells(row, column) < 5 Then
                          low = low + 1
                          Else
                          high = high + 1
                          End If

                          column = column + 1 ' (assuming you have multiple #'s per row)


                          Loop ' loops for the column

                          Cells(row, 10) = high ' assuming column 10 is where you want results.

                          Cells(row, 11) = low ' same for column 11

                          row = row + 1
                          column = 1 ' make this match the column # at the beginning

                          Loop ' loops for the row


                          End Sub

                            Avatar
                            bgonçalves
                            Brasil
                            Member #92564
                            June 9, 2010
                            2122 Posts
                            Offline
                            Posted: January 6, 2013, 11:00 am - IP Logged

                            Hello, omiller, heels, are the distances between the digits example
                              The digit 8, the base is 1 draw, the digit 8 has once again given after 5 draws
                            After 12 then draws 3 .... etc then jump by position or column
                              The eighth digit is = 5,8,3 ... etc, may further 0-10 low heel 10-20 heeled
                              The jump is to see each upright position, and not by any of the three digits
                            Sure will have to do statistics for each position in the vertical pick3


                              United States
                              Member #124493
                              March 14, 2012
                              7023 Posts
                              Offline
                              Posted: January 6, 2013, 3:15 pm - IP Logged

                              Hello, omiller, heels, are the distances between the digits example
                                The digit 8, the base is 1 draw, the digit 8 has once again given after 5 draws
                              After 12 then draws 3 .... etc then jump by position or column
                                The eighth digit is = 5,8,3 ... etc, may further 0-10 low heel 10-20 heeled
                                The jump is to see each upright position, and not by any of the three digits
                              Sure will have to do statistics for each position in the vertical pick3

                              Thanks everybody for posting.

                              The VBA is a little light years ahead of me, so i started reading that book that somebody posted in learning VBA.

                              The author says the meat and potatoes of the VBA is in chapter 11.

                              The book has 900 pages, and I only just started reading and am in chapter 1, page 45, so I will see how this develops.

                              Right now my non VBA formula is analyzing one cell that counts the even occurences.

                              So my current formula is,

                              Well I started with this and then I got stuck.

                              =IF(DJ1488=2,"3O/2E",0)

                              However the following formula got an error value.

                              =IF(DJ1489=2,"3O/2E",0)+IF(DJ1489=3,"2O/3E",0)

                              so thats when I gave up.