How to Compare Two Methods of Creating a Spherical Helix

Tutorial #1

1. How to Compare Two Methods of Creating a Spherical Helix Picture 1
   
   
   For this article, create a new workbook and create the following worksheets: Data, later copy it to become Data (2), Chart1, Chart2 and Saves.
2. Set Your Preferences: Open Preferences in the Excel menu and follow the directions below for each tab/icon.

   1. In General, set R1C1 to Off and select Show the 10 Most Recent Documents .
   2. In Edit, set all the first options to checked except Automatically Convert Date System . Set Display number of decimal places to blank (as integers are preferred). Preserve the display of dates and set 30 for 21st century cutoff.
   3. In View, click on show Formula Bar and Status Bar and hover for comments of all Objects . Check Show gridlines and set all boxes below that to auto or checked.
   4. In Chart, allow Show chart names and set Data markers on hover and leave the rest unchecked for now.
   5. In Calculation, Make sure Automatically and Calculate before save are checked. Set max change to .000,000,000,000,01 without commas as goal-seeking is done a lot. Check Save external link values and Use 1904 system
   6. In Error checking, check all the options.
   7. In Save, select Save preview picture with new files and Save Autorecover after 5 minutes
   8. In Ribbon, keep all of them checked except Hide group titles and Developer .
