Bulldog Through an Intellectually Simple Problem

Revision as of 16:01, 21 July 2016 by Kipkis (Kipkis | contribs) (importing article from wikihow)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Ever keep guessing at an answer instead of using the old noggin? Learn how to use your head instead of your fingers trying to figure out the answer.

Steps

The Tutorial

  1. Open a new Excel workbook and create a Data worksheet titled Bulldoggin Blues and another called The LR Chart. Save the workbook into a logical file folder under the name 'L to R from 90 degrees', or something similar.

  2. Set Preferences. The Goal Seeking Preferences will be important. Open Preferences in the Excel menu. Recommended Settings: Set General to R1C1 Off and Show the 10 Most Recent Documents; Edit - set all the Top options to checked except Automatically Convert Date System. Display number of decimal places = blank (for integers preferred), Preserve display of dates and set 30 for 21st century cutoff; View - show Formula Bar and Status Bar, hover for comments and all of Objects, Show gridlines and all boxes below that auto or checked; Chart - show chart names and data markers on hover. Leave rest unchecked for now; Calculation -- Automatically and calculate before save, Limit iteration checked, max iterations 100, max change .000,000,000,000,01 w/o commas as this problem involves precise goal seeking and save external link values and use 1904 date system; Error checking - check all; Save - save preview picture with new files and Save Autorecover after 5 minutes; Ribbon -- all checked except Hide group titles and Developer.
  3. Select the entire worksheet by selecting in the far upper left hand corner between the A and 1. Do Format Cells Font Size 9 or 10 if you're OK with that, and do Number Number Decimal Places 4.
  4. Enter into cell A1 2880 and Insert Name Define Name AdjRows to cell $A$1. Into cell B1 enter 1712 and Format Cells Number Number Custom "Base" 0 and Insert Name Define Name Base for cell $B$1. Into cell C1 enter the Defined Variable name Spheroids.and into cell D1 enter 64 and Insert Name Create Name in Left Column Spheroids for cell $D$1. Into cell E1 input 0.618033988749895 and Insert Name Define Name GM (for Golden Mean) to cell $E$1. Into cell F1 enter the Defined Variable name Radius.and into cell G1 enter 62 and Insert Name Create Name in Left Column Radius for cell $G$1.
  5. Enter into cell A2 the formula w/o quotes "=Base*12*PI()" and Insert Name Define name Tip for cell A2. Edit Go to cell range A3:A2882 and with A3 as the active cell, enter the formula w/o quotes "=((A2+(-Tip*2)/(AdjRows)))" and Edit Fill Down, Edit Go To cell range B2:B2882 and with B2 the active cell enter w/o quotes the formula "=Base*24/Spheroids" and Edit Fill Down. Select cell C2 and Format Cells Border Black thick Outline and Fill Yellow -- this is the crux of the problem. Edit Go To cell range C3:C2882 and with C3 the active cell enter w/o quotes the formula, "=C2-1" and Edit Fill Down. Enter into cell C2 your best Initial Guess at the number of degrees to start calculating the Cosine and Sine formulas from in order to be able to have the chart move from left to right instead of right to left, and from 90 degrees through 0 degrees instead of vice versa. My Initial Guess was 90 degrees. In creating the chart (in a few steps), I noticed larger guesses like 180, 360, 450 and 540 were moving the arc leftwards the way ti should go, but the method was error prone and unscientific, even if it did eventually hit on the correct answer by chance. No, bulldogging one's way through life is not preferable; finesse is optimal and saves oodles of time.
  6. Edit Go To cell range D2:D2882 and w/o quotes enter formula "=Radius*COS(C2*PI()/180*0.125)" and Edit Fill Down. Edit Go To cell range E2:E2882 and w/o quotes enter formula "=Radius*SIN(C2*PI()/180*0.125)" and Edit Fill Down. Edit Go To cell range F2:F2882 and w/o quotes enter formula "=((SIN(A2/(B2*2))*GM*COS(A2)*GM*(COS(A2/(B2*2)))*GM)+D2)" and Edit Fill Down. Edit Go To cell range G2:G2882 and w/o quotes enter formula "=((SIN(A2/(B2*2))*GM*SIN(A2)*GM*(COS(A2/(B2*2)))*GM)+E2)" and Edit Fill Down.

