24. Tracing of Polar Curves | Problem#2 | Most Important
How to Acquire a Lemniscate Curve of Sinewave Spheres in Excel
- Become familiar with the basic image to be created:
Set Your Preferences:Open Preferences in the Excel menu and follow the directions below for each tab/icon.
- In General, setR1C1to Off and selectShow the 10 Most Recent Documents.
- In Edit, set all the first options to checked exceptAutomatically Convert Date System. SetDisplay number of decimal placesto blank (as integers are preferred). Preserve the display of dates and set 30 for 21st century cutoff.
- In View, click on showFormula BarandStatus Barand hover forcomments of all Objects. CheckShow gridlinesand set all boxes below that to auto or checked.
- In Chart, allowshow chart namesand setdata markerson hover and leave the rest unchecked for now.
- In Calculation, Make sureAutomaticallyandcalculate before saveis checked. Set max change to .000,000,000,000,01 without commas as goal-seeking may be done a lot in future versions. Checksave external link valuesanduse 1904 system
- In Error checking, check all the options.
- In Save, selectsave preview picture with new filesandSave Autorecoverafter 5 minutes
- In Ribbon, keep all of them checked exceptHide group titlesandDeveloper.
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.
MODIFIED:Enter the upper Defined Name Variables Section (here's a picture AFTER insertion of three new columns at C:E which you are to ignore for now):
- A1: Aligned left, enter Sinewave Spheres in a Lemniscate Curve and Format Font Apple Chancery or something fancy and nice?
- E1: AYE
- E2: BEE
- E3: CEE
- Modified: F1: 30
- F2: .50
- F3: .50
- Select cell range E1:F3 and Insert Name Create in Left Column, OK.
- G1: Stretch_y1
- Modified: G2: Enter w/o quotes the formula "=(8.5*(SHRINKER*10))*0.75"
- H1: Stretch_x1
- Modified: H2: Enter w/o quotes the formula "=(8.5*(SHRINKER*10))*3"
- Select cell range G1:H2 and Insert Name Create in Top Row, OK.
- G3: Shrinker
- H3: Enter w/o quotes the formula "=0.1*12/SPHERES" and Insert Name Define Name Shrinker to cell $H.
- I1: ROWS
- J1: "=17285-5"
- I2: MAGIC
- J2: Enter w/o quotes the formula "=J1/SPHERES"
- I3: SPHERES
- Modified: J3: 30 for now.
- Select cell range I1:J3 and Insert Name Create in Left Column, OK.
MODIFIED:Enter the column heading of rows 4 and 5:
- Modified: A5: Lem Cos (for Lemniscate Cosine formula)
- Modified: B5: Lem Sin
- C5: Indicator
- D5: Randy
- E5: t: 0 to nπ
- F5: z1_
- G5: Adj_x1
- H5: Adj_y1
- I4 and J4: Charting
- I5: x: No z
- J5: y: With z
- Command+Select cells F1:F3 and I3 and Format Fill yellow.
- Select cell J3 and Format Fill sky blue from the color wheel.
- Select cell range I4:J5 and Format Font italic.
NEW:Place the cursor in the column headers for C, D and E (that is, Edit Go To C:E) and Insert Columns. Enter the column variables, headers and column formulas for new columns C:E.
- C3: Enter Unadjusted RADIUS
- C4: Enter 3 and do Insert Name Define Name Unadjusted RADIUS to cell $C. Format Fill Red and Font White, with Border bold Outline (Navy Blue), Format Number Number Decimal Places 4.
- Select E2 and enter Height. Select E3 and enter 2 and do Insert Name Define Name Height to cell $E. Format Fill yellow and Format Cells Number Number decimal places 5 and font bold.
- Select cell D4 and Enter Adjustment. Select cell E4 and enter "=0.125*2880/17260" w/o quotes and do Insert Name Define Name Adjustment to cell $E. Select D4:E4 and Border Outline Navy Blue bold, fill white, decimal places 5.
- Select cell C5 and enter radius r.
- Select cell D5 and enter ø degrees (or theta degrees).
- Select cell E5 and enter sin 2øπ.
- Select cell C4 and copy and past special format to cell C6. Into cell C6, enter "=UNADJUSTED_RADIUS".
- Edit Go To cell range C7:C17285 and enter into C7 the formula w/o quotes, "=C6" and Edit Fill Down. Format Fill light blue.
- Select cell D6 and enter 0. Edit Go To cell range D7:D17285 and enter to D7 "=D6+1" and Edit Fill Down. Fill light blue.
- Edit Go To cell range E6:E17285 and enter to E6 w/o quotes the formula, "=HEIGHT*SIN(2*D6*PI()/180*Adjustment)", Edit Fill Down and Fill light blue. Select C6:E17285 and Format Number Number Decimal Places 4.
NEW:Enter the Thickness Looker lookup table.
- Select cell O5 and enter Thickness Looker.
- Edit Go To cell range O6:O69 and enter 1 into O6 and do Edit Fill Series Column Linear Step Value 1, OK.
- Edit Go To cell range P6:P17 and enter .8 and Fill Down. Enter .85 into P18, .90 into P19, .95 into P20 and .5 into the cell range P21:P69 via Edit Fill Down.
- Select cell range O6:P69 and Insert Name Define Name Thickness_Looker to cell range $O:$P and Format Fill yellow. To the right of P, I have a column I copied and pasted values into P from, as multiplied P by 10, or divided by 100, or subtracted 30, or 50 -- it took a lot of hunting around to get the right values to make the chart come out so you should probably copy the table and paste values for it in the Saves worksheet at some point, including ...
- Select cell O3 and type Thickness and enter to O4 "=VLOOKUP(SPHERES,Thickness_Looker,2)" and do Insert Name Define Name Thickness to cell $O. Format Fill light blue and border red outline bold.
MODIFIED:Enter the column formulas:
- Modified: Lem Cos: Edit Go To cell range A6:A17285 and enter into A6 w/o quotes the following formula, "=Thickness/2*C6*COS(D6*PI()/180*Adjustment)" and Edit Fill Down. Format Fill light blue.
- Modified: Lem Sin: Edit Go To cell range B6:B17285 and enter into B6 w/o quotes the following formula,"=Thickness/2*(C6^2*Adjustment)*E6" and Edit Fill Down. Format Fill light blue.
- Modified: Indicator: Select cell F6 and enter 1. Edit Go To cell range F7:F17286; enter into F7 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. Format Number Number Custom 0.00000;; to stop the zeros from appearing.
- Modified: Randy: Edit Go To cell range G6:G17286 and enter into G6 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 G6:G17285 and Insert Name Define Name Randy to cell range $G:$G285.
- Modified: t: 0 to nπ: Select cell H6 and enter 0. Select cell H7 and enter the formula "=(2*PI()/MAGIC)". Edit Go To cell range H8:H17285 and enter w/o quotes into E8 the formula "=IF(F8=1,2*PI(),2*PI()/MAGIC+H7)" and Edit Fill Down.
- Modified: z1_: Edit Go To cell range I6:I17285 and enter w/o quotes into I6 the formula "=CEE*COS(AYE*H6)" and Edit Fill Down. Edit Go To cell range I6:I17285 and Insert Name Define Name z1_ to cell range $I:$I285.
- Modified: Adj_x1: Edit Go To cell range J6:J17285 and enter w/o quotes into J6 the formula "=IF(F6=1,A6,J5)" and Edit Fill Down. Edit Go To cell range J6:J17285 and Insert Name Define Name Adj_x1 to cell range $J:$J285. This makes a constant adjustment as if one were referencing a new center every new sphere from Lem Cos, else it takes the value just above itself.
- Modified: Adj_y1: Edit Go To cell range K6:K17285 and enter w/o quotes into K6 the formula "=IF(F6=1,B6,K5)" and Edit Fill Down. Edit Go To cell range K6:K17285 and Insert Name Define Name Adj_y1 to cell range $K:$K285. This makes a constant adjustment as if one were referencing a new center every new sphere from Lem Sin, else it takes the value just above itself.
- Modified: x: No z: Edit Go To cell range L6:L17285 and enter w/o quotes into L6 the formula "=SHRINKER^2*(Stretch_x1*(((BEE^2-CEE^2*COS(AYE*H6)*COS(AYE*H6))^0.5 *COS(H6)))+Adj_x1)" and Edit Fill Down. This is the x part of the heart of the sinewave sphere formula from the text, without the z dimension added or multiplied in, which is why it took me so long to discover how to make it work.
- Modified: y: With z: Edit Go To cell range M6:M17285 and enter w/o quotes into M6 the formula "=SHRINKER^2*(Stretch_y1*(((BEE^2-CEE^2*COS(AYE*H6)*COS(AYE*H6))^0.5 *SIN(H6))+z1_)+Adj_y1)" and Edit Fill Down. This is the y part of the heart of the sinewave sphere formula from the text, with the z dimension added in, which is why it took me so long to discover how to make it work. In the spirallic spheroids Garthwaite Curve, the z-dimension is multiplied into both x and y parts. Furthermore, no adjustment was made for the GOLDEN MEAN Long Leg, which was expected all along, until it worked without it. The other curve doesn't.
- Modified: Select cell L17286 and enter the formula w/o quotes "=L6" and select cell M17286 and enter the formula w/o quotes "=M6". This makes the top connecting line from the last sphere to the first.
- Modified: Planned Error Value -- Select cell L17287 and enter "=SHRINKER^2*(Stretch_x1*(((BEE^2CEE^2*COS(AYE*H17287)*COS(AYE*H17287))^0.5*COS(H17287))) +Adj_x1)*Randy" or +Randy, etc. Warning: this can really take a lot of processing time -- set calculation to Manual first.
- Modified: Planned Error Value -- Select cell M17287 and enter "=SHRINKER^2*(Stretch_y1*(((BEE^2CEE^2*COS(AYE*H17287)*COS(AYE*H17287))^0.5*SIN(H17287)) +z1_)+Adj_y1)*Randy" or +Randy, etc. Warning: this can really take a lot of processing time -- set calculation to Manual first.
- Modified: Edit Go To cell range L6:M17288 and do Format Fill sky blue.
- Modified: Select cell G5 and Format Fill light sea green, font red, Border navy blue outline bold. Copy this cell to K17287. Then do Edit Paste Special Format of this cell to cell F6, H6, H7, L17286 and M17286 to make distinct the format of those cell's formulas/values.
Explanatory Charts, Diagrams, Photos
- (dependent upon the tutorial data above)
Create the Chart.
- Modified: Edit Go To cell range L6:M17286 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 wide rectangle.
- 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 0 pt, Distance 30 pt, Transparency 37 %. Set Glow to very light blue Size = 11 pt. 2% transparency, Soft Edges 0 pt. OK.
- 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.
- 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.
- The chart handles even numbers of spheres only, and better above 8 or so, up to 64.
- The chart's horizontal axis is set to auto, as is the vertical.
Above is the image to be created.
Make use of helper articles when proceeding through this tutorial:
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
- Aye = 20, Spheres = 20:
- Spheres = 64:
Spheres in Lemniscate = 64
- ERRORS: If there are unplanned errors or error values, either the sheet in incomplete and needs further input or Lookup Tables for critical variables or perhaps there's a mistake somewhere along the line. If the instructions have been completed and there are still errors, select the cell that has the error value that is furthest left and topmost first. Look for a typo in a formula or unmatched parentheses. Possibly, a Defined Name is wrong -- they need to be input into the formulas exactly as they were defined. Do Insert Name Define to check. If there is a #DIV/0! error, the example does not, so look for a variable that somehow did not get filled in with a value perhaps. At any rate, what to do is select the cell with the error, and after checking all those typical errors, do Tools Auditing Trace Precedents and/or Trace Error. If fixing all the topmost leftmost errors does not fix the rest of your errors on the worksheet, one may need to do it the hard way, from the bottom right upwards then leftwards; that is the slow but sure way to fix all errors.
- Also, errors in the chart data will most likely plot as zeros. This may be acceptable or desirable even. However, if too many lines (or curves) are returning to 0, it may indicate a logical flaw in the data -- or too many tiny values and then perhaps rescaling the chart is needed by inspecting the horizontal and vertical axes and changing them to zero in on the problem. Hover over or click on a data marker on the series plot and then do a search in the proper column by value for that value, and identify its precedents.
Sources and Citations
- The source workbooks for this article include: "Lemniscate Garthwaite Curve.xlsx" and "Leminscate Sinewave On Sphere p 266.xlsx"
- The Garthwaite Curve shown was first created by Chris Garthwaite in May of 1994 and is based upon the Spherical Helix found on page 264 along with the Sine Wave Sphere on page 266 of "CRC Standard Curves and Surfaces" by David von Seggern, CRC, ANN ARBOR, 1993.
Video: Polar Circles and Lemniscates
25People Who Won the Battle Against Excess Weight and Now Enjoy Their Lives
Can you ever actually wear your wedding dress again
How Many Calories Is in a Fast-Food Breakfast
White Wine Chicken with Artichokes and Tomatoes, with Golden New Potato Coins
Bitter Theplas Health Benefits And Recipe In Hindi
Alcohol increases breast cancer risk’
Bottega Veneta SpringSummer 2019 Collection – Milan Fashion Week
Christmas Dinner Plan
Kidney Cancer: A Guide to Nephrectomy
How to Do Street Photography
Fix It Here, Doc’ — When a Patient With a Severed Finger Wont Go to the ER
Tom Hiddleston Gets (Almost) Naked for W PhotoShoot
Inspiring Winter Wedding Centerpieces
Date: 16.12.2018, 05:54 / Views: 33261