Do a Short Goalseeking Neutral Operations Problem in Excel

One of the more useful tools of Excels in Goal seeking. Here is how to apply it to quickly solve a problem involving Neutral Operations between two circle areas.

Steps

The Tutorial

  1. Create a new worksheet in Excel titled Goal Seeking - Neutral Operations, or something reasonably similar.
  2. Set Preferences: 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 calc before save, max change .000,000,000,000,01 w/o commas if you do goal-seeking a lot and save external link values and use 1904 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. The problem is to figure out a+b=a*b = c where a = πR^2 and b = πr^2. The steps of Neutral Operations for Addition vs. Multiplication are: a+b = ab; a+b-b = ab-b; a = b(a-1); a/(a-1) = b and b has been isolated and defined in terms of a and 1 but a may not = 1 lest division by 0 result in the denominator. Also, it's possible to start out by subtracting a from both sides instead of b, and because both addition and multiplication are commutative, it is also true that b/(b-1) = a. It's symmetrical, which is a hot property in math these days. For more on Neutral Operations, google NeuOps-Graphs01.
  4. In rows 1, enter the column headings. A1: a.r (for the radius of a); B1: b.r; C1: c: (the sum of a+b); D1: a; E1: b; F1 c (the product of a*b); G1: Goal; H1 0. Format Cells Alignment Horizontal Centered and Font Underlined,
  5. Enter values and formulas in row 2. B2: a guess at the answer to the equation =(PI()*B2^2)/((PI()*B2^2)-1) -- D2 should = 9, so 9/π = 3 roughly and 3/(3-1) =1.5, so the square root of that is about 1.22, so enter that as a preliminary guesstimate at the value of b.r  -- or just enter 1. This is the cell that will contain the goal seeking answer and it must start with a preliminary guess.
  6. In cell D2, enter the formula w/o quotes "=(PI()*B2^2)/((PI()*B2^2)-1)". The open parentheses for pi() are correct. This statement then is similar to stating that a = b/(b-1) where b=πr^2 and r of b.r resides in cell B2.
  7. In cell F2, enter "=D2". This is what we want a to equal, as the first part of c. In cell G2 enter 9 as this is our Goal Value. In cell H2, subtract F2 from G2, i.e. subtract the goal seeking result from the goal and we want the difference to equal 0.
  8. Select columns B and H and do Format Cells Number Number Decimal Places 15. In the Excel menu under Preferences for Calculation, set Limit Iteration to checked, Maximum iterations 100, and Maximum change .000,000,000,000,001 without the commas.

Goalseeking Used to Find the Answer

  1. Perform the Goal Seeking step. Select cell H2 and do Tools Goal Seek Set cell H2,  To Value (input) 0, by changing, and click on cell B2. 1.4210854715202E-14 is as close as it gets when it provides the answer for b.r B2 of 0.598413420602149; now a = 9 we know and we know the neutral radius.
  2. Copy row 2 to row 3. Enter in E3 the formula "=9/(9-1)" without quotes because b = a/(a-1). The answer is 1.125
  3. In cell C3 enter the formula w/o quotes "=E3+D3", which is the sum of a + b. In cell F3, enter the formula w/o quotes "=E3*D3", which is the product of a*b and also = 10.125, so it's proved that a+b = a*b = c. In cell G3, input the formula, "=C3".
  4. In cell D4, enter the formula "=D3" w/o quotes. In cell A4, under a.r, enter the formula without quotes, "=SQRT(D4/PI())", i.e. find the radius of a by taking πR^2 = a and changing it to R = SQRT(a/π). The answer  = 1.69256875064327
  5. Prove the hypothesis that πR^2 +πr^2 = πR^2 *πr^2 = 2A where A=Area:
  6. In cell B8, enter the formula w/o quotes "=PI()*A4^2"; in cell C8, enter the formula w/o quotes "=PI()*B3^2"; in cell D8, enter the formula w/o quotes "=B8+C8"; and in cell E8, enter the formula w/o quotes "=B8*C8". Finally, in cell H8, enter the formula w/o quotes "=D8-E8" and Format Cells Number Number Decimal Places 15.
  7. Done. Save the workbook in a logical file folder. The problem of creating two circles that have the same result whether their areas are added or multiplied together has been solved in the case where one circle's area = 9 sq. units.

Helpful Guidance

  1. Make use of helper articles when proceeding through this tutorial
    • See the Related Articles below and the article How to Do the Sub Steps of Neutral Operations for a list of articles related to Excel, Geometric and/or Trigonometric Art, Charting/Diagramming and Algebraic Formulation relating to Neutral Operations.
    • For more art charts and graphs, you might also want to click on Algebra, 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

  • Typically, goal seeking works where there is just one unknown, but by knowing the parameters of one unknown and staying within those and solving probabilistically for that unknown, more unknowns may be taken on, until one arrives at an overall good guesstimate or projection. So goal seeking is highly valuable in budgeting and other accounting endeavors, e.g. Pro Forma Statements. Statisticians love this tool as it gives them ballpark answers before they do heavy analysis. It is also useful in Chaos Theory and predicting Unpredictability, as it handles bifurcation problems fairly readily. It is also useful in finding roots of curves that cross the x axis many times, which is otherwise a thorny problem in math and calculus -- but one's initial guess needs to be quite close for it to work well in that problem arena.

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Do_a_Short_Goalseeking_Neutral_Operations_Problem_in_Excel&oldid=300413"