United States
Member #13,130
March 30, 2005
2,171 Posts
Offline
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.
United States
Member #13,130
March 30, 2005
2,171 Posts
Offline
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
Pennsylvania United States
Member #1,340
April 6, 2003
2,577 Posts
Offline
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=-)
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)