Solve a Difficult Neu Ops Problem by Goal Seeking in Excel

Learn how to solve a*b*c*...*i = a^(b*c*...*i) via goal seeking and Neutral Operations ("Neu Ops") in Excel. The algebra is wicked, but the solution falls easily to goal seeking.

Steps

The Tutorial

  1. Create a new workbook in Microsoft Excel with 2 worksheets: Data and Saves. Save the workbook into a logical file folder.
  2. Set Preferences under the Excel Menu, with Calculation - Goal Seeking Preferences being particularly 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. Click in the upper left hand corner of the Data worksheet to select the entire worksheet, between the 1 and the A, and Format Cells Font 9 or 10 if you're comfortable with that and Format Number Number Decimal Places 15., because there's a need to see that there's truly zero difference in two methods of calculating a relationship within the same group of numbers.
  4. Set the problem out. The problem is to set a*b*c*d*e*f*g*h*i = a^(b*c*d*e*f*g*h*i) = y. Algebraically, dividing both sides by a gives, after simplification, b*c*d*e*f*g*h*i = a^((b*c*d*e*f*g*h*i).-1), and inverting the exponent and raising both sides to it gives (b*c*d*e*f*g*h*i)^(1/((b*c*d*e*f*g*h*i)-1)) = a, after simplifying. So, fine, there is an isolated and defined but isolating much of anything else is going to be quite difficult if not impossible. One can say, however, that since a*b*c*d*e*f*g*h*i= y and (b*c*d*e*f*g*h*i)^(1/((b*c*d*e*f*g*h*i)-1)) = a, that (b*c*d*e*f*g*h*i)^(1/((b*c*d*e*f*g*h*i)-1))bcdefghj = j. Therefore, (bcdefghj)^(1+(1/((b*c*d*e*f*g*h*i)-1))) = y. But unless one knows the values of a or b through 0, one is at pretty much of a loss to say much else definitively. One might be able to build this structure up through series of steps, however, algebraically or by goal seeking. That is because ab = a^b becomes ab/a = (a^b)/a becomes b = a^(b-1) becomes b^(1/(b-1)) = a. The latter route of goal seeking has proven fruitful, however, if somewhat obvious. Obvious because, no matter the value selected for a, so long as b thru whatever multiply to 1 via reciprocals, they will also be raising a to the power of 1, via reciprocals that produce 1. So, for example, x*25*.04 = 1x = x and x^(25*.04) = x^1 = x also. And while there are other solutions, this one meets the criteria and is easy for Excel to calculate, so that's what its goal seeking function does in this case.
  5. Set up the worksheet. Into column A, enter the labels for the various quantities, A2: a; A3: b, A4: c and so forth down to A10: i.  In A12 enter a*b and in A13 enter a^b. A14 may hold Diff as will equal 0, but it looks cleaner without it, so skip to A15 and enter a*b*c and into A16 enter a^(b*c). In cell A18 enter a*b*c*d and in cell A19 enter a^(b*c*d). In cell A21 enter a*b*c*d*e and in cell A22 enter a^(b*c*d*e). In cell A24 enter a*b*c*d*e*f and in cell A25 enter a^(b*c*d*e*f). In cell A27 enter a*b*c*d*e*f*g and in cell A28 enter a^(b*c*d*e*f*g. In cell A30 enter a*b*c*d*e*f*g*h and in cell A31 enter a^(b*c*d*e*f*g*h). In cell A33 enter a*b*c*d*e*f*g*h*i and in cell A34 enter a^(b*c*d*e*f*g*h*i).
  6. Select column A and Edit Replace Find what: a Replace with: a_ Replace All, and do this for every letter through i_ replacing i. Then inspect carefully the formulas to see that it was all done correctly, else fix it manually. Then select rows 2:10 and Insert Names Create Names in Left Column, OK.
  7. Next, select the column labels that are formulas and paste them into their places. Copy A12:A13 and paste it to B12 and insert an = sign before the formula in the new location and hit enter for both cells B12 and B13. Copy A15:A16 and paste to C15 and insert an = sign before both formulas and hit enter. Copy A18:A19 and paste it to D18, insert an = sign before both formulas and hit enter. Copy A21:A22 and paste to E21, then insert an = sign before both formulas and hit enter. Copy A24:A25 and paste it to both F24 and G24 and insert an = sign before all 4 formulas and press enter for each one. Copy A27:A28 and paste to H27 and enter an = sign before each one and hit enter for each. Copy A30:A31 to I30 and enter an = sign before each formula and hit enter for each one. Copy A33:A34 and paste to J33 and enter an = sign before each formula and hit enter for each one.
  8. Select cell B14 and enter the formula w/o quotes "=B13-B12" and then copy this zero difference formula to beneath every formula pair you just pasted in, i,e, paste it to cell C17, D20, etc. until all are done including cell J35.
  9. Select cell B1 and enter the formula note with the leading space, "  =B3^(1/(B3-1))", as referred to above in Step 4 as "b^(1/(b-1)) = a." Select B3 and enter 25, and then select cell B2 and enter the formula w/o quotes "=B3^(1/(B3-1))" and watch the difference cell in B14 go to 0. with 15 trailing zeroes. The balancing figures you should get are 28.588245902073

GoalSeeking Finds the Answers

  1. Select cell C4 and enter 1.979 999 999 999 9, Select C3 and enter 25. Select C1 and enter 1 as the Initial Guess for goal seeking purposes. Select Cell C17 and do Tools Goal Seek...  Set cell C17, To value (input) 0, By changing cell (and click on cell) C2. The answer you should get in C2 = 1.08377795636471 and the balancing figures you should get are 53.6470088400507; so clearly there is more than one way to skin a cat than having the exponents reciprocate to 1.
  2. Select cell D2 and enter "=C2-B2+C2" as a simple means of decrementing. Copy cells C3:C4 and paste them to D3, In cell D4 enter 1 as an initial guess for goal seeking purposes. Select cell  D20 and do goal seeking by setting that cell to 0 and changing cell D4. D4's value should change to 0.0202020202020213 to make reciprocation come out to 1 for the 3 values, 0.0202020202020213, 25 and 1.9799999999999. Note that the balancing figure is the same as a_, 1.0240260766465 because of the reciprocation equaling 1.
  3. Go to cell E2 and enter "=D2-C2+D2" as a means of decrementing. Copy cells D3:D4 and paste them to cell E3. Into cell E5 enter 2 and into cell E6 enter 1. Trying to trick Excel here; let's see if it works. Select cell E23 and do Tools Goal Seek on cell E23 to value 0 using cell -- and click on cell E6. Excel was not fooled.
  4. Go to cell F2 and enter "=E2" and let's try another means of trickery. Copy cell F2 tp cell range F3:F6 and into cell F7 enter .5.Select cell F26 and do goal seeking as usual, using cell F7 at the end. Not fooled in the slightest. Converted the .5 to a 1 rather quickly..
  5. Go to cell G4 and enter "=E2-D2+E2". Copy cell range F3:F5 and paste it to G3. Enter 2 into cell G6 and 1 into cell G7. Goal seek by selecting cell G26 and doing Tools Goal Seek and confirm cell G26 and to value 0 and by changing cell G7. Not fooled at all.
  6. You can see the process here. Try your own values for a_ in H4, I4 and J4 as you add H8, I9 and J10 and goal seek by changing them. Find out what there is to learn from the process.

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.

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.

Related Articles

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