Approximate Arc Length Using the Distance Formula

You will learn to approximate the arc length of a spherical helix and thus the length of any curve for which you have the {x, y} data set, using the distance formula, Also, learn of another approach using Calculus for simpler curves.

Steps

  • Become familiar with the image to create:

The tutorial

  1. For those of you who have completed an article and workbook involving the Garthwaite Curve and/or spherical helices, you may do a SAVE AS of that workbook and save considerable time and effort, for what you want is one sphere's data to apply the distance formula s=((x2-x1)^2 + (y2-y1)^2)^.5 to each new set of 4 x's and y's. So, for example, if you have a workbook with 2880 rows that creates 12 round spheres, 2880/12=240 x,y pairs you want, starting at the top probably. Otherwise, follow the steps below to create such a workbook and calculate the arc length of the sphere via the distance formula. You will need to create a new workbook and 4 worksheets: Data, Chart, Arc Length and Saves.
  2. Set the Preferences under the Excel menu:
    • In General, set R1C1 to Off and select Show the 10 Most Recent Documents .
    • 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.
    • 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.
    • In Chart, allow show chart names and set data markers on hover and leave the rest unchecked for now.
    • In Calculation, make sure Automatically is checked and calculate before save is also checked. Set max change to .001 as goal-seeking is not done in this project. Check save external link values and use 1904 system
    • In Error checking, check all the options.
    • In Save, select save preview picture with new files and Save Autorecover after 5 minutes
    • In Ribbon, keep all of them checked except Hide group titles and Developer .
  3. Go to cell A16 and do Freeze Panes under the Window menu. It helps by placing the cursor between the A of Column A and the 1 in Row 1 in the upper leftmost corner and selecting the entire worksheet. Format Cells Number Number Decimal Places 4, Font Size 9 or 10.
  4. Create the Defined Variables upper section (here's a picture):

    • Input AjRows into cell A1 and 2880 into cell A2.
    • Input GM into cell B1 and "=(-(1-SQRT(5))/2)" w/o quotes into cell B2. GM stands for Golden Mean.
    • Input Factor1 into C1 and "=VLOOKUP(ABS(Spheroids),Looker,IF(Spheroids<=24,2,2))"
    • Input KEY into cell D1 and "=IF(Spheroids<=24,Spheroids*VLOOKUP(Spheroids,LOOKER2,2),Spheroids)"
    • Input Number into cell E1 and 1 into cell E2. This is used to warp output but we won't be using it. Format Fill yellow.
    • Input CHAOS into cell H1 and 1 into cell H2. Also used to warp output; also unused now.
    • Select cell range A1:H2 and Insert Name Create Names in Top Row, OK. Align Center. Format Fill Chaos yellow.  Command+Select cell range A2:E2, H2 and do Border black bold Outline. Do Insert New Comment on all constants, variables and formulas as originally given, copying the original formula or value from the formula bar into the new comment -- this applies to A4 to H4 as well.
    • Input Tip to cell A3 and "=(Base*12/(VARIABLE*1)*PI())*CHAOS" into A4, w/o quotes as usual.
    • Input Base to cell B3 and "=16*107" into B4.
    • Input Spheroids to cell C3 and 24 to cell C4.
    • Input ShrinkExpand to cell D3 and 1 to cell D4.
    • Input PiDivisor to cell E3 and 180 to cell E4.
    • Input Thickness to cell F3 and 2 to cell F4. This is the relative Ring Size.
    • Input ShrinkExpand2 to G3 and 1.5 to G4.
    • Input VARIABLE to H3 and 1 to H4.
    • Format Fill C4 purple to really set it off from the other cells, Format Fill D4:H4 yellow. Make Font size 14 for cells C4 and F4. Select cell range A4:H4 and Format Cell Border black bold outline, then do red bold outline for cell F4. F4 is critical to good sphere roundness and sphere size overall, now that the Lookup Table for KEY has been doubled.
  5. Input the Column Headings:
    • Input Base t to A5.
    • Input constant c to B5.
    • Input Cos to C5.
    • Input Sin to D5.
    • Input Main X to E5.
    • Input Main Y to F5.
    • Input Second X to G5.
    • Input Second Y to H5.
  6. Input the Lookup Tables:
    • Input LOOKER to I5.
    • Enter 1 to I6. Edit Go To cell range I6:I180 and do Edit Fill Series Column Linear Step Value=1 OK.
    • Input .125 to cell J6. Edit Go To cell range J6:J180 qnd Edit Fill Down.
    • Input "=I6*$K$35/$I$35" to K6 w/o quotes. Edit Go To cell range K6:K180 and Edit Fill Down. Select K35 and input .125; that completes the LOOKER TABLE so go to I6:K180 and Insert Name Define Name Looker to cell range $I$6:$K$180.
    • Input LOOKER2 to L5.
    • Enter 1 to L6. Edit Go To cell range L6:L29 and do Edit Fill Series Column Linear Step Value=1 OK.
    • Enter the following values to MK6:M29 please: MK6:48; M7:32, then 16, 8 2*PI(), 2*PI(), 2*PI(), 4 from M13 down to M21, then 2 down to M29.
    • Enter DOUBLED!! into cell M5.
    • Select L6:M29 and Format Fill yellow with red bold border and Insert Name Define Name LOOKER2 to cell range $L$6:$M$29.
    • Select Row 5 and set Font Red, Underlined, Bold, Aligned center horizontally..
  7. Input the Column Formulas:
    • Input to A6 "=IF(ODD(Spheroids)=Spheroids,0,Tip)" w/o quotes. Format Fill Light Rose. Do Insert New Comment of original formula for all of these top formulas.
    • Edit Go To A7:A2886 and input to A7 "=(A6+(-Tip*2)/(AjRows))" w/o quotes and Edit Fill Down.
    • Select B6 and input "=Base*24/Spheroids". Insert New Comment.
    • Select B7:B2886 and input to B7 "=B6" and Edit Fill Down Insert New Comment = INC,
    • Edit Go To C6:C2886 and input to C6 "=Thickness*Spheroids/KEY*(COS((ROW()-6)*Number*PI()/PiDivisor*Factor1))" w/o quotes and Edit Fill Down. INC.
    • Edit Go To D6:D2886 and input to D6 "=Thickness*Spheroids/KEY*(SIN((ROW()-6)*Number*PI()/PiDivisor*Factor1))" w/o quotes and Edit Fill Down. INC.
    • Edit Go To E6:E2886 and input to E6 "=((SIN(A6/(B6*2))*GM*COS(A6)*GM*(COS(A6/(B6*2)))*GM)+C6)/ShrinkExpand" w/o quotes and Edit Fill Down. INC.
    • Edit Go To F6:F2886 and input to F6 "=((SIN(A6/(B6*2))*GM*SIN(A6)*GM*(COS(A6/(B6*2)))*GM)+D6)/ShrinkExpand" w/o quotes and Edit Fill Down. INC.
    • We won't be using column G or H but I'll give them to you anyway, in case you want to measure the arc lengths of two rings of spheres, one external to another,
    • Edit Go To G6:G2886 and input to G6 "=((SIN(A6/(B6*2))*GM*COS(A6)*GM*(COS(A6/(B6*2)))*GM)+C6)/ShrinkExpand2" w/o quotes and Edit Fill Down. INC.
    • Edit Go To H6:H2886 and input to H6 "=((SIN(A6/(B6*2))*GM*SIN(A6)*GM*(COS(A6/(B6*2)))*GM)+D6)/ShrinkExpand2" w/o quotes and Edit Fill Down. INC.

Explanatory Charts, Diagrams, Photos

  • (dependent upon the tutorial data above)
  1. Measure the Arc Length
    • Create the Chart
    • Select E6:F125 and create a chart that is Scatter, Smooth Line Scatter. Cut it and Paste it to the Arc Length worksheet upper left corner and drag it by the lower left corner until it occupies the region A35 to 1/2 the J column. Decrease the J column to fit along the edge of the Chart Area. Use Chart Layout to get ride of grid lines and legend. Set vertical scale to Minimum -.05 and Maximum .3 with Major Unit .05 and then set the horizontal axis scale to Minimum ,85 and Maximum 1.15 with Major Unit .05. Clock on the chart and then Chart Layout and do Current Selection Series 1, Format Selection. For Marker Style, select round circle size 3 to 5, as you prefer. What will be measured is the distance between markers, which you may see if you uncheck Smoothed Line. Thus, it's only an approximation, better in small areas than in large ones. I do not know how to calculate the total error. At least not between Excel and Excel. For Calculus vs. Excel, I can do it if given a function I can find the derivative for; this is not one of those, unfortunately.
    • Enter 2880 to L1 and "=Spheroids" to L2. Input Rows to K3 and enter "=L1/L2" to L3 and Insert Name Define Name Rows to $L$3.
    • Input Row# to K5. Input Main X to L5. Input Main Y to M5. Input Arc to N4 and Length to N5 and do Font size 16 for those. Copy them and paste them to O4:O5, then input Cumulative to O3. Input S# (for Sphere#) to P5 and format the font red. Select columns K:P and align center and do Format Column Autofit Selection.
    • Input 1 to cell K6 and Edit Go To cell range K6:K2885 and do Edit Fill Series Column Linear Step Value=1, OK.
    • Input "=Data!E6" into cell L6 then input "=Data!F6" to cell M6. Edit Go To L6:M2885 and Edit Fill Down.
    • Input to N7 "=((L7-L6)^2+(M7-M6)^2)^0.5" and copy and paste it to N8.
    • Input to O8 "=SUM($N$7:N8)" which will keep a cumulative total running beside the distance formula in Arc Length.
    • Edit Go To O8:N2885 and Edit Fill Down. Now you have the arc length cumulatively along the entire path. If you Edit Go To cell O125, you should see the cumulative total of the first sphere there, the one in the chart, at 7.3507 -- it would be nice to have a way to find these no matter how many integer spheres were input.
    • Go to cell P6 and input "=IF(K6/Rows<>INT(K6/Rows),"",INT(K6/Rows))", which formula leaves a blank unless the row number is evenly divisible by the Rows/Spheres (120)=2880/24 calculated up top automatically. Format Fill P6 yellow and make the font bold red size 16. Edit Go To P6:P2885 and Edit Fill Down. Now we have each sphere# in bold red large font to make it easy to find the cumulative totals.
  2. Copy the formulas from the Data worksheet and paste then to the Saves worksheet, then again below but to Paste Special Values atop itself this time to preserve the formatting but obtain only values. Then copy a picture of the chart with some of the data of the Arc Length worksheet and with the shift key again depressed, Paste Picture underneath the two sets of data, the formulas and values you just pasted as a good record of how you created the image.
  3. You may want to total the Arc Length column as a double check on the cumulative formula, which will not work without the leading number having the absolute reference dollar signs. THE INTeger function is a relic from the past and is not advertised any more but is valuable.
  4. For your final chart, make the chart title read the total Arc Length of the number of Spheroids you input by typing in the text box something like a reference to the worksheet or workbook and then a defined variable name (I cannot get it to work on XL2011 sheets unless I import from older versions and edit those text boxes): ='Revised-THE Garthwaite Curve.xlsx!'CumTotal
  5. The final image.



Helpful Guidance

  1. Make use of helper articles when proceeding through this tutorial:
    • 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.
    • For more art charts and graphs, you might also want to click on Microsoft Excel Imagery, Mathematics, Spreadsheets or 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.



Tips

  • I come to a slightly different answer than wikipedia obtains for the arc length of their example. Given that =SQRT(1+((25*X^8)/(9*X^4))) = 1.9436506316151, then =SQRT(((9*X^4)+(25*X^8))/(9*X^4)) also = the same figure. If we take the root of the denominator, now we have =SQRT(((9*X^4)+(25*X^8))/(1))/1 because dt = 3x2, as does the denominator, so these cancel, and we are left with the figure 5.8309518948453 as a result, which is clearly too long. We divide this by 2 because otherwise the answer = 0 and there are two units to consider, from 0 to 1 and from 0 to -1, and the total length is, on average, attributable to these two units. Besides, we need an answer much closer to the + and -1.943650632 arrived at so far. Clearly the curve has some arc length. So 5.8309518948453 / 2 = 2.91547594742265, which is 2.915, whereas wikipedia gave the answer as 2.905, which I attribute to a typo.
  • For those of you who are new to Calculus, t^5 become 5t^4, when derived, via the Power Rule, which states that for function y=f(x), the derivative f'(x), or dy/dx, of x^n is n*x^(n-1). dy/dx is nomenclature and is not about division; it means "the derivative of y with respect to x." If y = x^5, then y' = f'(x) = dy/dx = 5x^4. Both Leibniz and Newton invented the Calculus, and the difference in notation is preserved.
  • Spherical Arc Length
  • Line of Spheres
  • The other method(s) involving the Calculus involve derivation and integration. Here, from Wikipedia, is an understandable example of how to achieve results via the Derivation Method:
  • Here is another example, one more complicated, of the arc length of the 'tekeporter' -- calculated to be 216.1120; all I needed to do was to copy the page from the existing Arc Length file to the "GW TEKEPORTER" file, update the number of spheres and fix the Main X and Y formulas and create the chart. I also freezed panes on line 1445 before Grabbing a picture of the whole and using Preview to create a JPG file:
  • Here's another example, from the early workbook, "Create a Powerful Trigonometric Design in Excel". Again, I just made a copy of the Arc Length page and updated it for the local particulars -- in this case, one essentially very large sphere-like object taking up 360 rows. Its entire arc length comes in at a whopping 120,396.9878 units:
  • And here is what is actually being traced and counted as the arc lengths -- the Unsmoothed Line of the Tokomak:
  • Of course, Chaos Curves are always interesting:
  • One last one - the Square of Spheroids:

Warnings

  • ERRORS: If there are unplanned errors or error values that have otherwise not been discussed above, 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.

Related Articles

Sources and Citations

  • 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 of "CRC Standard Curves and Surfaces" by David von Seggern, CRC, ANN ARBOR, 1993.
  • The workbook used for this article was "Revised-THE Garthwaite Curve.xlsx"
  • http://en.wikipedia.org/wiki/Arc_length
Retrieved from "https://kipkis.com/index.php?title=Approximate_Arc_Length_Using_the_Distance_Formula&oldid=349597"