Solve a Medium Difficulty Neutral Operations Problem with Excel

The problem is: a+b+c+...+x+y+z = abc...xyz; i.e. the sum of 26 numbers must equal their product. Learn to solve this medium difficulty problem via goal seeking and an algorithmic approach. This problem has many solutions, depending upon where one starts. Here is an example of a correct answer set, starting out as a+b=a*b=2πr=Circumference of circle C.

Steps

The Tutorial

  1. Start by opening a new Excel workbook and create two worksheets, DATA and SAVES. Save the workbook into a logical file folder.
  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 spaces as this problem involves precise goal seeking; 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. Begin by learning the basics of neutral operations: for a+b = a*b = c, with a and b constant values, addition is a neutral operation versus multiplication. Taking then a+b-b=ab-b and simplifying the left while factoring b out on the right, leaves a =  b(a-1). Dividing both sides by (a-1) and simplifying leaves a/(a-1) =b, Since ab = c, it's also true that a^2/(a-1) = c. Because a could have been subtracted first instead of b, and because addition and multiplication are both commutative, it's also true that b/(b-1) = a and b^2/(b-1) = c as well. Neither a nor b then may equal 1 lest division by 0 result in the denominator.
  4. Click in the upper left corner of the DATA worksheet between the 1 of Row 1 and the A of column A in order to select the entire worksheet. Do Format Cells Number Number Decimal Places 15 OK and Format Cells Font Size  9 or 10 or whatever you're comfortable viewing. Select columns B:G and set column width to 20.17. Select column A and set column width to 4.
  5. Enter the column headings. B1: a; C1: b:z; D1: Result c; E1: Goal; F1: 0 (which would appear with many trailing zeroes).
  6. Enter row 2 data and formulas. B2: 5; D2: (w/o quotes) "=B2^2/(B2-1)"; E2: "=2*PI()"; F2: "=E2-D2" w/o quotes.

Goal Seeking Finds the Answer

  1. Perform Goal Seeking on row 2 as follows. Select cell F2 and go to menuitem Tools Goal Seek, Accept F2, hit tab twice and enter To value: 0, then enter By changing cell: and click on cell B2, OK. After a moment, B2's value should change to 5.03537698500395 and F2 should equal 0. Now the a value for arriving at 2π via Neutral Operations is known, and b remains to be determined. But b = a/(a-1) whereas c = a^2/(a-1).
  2. Enter row 3 data and formulas as follows, w/o quotes:  A3: a,b; B3: "=B2"; C3: "=B3/(B3-1)"; D3: "=B3+C3"; E3: "=B3*C3"; F3: "=E3-D3". Do Edit Go To cell range F3: F51 and do Edit Fill Down.
  3. Enter row 4 formulas as follows w/o quotes:  B4: "=D2". D4: "=B4^2/(B4-1)"; E4: " "=D4".
  4. Enter row 5 formulas as follows. w/o quotes: A5: c; B5: "=B4"; C5: "=B5/(B5-1)"; D5: "=B5+C5" E5: "=B5*C5".
  5. Now the work gets much simpler. Copy cell range B4:E5 and Paste it to the following alternate cells:  B6, B8, B10. B12, B14, B16, B18, B20, B22. Select cell range B4:E23 and copy it and paste it to cell B24. Select cell range B48:E49 and copy it to cell B50.
  6. Go to cell A7 and type d and skip down two cells and type e and skip down two cells to cell A11 and type f and continue in this manner until you reach cell 51 and type z into it.
  7. Edit Go To cell range A3:A51 and copy it. Go to cell A55 and paste it and do menuitem Data Sort Sort by Columns: Column A; Sort on: Values; Order: A to Z; Color/Icon is blank, OK. Select row 56 and do Insert Row and type b and delete b from the a,b found in cell A55.
  8. Select cell B55 and enter w.o quotes "=B3"; select cell B56 and enter "=C3", in cell B57 enter "=C5"; in cell B58 enter "=C7" and continue in this manner, skipping a referred-to row until you reach cell B80 and enter "=C51".
  9. Select cell B81 and enter the formula w/o quotes "=SUM(B55:B80)" and the answer should be 31.984453366650600
  10. Select cell range C55:C80 and with cell C55 the active highlighted cell enter the formula w/o quotes "=B55" and Edit Fill Down.
  11. Select cell C81 and enter the formula w/o quotes "=C55*C56*C57*C58*C59*C60*C61*C62*C63*C64*C65*C66*C67*C68*C69*C70*C71* C72*C73*C74*C75*C76*C77*C78*C79*C80" and the answer should be 31.984453366650600 thus proving that the sum of a to z = the product of a to z. Done.
  12. Copy and save the worksheet to the SAVES worksheet. In Tips. you'll learn how to change it to take other numbers. Save the workbook.

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

  • By resetting the first goal in cell C2 to say 10 and re-performing the goal seeking operation, via selecting cell F2 and setting it as the cell to reach value 0 by changing cell B2, the entire worksheet will recalculate in a flash and the sum and product will agree at 35.300624683154300.
  • It's possible to change the logic of the spreadsheet to be a-b=ab or a+b=a/b or a-b = a/b or ab = a^b. Just work through the algebra first so it's known what c equal in terms of a and what b equals in terms of a also first. Also try a^2 + b^2 = a^2 * b*2 = c^2 where b^2 = a^2/(a^2-1) and c^2 = a^4 / (a^2 - 1).. Google NeuOps-Graphs01 and similar sites you'll find for more info on Neutral Operations.

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, depending upon your Settings. 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

Sources and Citations

  • Uses file "a+b+c...z = abc.xlsx"
Retrieved from "https://kipkis.com/index.php?title=Solve_a_Medium_Difficulty_Neutral_Operations_Problem_with_Excel&oldid=302140"