How to Acquire Squares of Sinewave Spheres in Excel

The Tutorial

1. If you have already completed the workbook from the article, Acquire Sinewave Spheres via Excel and want to SAVE AS that workbook and follow its instructions again as MODIFIED or NEW, that is one option available to you and you should do a File Open in that case. Otherwise, simply follow these steps: Create a New Workbook with three worksheets; Data, Chart (unless you are 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 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 may be done. 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. MODIFIED: It helps placing the cursor at cell A14 and doing Freeze Panes. Edit Go To cell range A1:I17286 and Format Cells Number Number Decimal Places 4, Font Size 9 or 10, Fill (from the color wheel) a fuchsia.

   1. Make the Border Dark Blue bold Outline. For rows 1:3, set the font size to 14 and the decimal places to 5.

4. MODIFIED: Enter the Defined Variable Names and some initial values:

   1. In A1, enter a;
   2. In A2, enter b;
   3. In A3, enter c_;
   4. Select cell range A1:B3 and Insert Names Create in Left Column and do Border Outline Blue bold; select cell range B1:B3 and Format Fill yellow (for input);
   5. In B1, enter 30; f(or the complete sphere, the value = 100)
   6. In B2, enter .5; f(or the complete sphere, the value = .5)
   7. In B3, enter .5; f(or the complete sphere, the value = .5)
   8. In cell C1, enter Change_x
   9. In cell D1, enter Change_y
   10. Select cell range C1:D2 and Insert Names Create in Top Row and do Border blue bold Outline plus Center Vertical Divider Line
   11. In C2, enter "=Change*6/8.5"
   12. In D2, enter"=H3/SW_Spheres*12" and Insert Name Define Name Change to cell $D$2.
   13. In cell E1, enter Magical
   14. In cell E2, enter SW Spheres
   15. Select cell range E1:F2 and Insert Names Create in Left Column and do Border blue bold Outline plus Center Horizontal Divider Line
   16. In cell F1, enter "=17280/SW_Spheres"
   17. In cell F2, enter 24 and do Format Fill yellow for input.
   18. In cell G1, enter Stretch_x
   19. In cell H1, enter Stretch_y.
   20. Select cell range G1:H2 and Insert Names Create in Top Row and do Border blue bold Outline plus Center Vertical Divider Line
   21. In cell G2, enter "=SW_Spheres/(2*SW_Spheres/6)*VLOOKUP(SW_Spheres,LikelyStory,2)"
   22. In cell H2, enter "=Stretch_x"
   23. Command+Select cells C1:E2, F1, G2:H2, H3 and Format Fill white..
   24. In cell H3, enter 8.5

5. MODIFIED: Input Column Headers:

   1. A5: t: 0 to 2π
   2. B5: z
   3. C4 Charting ...and ... D4: Charting
   4. C5: x: No z
   5. D5: y: With z and Select cell range C4:G5 and do Font Italic and Fill White.
   6. E5: Side
   7. F4: Indicates, and F5: SphereNumber
   8. G5: Adj_X
   9. H5: Adj_y  and into H4 a note about the distance to the bottom hole of the first sphere "2.9473"
   10. I5: Indicative
   11. Edit Go To cell range B6:B17286 and Insert Name Define name z to range $B$6:$B$17286
   12. Edit Go To cell range E6:E17286 and Insert Name Define name Side to range $E$6:$E$17286
   13. Edit Go To cell range F6:F17286 and Insert Name Define name Indicates to range $F$6:$F$17286
   14. Edit Go To cell range G6:G17286 and Insert Name Define name Adj_X to range $G$6:$G$17286
   15. Edit Go To cell range H6:H17286 and Insert Name Define name Adj_y to range $H$6:$H$17286
   16. Edit Go To cell range I6:I17286 and Insert Name Define name Indicative to range $I$6:$I$17286

6. MODIFIED/NEW: Enter series or formulas in columns:

   1. t: 0 to 2π: Select cell A6 and input 0 into A6 and Format Fill light blue, font red. Edit Go To cell range A7:A17286 and input "=IF(Indicates<>0,2*PI(),IF(A6=2*PI(), 0,2*PI()/Magical+A6))" and Edit Fill Down.
   2. z: Edit Go To cell range B6:B5765 and enter into B6 w/o quotes the formula, "=c_*COS(a*A6)" and Edit Fill Down.
   3. x: No z: Edit Go To cell range C6:C17286 and enter into C6 w/o quotes the formula, "=(Stretch_x*(((b^2-c_^2*COS(a*A6)*COS(a*A6))^0.5*COS(A6)))+Adj_x)" and Edit Fill Down. Format Fill Light Blue.
   4. y: With z: Edit Go To cell range D6:D17286 and enter into D6 w/o quotes the formula, "=(Stretch_y*(((b^2-c_^2*COS(a*A6)*COS(a*A6))^0.5*SIN(A6))+z)+Adj_y)" and Edit Fill Down. Format Fill Light Blue. Select cell C17286 and enter "=C6" and copy and paste to cell D17286 and format fill yellow.
   5. Side: Edit Go To cell range E6:E17285 and enter into cell E6 w/o quotes the formula, "=IF(Indicative<=SW_Spheres,VLOOKUP(Indicative, LOOKY,1+SW_Spheres/4),E5)"
   6. Indicates: Select cell F6 and do Format Font Red, Fill light blue.
   7. Indicates: To cell F6 enter "=INT(((ROW()-6)/Magical))".
   8. Indicates: Edit Go To cell range E7:E17286 and enter w/o external quotes the formula, "=IF(((ROW()-6)/Magical)=INT((ROW()-6)/Magical),1+MAX($F$6:F6),0)" and Edit Fill Down and Format Fill light blue. INT gives the INTeger value of a quantity/result. It is supposedly no longer offered by Excel, but they still allow for its usage from old worksheets, so you can use it if you know about it. It's indispensable at times.
   9. Adj_X: Edit Go To cell range E6:E17286 and enter into cell E6 w/o external quotes the formula, "=IF(OR(Side="A",Side="E"),0,IF(AND(Side="B",Indicates<>0),(G5+Change_x),IF(Side<>"D",(G5),0)))+IF(Side="C",0,IF(G5=18,0,0))+IF(AND(Side="D",Indicates<>0),G5-Change_x,IF(OR(Side="B",Side="C"),0,G5))+IF(Side="E",-Change_x,0)" and Edit Fill Down #* To cell E6 Insert New Comment and edit in by pasting from the formula bar "Original Formula =IF(OR(Side="A",Side="E"),0,IF(AND(Side="B",Indicates<>0),(G5+Change_x),IF(Side<>"D",(G5),0)))+IF(Side="C",0,IF(G5=18,0,0))+IF(AND(Side="D",Indicates<>0),G5-Change_x,IF(OR(Side="B",Side="C"),0,G5))+IF(Side="E",-Change_x,0)". It is a good idea to Insert New Comment for all original formulas so you have them close to hand should they be overwritten completely and this article is hard to find and you have no backup file.
   10. Adj_Y: Edit Go To cell range F6:F17286 and enter into cell F6 w/o external quotes the formula, "=IF(OR(Side="D",Side="E"),0,IF(AND(Side="A",Indicates<>0),Change_y+H5,H5)) +IF(AND(Side="C",Indicates<>0),-Change_y,0)" and Edit Fill Down.
   11. To cell F6 Insert New Comment and edit in by pasting from the formula bar "Original Formula =IF(OR(Side="D",Side="E"),0,IF(AND(Side="A",Indicates<>0),Change_y+H5,H5))+ IF(AND(Side="C",Indicates<>0),-Change_y,0)". Format fill Medium Blue.
   12. Indicative: Edit Go To cell range I6:I17286 and enter into cell I6 w/o external quotes the formula, "=MAX($I5:I$5)+IF(((ROW()-6)/Magical)=INT((ROW()-6)/Magical),1,0)".

7. Create the Lookup Tables, LOOKY and LikelyStory:

   1. Go to cell L3 and in Red Bold Font size 20, type LOOK OUT FOR LOOKUP TABLES FAR BELOW! You want to save up top right of the main data set you just entered.
   2. Go to cell K17153 and type LikelyStory, font bold red. Select cell range K17154:K17169 and enter 4, then do Edit Fill Series Column Linear Step Value 4, OK. Enter the following values into cells L17154:L17169, proceeding down from the top: 4, 3, 2, 1.5, 1.25, 1, 1, .75, .7, .65, .65, .5, .5, .5, .4, .4 Select cell range K17154:L17169 and Insert Name Define Name LikelyStory Do Format Font Red Bold, Fill Yellow. and Border Black Outline.
   3. Edit Go To cell range K17086:K17150 and enter 0 into cell K17086 and do Edit Fill Series Column Linear Step Value 1, OK.
   4. Into cell K17085 type LOOKY and do Font Red bold.
   5. Edit Go To cell range L17086:AA:17086 and input 4 into cell L17086 and do Edit Fill Series Rows Linear Step Value 4, OK.
   6. Select AA17985:K17169 and do Format Column Autofit Selection.
   7. Into cell range L17087:L17090 from top to bottom, enter A, B, C, D. What you're doing is setting the Side Letter of 4 sides to each sphere number down the left column of the number of sides in the top row, 4 in this case. The next case is 8 sides as indicated by the value 8 in cell M17086. From the top down, enter A, A, A, B, B, C, C, D -- a total of 8 letters. Now to add another letter of each type in subsequent series. So go to N17088 and enter "=N17087" into that cell and copy that cell and paste it to cell range N17087:AA17150. Thus are made copies of whatever entry is made to the cell above. Now, it's just a matter of changing the cell letter at the right time. So if, for example, there is to be one more A under 12 than under 8, you type a B after adding one more A in the column, and add a C after adding one more B in the column, and enter a D after adding one more C in the column, and after there is one more D, you hit delete, and the rest of the column entries go to zero. The zeros were cleaned up by deleting them all -- your preference. Here are pictures of both the LikelyStory and LOOKY vlookup tables necessary for completing this article:
      
      

      LikelyStory

Explanatory Charts, Diagrams, Photos

1. (dependent upon the tutorial data above)

1. Create the Chart:

   1. Select cell range C6:D17286 and do Charts on the Ribbon (or Chart Wizard), All/Other, Scattered - Smoothed Line Scatter.
   2. Copy or Cut and Paste the chart which landed atop the data or to the side and paste in the Chart worksheet, where it should be pulled open to an approximate square at the lower left by hovering the cursor until it changes to the double-headed arrow and then grabbing the chart frame and pulling down and right.
   3. Select Chart Layout and get rid of Axes, Grid lines, and the Legend.
   4. Click in the Chart Area outside the Plot Area near the outer border and choose no line and no Fill. Do the same for the Plot Area. To do one of the 24 spheres, select the cell range per Indicates. In that case, the chart area may need to be squeezed perhaps until the sphere appears round (may be a little pumpkinish).
   5. Select Chart Layout on the Ribbon after tapping in the chart and at left select Chart Area Format Selection. Glow is a wine brown, size 48 pt, transparency 255, 0 pt soft edges. Line is No Line, and 3-D and Depth is None. Fill Transparency is 0% Gradient is Radial Centered Far Left White, Far Right Navy Blue or Dark Blue.
   6. Select Chart Layout on the Ribbon after tapping in the chart and at left select Plot Area Format Selection. No Line, Glow, Shadow or 3-D. Fill Gradient is Rectangular Centered and Transparency 0%. At far left is black at 0%, then Blue-Purple at 24% and Light Blue at 74%.
   7. Select Chart Layout on the Ribbon after tapping in the chart and at left select Series 1 Format Selection. Line is Navy Blue, Smoothed Line, Transparency 0%, Weight 1 pt.
   8. Do Chart Format Effects Reflection [choose the 3rd one down on the right probably -- it's not letting me access it right now because I'm dealing with the Chart vs. a Copied Picture of the chart, done with the shift key for both Copy and Paste.
   9. Copy and Paste both data and pasted values of the data and a picture of your charts to the Saves worksheet. Copy the Lookup Tables to the Saves worksheet as well.
   10. Try setting a,b and c_ to 100, .5 and .5 respectively and set the line weight is set to 1 or less for charts with few spheres. The more spheres, the less is preferred a to equal, as the number of lines per sphere.

2. Finished!

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.