3. It helps by placing the cursor at cell A16 and doing Freeze Panes. Edit Go To cell range A1:H2912 and Format Cells Number Number Decimal Places 4, Font Size 9 or 10,
4. How to Compare Two Methods of Creating a Spherical Helix Picture 2
   Enter the upper Defined Name Variables Section on the Data worksheet (here's a picture):

   1. Cell A1: enter AjRows
   2. B1: enter GM (for Golden Mean)
   3. C1: enter Factor1
   4. D1: enter KEY
   5. E1: enter Thickness
   6. A2: 2880
   7. B2: "=(-(1-SQRT(5))/2)" w/o quotes and Format Cell Number Number Decimal Places 8
   8. C2: "=VLOOKUP(ABS(Spheroids),Looker,IF(Spheroids<=24,2,3))"
   9. D2: "=IF(Spheroids<=24, Spheroids*VLOOKUP(Spheroids,LOOKER2,2),Spheroids)"
   10. E2: .5 and Format Cell Number Number Decimal Places 1. Insert New Comment "This controls the ring size." Format cell Fill yellow, Border red bold outline.
   11. Select cell range A1:E2 and Insert Name Create Names in Top Row, OK.
   12. F2: "=2^6*3*PI()*103" w/o quotes, which equates to Tip's value.
   13. A3:: Tip
   14. B3: Base
   15. C3: Spheroids
   16. D3: ShrinkExpand
   17. E3 103 and Format Cell Number Number Decimal Places 0. Format cell Fill yellow, border red bold outline. Insert New Comment "Primes that work up to 107 are: (1),13,17,(29),(31),43,47,(59),(61),73,77, (89),(91),103,107 and -1 turns the other way … as does 107, with parentheticals having only half the turns or so ...823 and 827 are good, as are 1037, 4813.  This is the pattern: 13, 17 + 30 = 43,47 + 30 = 73, 77 + 30 = 103, 107 ... 13, 17 - 30 = -17, -13 which are both good!! So ±13±30n or ±17±30n, n={0,1,2,3,...}. Other primes and non-primes yield interesting results, but they're not spheres."
   18. F3: Prime
   19. Select cell range E3:F3 and Insert Name Create Names in Right Column, OK.
   20. A4: "=2^6*3*PI()*Prime" w/o quotes
   21. B4: "=2^4*Prime" w/o quotes
   22. C4: 24 and Format Cell Fill Light sky blue.
   23. D4: .5 and Format Cell Fill yellow
   24. Select cell range A3:D4 and Insert Name Create Names in Top Row, OK.
5. Enter the column headings of rows 4 and 5:

   1. A5: Base t
   2. B5: constant c
   3. C5: Cos
   4. D5: Sin
   5. E4 and F4: METHOD 1
   6. E5: Main X
   7. F5: Main Y
   8. G4: M1-M2 and G5 DIFF and copy G4:G5 to H4:H5.
   9. Command + Select cell range A1:H1, A3:D3, A5:H5, E4:H4 and F3:H3 and Format Cell Fill White Font Electric Purple Bold. Select cell range A5LH5 and Format Cells underline. Select cell range C4, E2:E3, F2, E4:H5 and Format Cell Font Size 14.
6. Enter the column formulas

   1. A6: "=IF(ODD(Spheroids)=Spheroids,0,Tip)" w/o quotes and Format Fill light rose.
   2. Edit Go To cell range A7:A2886 and enter to A7 w/o quotes the formula "=((A6+(-Tip*2)/(AjRows)))" and Edit Fill Down. The bottom entry should match the top one, except that it's negative.
   3. B6: "=(Base*24/Spheroids)" w/o quotes and Format cell Fill light rose.
   4. Edit Go To cell range B7:B2886 and enter to B7 w/o quotes the formula "=A6" and Edit Fill Down.
   5. Edit Go To cell range C6:C2886 and enter to C6 w/o quotes the formula "=Thickness*Spheroids/KEY*(COS((ROW()-6)*PI()/180*Factor1))" and Edit Fill Down.
   6. Edit Go To cell range D6:D2886 and enter to D6 w/o quotes the formula "=Thickness*Spheroids/KEY*(SIN((ROW()-6)*PI()/180*Factor1))" and Edit Fill Down.
   7. Edit Go To cell range E6:E2886 and enter to E6 w/o quotes the formula "=((SIN(A6/(B6*2))*GM*COS(A6)*GM*(COS(A6/(B6*2)))*GM)+C6)/ShrinkExpand" and Edit Fill Down.
   8. Edit Go To cell range F6:F2886 and enter to F6 w/o quotes the formula "=((SIN(A6/(B6*2))*GM*SIN(A6)*GM*(COS(A6/(B6*2)))*GM)+D6)/ShrinkExpand" and Edit Fill Down.
   9. The results in C2886:F2886 should match C6:F6.
7. Create the Lookup Tables

   1. Enter LOOKER into cell K5. Edit Go To cell range K6:K2180 and enter 1 into cell K6. Do Edit Fill Series Column Linear Step Value = 1, OK. Edit Go To cell range L6:L180 and enter .125 into L6 and Edit Fill Down. Edit Go To cell range M5:M180 and enter 0 to cell M5. Do Edit Fill Series Column Linear Step Value =   0.004166667, OK. Edit Go To cell range K6:M180 and Insert Name Define Name LOOKER to cell range $K$6:$M$180. Format Border red bold outline..
   2. Enter LOOKER2 into cell O5. Edit Go To cell range O6:O29 and enter 1 into cell O6. Do Edit Fill Series Column Linear Step Value = 1, OK. Edit Go To cell range P6:P29 and enter the following values from top to bottom: P6: 24, 16, 8, 4, =PI(), =PI(), =PI(), 2 from P13 to P21 and 1 from P22 to P29 via Edit Fill Down. Edit Go To cell range O6:P29 and Insert Name Define Name LOOKER2 to cell range $O$6:$P$29. Format Fill yellow and Border red bold outline.

Explanatory Charts, Diagrams, Photos

1. (dependent upon the tutorial data above)

1. Create the Method1 full Chart

   1. Edit Go To cell range E6:F2886 and select either Charts from the Ribbon or Chart Wizard. Select All/Other, and scroll down to  Scatter Smooth Lined Scatter. If in Chart Wizard, a new Chart sheet will open for you, otherwise, a Chart will land atop the data to cut or copy and paste to the Chart worksheet's upper left hand corner. Hover over the lower right corner until the cursor become a double-headed arrow, then grab the corner and pull it down and to the right to form a square. Click in the Plot Area and select Chart Layout from the Ribbon and get rid of the axes, grid lines and legend. Resize the chart to be 5.55" x 5.55" and drag it to the center of the page. Select Chart Layout Series 1 and choose Line Color blue, Weight 1. Here's the chart:
      How to Compare Two Methods of Creating a Spherical Helix Picture 3
2. Create the Method1 partial Chart

   1. Edit Go To cell range E6:F246 (the first two spheroids) and select either Charts from the Ribbon or Chart Wizard. Select All/Other, and scroll down to  Scatter Smooth Lined Scatter. If in Chart Wizard, a new Chart sheet will open for you, otherwise, a Chart will land atop the data. Move it to the right and format the scales as follows. Horizontal Minimum=.65, Maximum=1.35, Major Unit = .35, Vertical axis crosses at .65. Vertical: Minimum = -1.25, Maximum=.625, Major unit=.075, Horizontal Axis crosses at 0.0 I realize this cuts a shade off the top and that's pertinent. The spheroids should appear as fairly good spheres once you size the graph to be W= 4.92" x L = 5.5". Select Chart Layout Series 1 and choose Line Color blue, Weight 1. Here's the chart:
      How to Compare Two Methods of Creating a Spherical Helix Picture 4

Tutorial #2

1. That completes the Method1 spreadsheet for now, except that I have Inserted a New Comment for important formulas I might overwrite, copying the formula from the formula bar into the new comment. To create the Method2 spreadsheet, do menuitem Edit Move or Copy Sheet, with the Create a copy box checked, before sheet Chart and after sheet Data, to the same workbook, make a copy of the Data worksheet just done and title it Data (2) if not done so automatically. I may refer to this worksheet as the Method2 worksheet, instead of Data(2) -- both terms refer to the same sheet.
2. Enter the Column Formulas for Data(2) that are different from Data:

   1. Cos: Edit Go To cell range C6:C2886 and enter to C6 w/o quotes the formula "=(COS((ROW()-6)*PI()/180*Factor1))" and Edit Fill Down. Done away with are Thickness and KEY.
   2. Sin: Edit Go To cell range D6:D2886 and enter to D6 w/o quotes the formula "=(SIN((ROW()-6)*PI()/180*Factor1))" and Edit Fill Down. Done away with are Thickness and KEY.
   3. The results in C2886:F2886 should match C6:F6.
3. Enter the Defined Variables Section that changes for Method2. Here is a picture:

   1. B1: Change GM to GM=Other
   2. B2: Enter .75 (a lucky guess on my part but .8 works, and .1 does not).
   3. A4: Enter "=Base*12*PI()" w/o quotes
   4. B4: Enter "=16*103"w/o quotes
   5. F3: Delete the word Prime. Copy F3 and paste it to D1:E2, then to E3.
   6. Command Select A1:F1, F2, A3:F3, E3:H3, A5:H5 and Format Font red. Select A5:H5 and Format underline. Command + Select F2, E4:H5 and do Font Size 14.
   7. G1: Enter 2880, the number of rows (approx).
   8. H1: Enter "=Spheroids" w/o quotes
   9. G2: Enter Rows/Sphere
   10. H2: Enter "=G1/H1" w/o quotes
4. Enter the Difference Column's formulas

   1. Select cell G6 and enter, w/o quotes, the formula "=Data!E6-'Data (2)'!E6" which very much depends upon what titles you've given your worksheets.
   2. Copy G6 and paste it to cell range G6:H2886.
   3. Select cell G2887 and enter w/o quotes "=SUM(G6:G2886)" and copy it and paste it to H2887. My results are 0 and 0 and yours should be too, though there are plenty of individual differences in the cells above. However, a MAX() formula informs one that the largest difference is .0251 for x and .025 for y.

Explanatory Chart - Tutorial #2

1. (dependent upon the tutorial data above)

1. Create the Chart for Data(2) / Method2

   1. Edit Go To cell range E6:F2886 on Data (2) and select either Charts from the Ribbon or Chart Wizard. Select All/Other, and scroll down to  Scatter Smooth Lined Scatter. If in Chart Wizard, a new Chart sheet will open for you, otherwise, a Chart will land atop the data to cut or copy and paste to the Chart worksheet's upper left hand corner. Hover over the lower right corner until the cursor become a double-headed arrow, then grab the corner and pull it down and to the right to form a square. Click in the Plot Area and select Chart Layout from the Ribbon and get rid of the axes, grid lines and legend. Resize the chart to be 5.55" x 5.55" and drag it to the center of the page. Select Chart Layout Series 1 and choose Line Color red, Weight 1. Here's the chart:
      How to Compare Two Methods of Creating a Spherical Helix Picture 5
2. Create the Method 2 Partial chart

   1. Edit Go To cell range E6:F246 on Data (2) (the first two spheroids) and select either Charts from the Ribbon or Chart Wizard. Select All/Other, and scroll down to  Scatter Smooth Lined Scatter. If in Chart Wizard, a new Chart sheet will open for you, otherwise, a Chart will land atop the data. Move it to the right and format the scales as follows. Horizontal Minimum=.65, Maximum=1.35, Major Unit = .35, Vertical axis crosses at .65. Vertical: Minimum = -1.25, Maximum=.625, Major unit=.075, Horizontal Axis crosses at 0.0 I realize this cuts a shade off the top and that's pertinent. The spheroids should appear as fairly good spheres once you size the graph to be W= 4.92" x L = 5.5". Select Chart Layout Series 1 and choose Line Color red, Weight 1. Here's the chart:
      How to Compare Two Methods of Creating a Spherical Helix Picture 6
3. Analyze the spheres into thirds (beginning, middle, end) and look for a pattern between the two methods:

   1. Select on the Data worksheet cell range E6:F:45 and do Format Cell Border red bold outline. In cell G45, enter "=SUM(E6:E45)-SUM('Data (2)'!E6:E45)". Copy it and paste it to cell H45 and select G45:H45 and Format Cell Border Border red outline.
   2. Copy E6:H45 and paste it to E46. Select E86 and paste it again. Select E126 and paste again.
   3. Command+Select cell range E46:F85, G85:H85 and Format Fill yellow and Border red bold outline. Command+Select cell range E86:F125, G125:H125 and Format Fill light blue and border red bold outline.
   4. Copy E6:H125 and paste to E127 and repeat this process until you've reached down to cell H2885, grabbing larger and larger chunks done to copy and paste as your go, bearing in mind that at 1445, you're at the midpoint. Outline the final row in bold red.
   5. F2887, aligned right: TOTAL DIFFERENCE. Enter to G2887 "=SUM(G6:G2886)" and copy and paste to to H2887. Totals = 0 and 0.
   6. Re-split the window above Total Difference, with room for 24 row entries beneath it. Do not freeze panes.
   7. E2888: Sphere Number
   8. F2888: Sphere Subsection
   9. H2888, aligned right, bold red text: THE PATTERN:
   10. E2889: Enter 1 and Format Cell Number Number Custom "00)" with quotes.
   11. E2892 and every 3rd cell below it until Sphere 8 is reached: Enter "=E2889+1" and copy this to every third cell beneath it.
   12. F2889: Enter 1 and Format Cell Custom as above, "00)". F2890: Enter 2. F2891: Enter 3. These are the the 3 sections of each sphere. Copy F2889 and Paste Special Format to cell range F2890:F2891. Copy F2889:F2891 and paste it 7 times beneath itself so that the first 8 spheres have their beginning, middle and end separated.
   13. G2889: Enter "=G45" by entering = and then locating the subtotal in the upper window in cell G45 and clicking on it. Do this for each such subtotal, separated by 40 rows, so you cannot just fill down.
   14. Observe THE PATTERN that emerges and how the the spheres are directionally shifted in terms of x and y back and forth.
4. GOAL SEEKING Y'S TO ZERO:

   1. I2891: Enter "=0-H2891" w/o quotes.
   2. Do menu item Tools Goal seek Set Cell I2891 To Value 0 By changing cell, and then click over to worksheet Data (2) cell B2 and select it, so you have 'Data (2)'!$B$2 as the By changing cell response, and click OK.
   3. Relocate to THE PATTERN at worksheet Data cell H2888 and notice that ALL the Y's have gone to zero, and perhaps all the X's as well. I've seen both occur.
   4. Check the all the charts and observe the differences in roundness, spread, etc.
   5. It may just be that 0.778674031976484 is a better overall factor to use than the Golden Mean.
   6. See below for the final charts.

Final Charts

1. GM=Other is 0.778674031976488 instead of .75 -- FINAL CHARTS:
2. How to Compare Two Methods of Creating a Spherical Helix Picture 7 GM Other has changed, Full Chart
   
   
   
3. How to Compare Two Methods of Creating a Spherical Helix Picture 8 GM Other has changed, Partial Chart
   
   
   
4. How to Compare Two Methods of Creating a Spherical Helix Picture 9 GM Other has changed, Data Summary
   
   
   
5. Prior Pattern with GM Other = .75 (first 8 spheres of the total 24)
   How to Compare Two Methods of Creating a Spherical Helix Picture 10
   
   
   
6. Now for something totally unexpected! I dropped the z-dimension totally out of the calculation for Main X and Main Y, i.e. Main X's formula is (on Data (2)): "=((SIN(A6/(B6*2))*GM*COS(A6)*GM)+C6)/ShrinkExpand" and Main Y's formula is: "=((SIN(A6/(B6*2))*GM*SIN(A6)*GM)+D6)/ShrinkExpand". And here are the results, which are amazing (GM=Other is set to 0.778674031976488):
   How to Compare Two Methods of Creating a Spherical Helix Picture 11
   
   
   
7. One half of the spheroids appear!
   How to Compare Two Methods of Creating a Spherical Helix Picture 12
   
   
   
8. At the top of the No Z Summary, one can see that the TOTAL DIFFERENCE is 0, 0!!! This is with one chart doing 24 spheroids and one doing only 12!!
   How to Compare Two Methods of Creating a Spherical Helix Picture 13
   
   
   
9. These results are, so far, totally mystifying ...

Explanation of the Basic Curve

1. Basically, to understand my work, you need to begin by grasping the following:

1. In Excel, if I let the x value of {x, y} Cartesian coordinate pairs of a graph equal the cosine of rows 0 to 360, and then I let the y values equal the sines of rows 0 to 360 -- I have just taken all those row numbers and treated them as angles, right? So the result is a circle.
2. In the Garthwaite Curve -- that ring of spiral balls you see me use all the time in many various ways -- I found out how to get 3-dimensionality out of Excel, by combining the z-dimension formula in with x and y (or in some other curves, just with y).
3. The formula for the "ball", formally called a "spherical helix", is short but a little difficult to explain, so I won't. Suffice it to say that it multiplies the sine by the cosine and again by a cosine, or the sine by the sine and then by the cosine, of various values -- be they constants or variables. Now the sine of an angle, given r=the hypotenuse, is y/r, and the cosine of an angle is x/r. If we let r=1, then sine = just the distance up the y axis and the cosine = just the distance along the horizontal x axis.
4. Imagine a triangle made by extending the hypotenuse at 30 degrees, then 45 degrees, then 60 degrees. At first, x is longer than y is tall. Then, at 45 degrees, both are equal. Then at 60 degrees, the roles are the exact reverse of what they were at 30 degrees and y is now taller than x is short by exactly the amount that x was longer than y at 30 degrees. So, therefore, in a circle, if the sine is long, the cosine is short and vice versa, or they're even. It's also possible that they measure 1 or 0, but those are the maximum and minimum values for a unit circle of radius r = the hypotenuse = 1.
5. And, for other curves, we are usually distorting the sine and/or cosine by adding/multiplying/subtracting or dividing it by something else.
6. Just when you think you understand, and you say to yourself, "Aha! Then the sine times the cosine produces a rectangle, the diagonal of which is the radius! I get it! I'm a genius!" ... it's not quite that simple, and yet it is also exactly that simple at the same time. It's just that the sine times the cosine do not define the endpoint of that diagonal when multiplied together, any more than 4*6 = 24 says anything about the point {4, 6} at the corner of the rectangle. Seems unfair, I know! I can sympathize, believe me. But you are a genius perhaps if you can agree that their multiplication as their values change produces the sequence (0, .25, .50, .25, 0, -.25, -.50, -.25, 0, .25, .50, .25, 0, -.25, -.50. -.25, 0)!!!
7. Why? Because at 45 degrees, the sine and cosine = 1/2 the square root of .5, or .7071, and so there are 4 occurrences of .50 (2 positive and 2 negative), because there are 4 places on the circle where the sine and cosine are equidistant from the x and y axes. The .25's occur at each 15 degrees and there are 8 of those, two on either side of a .50. Then, there are 5 zeros: at 0 degrees, 90 degrees, 180, 270 and 360 degrees. Of course there are a lot of other values in between the 0 and the .25 and the .50, etc, etc., but that is the main way to understand it. This is then a "half-curve", relatively speaking.
8. So if the sine and cosine vary between 0 and 1 ordinarily (without further adjustment), and they are ADDED together as one is small and the other is large, they will tend to equal 1. But if they are MULTIPLIED, they will equal at most .50 and at least, -.50
9. How to Compare Two Methods of Creating a Spherical Helix Picture 14
   If you can grasp that much, you are ahead of the game in terms of grasping trig and its ability to produce beautiful and unique designs that possess a unique clarity of definition, because when the "half-curve" is applied to a variable of 10pi to 0, the result is 5pi turns of the spiral of the helix of the sphere from one endpoint of 0 to the other endpoint of 0, which is a nice aesthetic number of turns to behold as a minimum.

Helpful Guidance

1. Make use of helper articles when proceeding through this tutorial:

   1. See the article How to Create a Spirallic Spin Particle Path or Necklace Form or Spherical Border for a list of articles related to Excel, Geometric and/or Trigonometric Art, Charting/Diagramming and Algebraic Formulation.
   2. For more art charts and graphs, you might also want to click on Category:Microsoft Excel Imagery, Category:Mathematics, Category:Spreadsheets or Category:Graphics to view many Excel worksheets and charts where Trigonometry, Geometry and Calculus have been turned into Art, or simply click on the category as appears in the upper right white portion of this page, or at the bottom left of the page.