Welcome Guest
Log In | Register )
You last visited December 5, 2016, 11:30 am
All times shown are
Eastern Time (GMT-5:00)

need help from the excel gurus...

Topic closed. 9 replies. Last post 11 years ago by time*treat.

Page 1 of 1
PrintE-mailLink
hypersoniq's avatar - 8ball
Pennsylvania
United States
Member #1340
April 6, 2003
2450 Posts
Offline
Posted: January 22, 2006, 2:58 pm - IP Logged

I am trying to find some way of getting a line to draw on a worksheet connecting numbers in a range based on the results of a cell

for example,

cell A1 contains 5... cells B1-L1 contain 0-9

Cell A2 contains 8... Cells B2-L2 contain 0-9

I want to insert a line that has one endpoint centered in the cell G1 and the other endpoint in the cell J2

I would like to automate chief's bell curve concept by adding lines

 

Playing more than one ticket per game is betting against yourself.

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

    Over my head....

    Way over

    L ttaL   T

      time*treat's avatar - radar

      United States
      Member #13130
      March 30, 2005
      2171 Posts
      Offline
      Posted: January 23, 2006, 11:09 am - IP Logged

      I wound up with 0 in B1 and 10 in L1. As far as the arrows ~ the way they're drawn from VBA, it looks like you will have to determine/set the width of the cells as part of your calculation. I won't say it can't be done as part of a regular spreadsheet formula, but I did a "record macro", and toyed with the values there, then reran.

       

      In neo-conned Amerika, bank robs you.
      Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

        hypersoniq's avatar - 8ball
        Pennsylvania
        United States
        Member #1340
        April 6, 2003
        2450 Posts
        Offline
        Posted: January 23, 2006, 6:03 pm - IP Logged

        thanks for the tip... macro/vba would be fine... I'll try that :-)

        Playing more than one ticket per game is betting against yourself.

          time*treat's avatar - radar

          United States
          Member #13130
          March 30, 2005
          2171 Posts
          Offline
          Posted: January 24, 2006, 12:29 am - IP Logged

          Poke for hyper...

          Sub line_maker()
            Dim std__col, std__row
            Dim base__row As Double
            Dim in__row As Double, out__row As Double
            Dim in__col As Double, out__col As Double
            Dim in__val As Double, out__val As Double
              'cell width = 8.43
              'cell height =10.5
              'first 11 rows'
             
              std__col = 48.75
              std__row = 10.5
              ActiveSheet.Lines.Delete 'deletes all lines'
             
              For base__row = 1 To 10
                in__val = Cells(base__row, 1).Value
                out__val = Cells(base__row + 1, 1).Value
               
                in__col = std__col * (1.5 + in__val)
                out__col = std__col * (1.5 + out__val)
                in__row = std__row * (base__row - 0.5)
                out__row = std__row * (base__row + 0.5)
               
                ActiveSheet.Shapes.AddLine(in__col, in__row, out__col, out__row).Select
              Next base__row
          End Sub 

          I'm getting as bad as Lotta... gotta write them routines no matter what. Crazy Yes Nod

          In neo-conned Amerika, bank robs you.
          Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

            hypersoniq's avatar - 8ball
            Pennsylvania
            United States
            Member #1340
            April 6, 2003
            2450 Posts
            Offline
            Posted: January 24, 2006, 5:46 am - IP Logged

            thanks!

            i had gotten as far as

                ActiveSheet.Shapes.AddConnector(msoConnectorStraight, X1, Y1, X2, Y2).Select

            but

              ActiveSheet.Shapes.AddLine(X1, Y1, X2, Y2).Select

            seems much simpler.

            Playing more than one ticket per game is betting against yourself.

              cahaba's avatar - moon
              Birmingham
              United States
              Member #18850
              July 21, 2005
              82 Posts
              Offline
              Posted: January 25, 2006, 9:48 pm - IP Logged

              sonic-

              Are you after a graph? 

              "Life is like a box of chocolates...you can speculate on what you're going to get through trial and error"




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

                "I'm getting as bad as Lotta... gotta write them routines no matter what. Crazy Yes Nod"

                 

                Who me???  TwitchTwitch

                L ttaL   T

                  hypersoniq's avatar - 8ball
                  Pennsylvania
                  United States
                  Member #1340
                  April 6, 2003
                  2450 Posts
                  Offline
                  Posted: January 27, 2006, 8:17 pm - IP Logged

                  sonic-

                  Are you after a graph? 

                  sort of..

                  I have a "grid" for numbers

                  it spans 55 columns 1-55 in the cells from left to right

                  as it stands now, I can enter a powerball drawing into a set of cells, and on the grid... white balls are indicated by yellow highlighting, powerball is indicated in  red highlighting, and the "coincident match" (powerball is same as a white ball) turns the cell inthe grid black w/ white text.

                  this is basically an entirely automatic way to produce the late kbcherokee's excel "bell curve". (he did it by hand)

                  I will be creating named ranges to handle WB1-WB5 and PB, what I want is the ability to read the numbers from the results array, then connect the corresponding points in the "grid" with different colored lines.

                  I had an old sheet where I did this by hand for a few hundred draws, tedious work to be sure, but of all the systems, it seemed to have the best potential. I let the sheet get behind because of the tedious update procedure (I had 1 sheet for each position WB1 thru PB and another that had all of them on it). I would like to re-visit the "bell curve" concept, only this time spend more of my time on interpretation rather than updating.

                  drawing lines by hand is a bit sloppy, and "snap to grid" attempts to connectitto a cell border line, no good for the project at hand. I am hoping to find a way to calculate the x,y coordinates of the cell center and connect the points with lines using a macro or vba code.

                  I have done this for PB data in AutoCAD, but that lacks the spreadsheet features needed (and it was also tedious). What I have gained from the AutoCAD experiment is a trig formula for excel that looks at the last 2 draws and calculates the resultant angle based on their grid position.

                  for any 2 consecutive draws (positional, i.e. WB1 to WB1)...

                  A.) LSV (line slope vector) this is the approach pattern, either SW, S or SE. Southwest (SW) represents the first number being lower than the second... South (S) is a repeat number... SouthEast (SE) is the first number being larger than the second...

                  LSV formula

                  =IF(C2=C4,"S",IF(C2<C4,"SE","SW"))

                  B.) Length of line (how long is the line in "grid units"?

                  =IF(C2<C4,(C4-C2),IF(C2>C4,(C2-C4),1))

                  C.) Angle of deviation from Horizontal

                  South = 90 degrees
                  East = 0 (SE=+)
                  West = 0 (SW=-)

                  =IF(D3="SE",(ATAN(1/E3)*180/PI()),IF(D3="SW",(-1*(ATAN(1/E3)*180/PI())),0))

                  D.) Angle of deviation from Vertical

                  South = zero degrees
                  East = 90 degrees
                  West = -90 degrees

                  =IF(D3="SE",90-F3,IF(D3="SW",(-90-F3),0))

                  I have no real idea where I am going with this just yet, but I would like to be able to see the lines corresponding to the data I am looking at. I am basically trying to move outside the traditional lottery system box and employ some trig and maybe calculus to the data to see what emerges (other than chaos). My end goal will be to take the bell curve idea and refine and tweak it into a more accurate number picker (chief used the bell curve in MM challenges)

                  any help appreciated ;-)

                  Playing more than one ticket per game is betting against yourself.

                    time*treat's avatar - radar

                    United States
                    Member #13130
                    March 30, 2005
                    2171 Posts
                    Offline
                    Posted: January 27, 2006, 10:14 pm - IP Logged

                    If all else fails, hyper's secured a solid future career as a map maker Big Grin

                    In neo-conned Amerika, bank robs you.
                    Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.