How to Create Sine Waves on Cylinders (Method 2)

The Tutorial

1. How to Create Sine Waves on Cylinders (Method 2) Picture 1
   For those who have completed the article and workbook from Create a Ring of Sinewave Spheres in Excel, this article directly follows that one, so do a SAVE AS of that workbook and save it under a new appropriate name for this project, and look for NEW and MODIFIED notes in the steps below. Otherwise, please create a new Excel workbook with three worksheets: Data 01, Chart (unless using Chart Wizard) 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 grid lines 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 is 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 placing the cursor at cell A16 and doing Freeze Panes. Edit Go To cell range A1:J17288 and Format Cells Number Number Decimal Places 4, Font Size 9 or 10, Fill (from the color wheel) a nice fuchsia and make the Border Dark Blue bold Outline.
4. How to Create Sine Waves on Cylinders (Method 2) Picture 2
   Enter the upper Defined Name Variables Section (here's a picture):

   1. MODIFIED: A1: Aligned left, enter Sine Waves on Cylinders in a Ring and Format Font Apple Chancery or something fancy and nice? Format Cell Fill White for cell range A1:C1 or so.
   2. E1: AYE
   3. E2: BEE
   4. E3: CEE
   5. F1: 40
   6. F2: .50
   7. F3: .50
   8. Select cell range E1:F3 and Insert Name Create in Left Column, OK. Select cell range F1: F3 and Format Fill yellow for input.
   9. G1: Stretch_y1
   10. G2: Enter w/o quotes the formula "=(8.5*(SHRINKER*10))"
   11. H1: Stretch_x1
   12. H2: Enter w/o quotes the formula "=(8.5*(SHRINKER*10))"
   13. Select cell range G1:H2 and Insert Name Create in Top Row, OK.
   14. G3: Shrinker
   15. H3: Enter w/o quotes the formula "=0.1*12/SPHERES" and Insert Name Define Name Shrinker to cell $H$3.
   16. I1: ROWS
   17. J1: "=17285-5"
   18. I2: MAGIC
   19. J2: Enter w/o quotes the formula "=J1/SPHERES"
   20. I3: SPHERES and Format Fill yellow.
   21. J3: 12 for now and Format Fill Turquoise Blue.
   22. Select cell range I1:J3 and Insert Name Create in Left Column, OK.
   23. Command+Select G1:J2, G3:H3 and Format Fill White. Set borders according to diagram (link variable name to variable) in Navy blue bold outline.
5. Enter the column headings of rows 4 and 5:

   1. A5: Adj Cos (for Adjusted Cosine)
   2. B5: Adj Sin
   3. C5: Indicator
   4. D5: Randy (for RandBetween)
   5. E5: t: 0 to nπ
   6. F5: z1_
   7. G5: Adj_x1
   8. H5: Adj_y1
   9. I4 and J4: Charting
   10. MODIFIED: I5: x: Plus z
   11. MODIFIED: J5: y: Plus z
   12. Modified: Select cell range I4:J5 and Format Font bold italic.
6. Enter the column formulas

   1. Adj Cos: Edit Go To cell range A6:A17285 and enter into A6 w/o quotes the following formula, "=17*COS((ROW()-6)*0.25/12*PI()/180)" and Edit Fill Down. The .25 is for 1440*.25 = 360. 17280/12=1440. So we are taking 1/12th of 1/4 or 1/48th of 17280 = 360, the degrees of the trig function cosine for a circle. PI()/180 converts radians to degrees. (ROW()-6) in row 6 = 0, so we start off taking the cosine of 0, which is 1, and multiplying it by 17. 17 is twice the Stretch factor, and if I recall rightly, is 1/2 the radius of the ring (the Stretch factor operates from each sphere's center). However, Shrinker is also involved, as you'll see later.
   2. Adj Sin: Edit Go To cell range B6:B17285 and enter into B6 w/o quotes the following formula,"=17*SIN((ROW()-6)*0.25/12*PI()/180)+17"and Edit Fill Down, which is different than the above one for cosine by the addition of 17 to it. All in all, that give us 4*8.5, and that is the radius, as I recall.
   3. Indicator: Select cell C6 and enter 1. Edit Go To cell range C7:C17286 and enter w/o quotes the formula, "=IF((ROW()-7)/MAGIC=INT((ROW()-7)/MAGIC),1,IF((ROW()-7)=0,1,0))" and Edit Fill Down. This formula says, 'Take a look at the row I'm in, divide it by the number of rows per sphere (MAGIC) and if that number is an integer, return a 1, otherwise if I'm in the next-to-top row also return a 1, otherwise, return a 0.' So now there is an indicator of where 1 sphere ends and the next one begins, no matter how many spheres the user selects to chart.
   4. Randy: Edit Go To cell range D6:D17286 and enter into D6 w/o quotes the following formula,"=RANDBETWEEN(0,10)/100" and Edit Fill Down. Warning: Make calculation Manual before adding this variable or column into your formulas, especially as a factor, as it can take 20 minutes to calculate and draw the new chart. It is not currently employed, but a copy of its formula has been saved at the bottom of the x and y formulas. Edit Go To cell range D6:D17285 and Insert Name Define Name Randy to cell range $D$6:$D$17285.
   5. t: 0 to nπ: Select cell E6 and enter 0. Select cell E7 and enter the formula "=(2*PI()/MAGIC)". Edit Go To cell range E8:E17285 and enter w/o quotes into E8 the formula "=IF(C8=1,2*PI(),2*PI()/MAGIC+E7)" and Edit Fill Down.
   6. z1_: Edit Go To cell range F6:F17285 and enter w/o quotes into F6 the formula "=CEE*COS(AYE*E6)" and Edit Fill Down. Edit Go To cell range F6:F17285 and Insert Name Define Name z1_ to cell range $F$6:$F$17285.
   7. Adj_x1: Edit Go To cell range G6:G17285 and enter w/o quotes into G6 the formula "=IF(C6=1,A6,G5)" and Edit Fill Down. Edit Go To cell range G6:G17285 and Insert Name Define Name Adj_x1 to cell range $G$6:$G$17285. This makes a constant adjustment as if one were referencing a new center of every new sphere from Adj Cos, else it takes the value just above itself.
   8. Adj_y1: Edit Go To cell range H6:H17285 and enter w/o quotes into H6 the formula "=IF(C6=1,B6,H5)" and Edit Fill Down. Edit Go To cell range H6:H17285 and Insert Name Define Name Adj_y1 to cell range $H$6:$H$17285. This makes a constant adjustment as if one were referencing a new center of every new sphere from Adj Sin, else it takes the value just above itself.
   9. MODIFIED: x: Plus z: Edit Go To cell range I6:I17285 and enter w/o quotes into I6 the formula "=SHRINKER^2*(Stretch_x1*((BEE*COS(E6))+z1_)+Adj_x1)" and Edit Fill Down. This is the x part of the heart of the sine wave cylinder formula from the text, with the z dimension added in.
   10. MODIFIED: y: Plus z: Edit Go To cell range J6:J17285 and enter w/o quotes into J6 the formula "=SHRINKER^2*(Stretch_y1*((BEE*SIN(E6))+z1_)+Adj_y1)" and Edit Fill Down. This is the y part of the heart of the sine wave cylinder formula from the text, with the z dimension added in.
   11. Select cell I17286 and enter the formula w/o quotes "=I6" and select cell J17286 and enter the formula w/o quotes "=J6". This makes the top connecting line from the last sphere to the first.
   12. MODIFIED: Select cell I17285 and copy it and paste it to cell I17287 and add *Randy at the end of it. Warning: this can really take a lot of processing time -- set calculation to Manual first. This is a planned error value result.
   13. MODIFIED: Select cell J17285 and copy it and paste it to cell J17287 and add *Randy at the end of it. Warning: this can really take a lot of processing time -- set calculation to Manual first. This is a planned error value result.
   14. Edit Go To cell range I6:J17288 and do Format Fill Turquoise blue.
   15. Select cell D5 and Format Fill light sea green, font red, Border navy blue outline bold. Copy this cell to H17287. Then do Edit Paste Special Format of this cell to cell C6, E6, E7, I17286 and J17286 to make distinct the format of those cell's formulas/values.

Explanatory Charts, Diagrams, Photos

1. (dependent upon the tutorial data above)

1. Create the Chart:

   1. Edit Go To cell range I6:J17286 and from the Ribbon or Chart Wizard select Charts All/Other Scatter Smoothed Line Scatter and Copy or Cut the chart that is atop the data worksheet and paste it to the top left of the Chart worksheet. Hover over the lower right corner until the cursor becomes a double-headed arrow and pull it open to become a large approximate square.
   2. Click in the Plot Area and select Chart Layout from the ribbon and at far left under Current Selection select Series 1, then under that, Format Selection. Set Line to Black, Smoothed line, Weight = 1 pt. and Dashed = Solid. Set Shadow to checked Outer 45 degrees, black, Size 100%, Blur 4 pt, Distance 30 pt, Transparency 75 %. Set Glow to very light blue Size = 1 pt. 2% transparency, Soft Edges 0 pt. OK.
   3. Do Current Selection under Chart Layout as Plot Area, Format Selection. No Line, No Glow and No Shadow. Set Fill to No. Gradient to None. 3-D is all zeros. OK.
   4. Do Current Selection under Chart Layout as Chart Area, Format Selection. Fill Gradient color Prussian Blue I think they call it on left 0% to Navy Blue on right 100%, Path, 0 degrees, Transparency 0%. Line = Auto. Shadow is Unchecked; No Glow or Soft Edges. 3-D Format is not set. OK.
   5. The AYE variable setting controls lines per cylinder and 40 allowed some daylight.
   6. How to Create Sine Waves on Cylinders (Method 2) Picture 3
      
      
      That's done now! Good work!

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.