Explanatory Charts, Diagrams, Photos

  • (dependent upon the tutorial data above)
  1. Create The LR Chart. Edit Go To cell range F2:G722. If you're using Chart Wizard, basically follow these same steps except that a new Chart Page will be created for you, which you many re-title. Select Charts from the menu, All or Other, and scroll down to select Scattered Smoothed Line Scattered and copy of cut the resultant chart and paste onto The LR Chart worksheet into cell A1. Hover over the lower right corner until the cursor changes to the double-headed arrow and then click on the corner of the chart and pull it open into a large square. The result should be approximately a quarter-circle of 16 spheres which do NOT start at 0 degrees and proceed to 0 degrees -- OOPS! That is, unless your Initial Guess was excellent and well thought out.
  2. Define the problem: There are 2880 rows and a quarter of the circle  = 2880/4 or 720. There is only one cycle being made around the ring because of the 0.125 factor used in the formula for Cosine (and Sine), of "=Radius*COS(C2*PI()/180*0.125)", because 2880 * .128 = 360 (degrees). 90 degrees does not work. Neither did 360 +90=450 or 540 when I was "Fooling around" guessing at the answer instead of thinking. It's simple, really: 720 degrees is not only a quarter circle but it is 8 times 90 degrees! And the problem is very easy when described as the comparison of the ratios 90/360 - x/2880, which soon becomes 2880 = 4x or x = 720. 720 is the correct answer, even though it just seems wrong, being twice 360. Or, another way to think of it is that 0.125*720 = 90, or 1/4 of 0.125*2880. 8 of those are needed because 2880 is 8 times 360. So, 8*90 = 720. Think of 2880 as the level of detail obtained per spheroid; 2880/64 = 45 data points per spheroid. You may wish to double-click on the series plot line and decrease the Line Weight to 1 or do it via Chart Layout in the upper left hand corner where it says Format Selection.
  3. Make a new chart, Edit Go To cell range F2:G2882 with 720 now entered into cell C2. It plainly starts and ends at {0,2}, which is exactly what is wanted, and proceeds from left to right, Problem solved!

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.
    • 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

  • Recall that the Radius, which has previously gone by the name Thickness, adjusts the roundness of the spheres/spheroids by determining packing. Thus, less spheres require less packing, or a smaller radius. A VLOOKUP table is useful in such circumstances where one variable changes with a more or less constant relation to another as it changes, i.e. depending upon the quantity of the independent variable.

Warnings

  • ERRORS: If you have errors or error values, either the sheet in incomplete and needs further input or Lookup Tables for critical variables or perhaps you've made 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 you have DIV/0!, I do not, so look for a variable that somehow did not get filled in with a value perhaps. At any rate, what you want 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 your worksheet, you 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 your chart data will most likely plot as zeroes. 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 datamarker on the series plot and then do a search in the proper column by value for that value, and identify its precedents.

Things You'll Need

  • Microsoft Excel. The above was done with MicroSoft® Excel® for Mac 2011, v 14.3.1 and other versions are compatible, except for Reflection, Shadow, Glow and other more recent Effects additions.

Related Articles

Sources and Citations

  • "CRC Standard Curves and Surfaces", David von Seggern, 1993, CRC Press Inc., Boca Raton, FL. ISBN 0-8493-0196-3, Library of Congress Card Number 92-33596, [pp. 264 7.1.4 Spherical Helix]
  • The source file for this article is "LEFT TO RIGHT FROM 90 DEGREES.xlsx".
Retrieved from "https://kipkis.com/index.php?title=Bulldog_Through_an_Intellectually_Simple_Problem&oldid=13209"