How to Solve a Medium Difficulty Neutral Operations Problem with Excel
Start by opening a new Excel workbook and create two worksheets, DATA and SAVES., Set Preferences., 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...
Step-by-Step Guide
-
Step 1: Start by opening a new Excel workbook and create two worksheets
Save the workbook into a logical file folder. -
Step 2: DATA and SAVES.
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. , 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. , 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. , B1: a; C1: b:z; D1:
Result c; E1:
Goal; F1: 0 (which would appear with many trailing zeroes). , B2: 5; D2: (w/o quotes) "=B2^2/(B2-1)"; E2: "=2*PI()"; F2: "=E2-D2" w/o quotes. , 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). , Do Edit Go To cell range F3:
F51 and do Edit Fill Down. , D4: "=B4^2/(B4-1)"; E4: " "=D4". , w/o quotes:
A5: c; B5: "=B4"; C5: "=B5/(B5-1)"; D5: "=B5+C5" E5: "=B5*C5". , 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. ,, 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. ,,,, Done. , In Tips. you'll learn how to change it to take other numbers.
Save the workbook. , For more art charts and graphs, you might also want to click on Category:
Algebra, Category:
Mathematics, Category:
Spreadsheets or Category:
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. -
Step 3: Set Preferences.
-
Step 4: Begin by learning the basics of neutral operations: for a+b = a*b = c
-
Step 5: with a and b constant values
-
Step 6: addition is a neutral operation versus multiplication.
-
Step 7: 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.
-
Step 8: Enter the column headings.
-
Step 9: Enter row 2 data and formulas.
-
Step 10: Perform Goal Seeking on row 2 as follows.
-
Step 11: Enter row 3 data and formulas as follows
-
Step 12: w/o quotes: A3: a
-
Step 13: b; B3: "=B2"; C3: "=B3/(B3-1)"; D3: "=B3+C3"; E3: "=B3*C3"; F3: "=E3-D3".
-
Step 14: Enter row 4 formulas as follows w/o quotes: B4: "=D2".
-
Step 15: Enter row 5 formulas as follows.
-
Step 16: Now the work gets much simpler.
-
Step 17: 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.
-
Step 18: Edit Go To cell range A3:A51 and copy it.
-
Step 19: Select cell B55 and enter w.o quotes "=B3"; select cell B56 and enter "=C3"
-
Step 20: in cell B57 enter "=C5"; in cell B58 enter "=C7" and continue in this manner
-
Step 21: skipping a referred-to row until you reach cell B80 and enter "=C51".
-
Step 22: Select cell B81 and enter the formula w/o quotes "=SUM(B55:B80)" and the answer should be 31.984453366650600
-
Step 23: Select cell range C55:C80 and with cell C55 the active highlighted cell enter the formula w/o quotes "=B55" and Edit Fill Down.
-
Step 24: 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.
-
Step 25: Copy and save the worksheet to the SAVES worksheet.
-
Step 26: Make use of helper articles when proceeding through this tutorial See the Related LifeGuide Hubs below and the article How to Do the Sub Steps of Neutral Operations for a list of articles related to Excel
-
Step 27: Geometric and/or Trigonometric Art
-
Step 28: Charting/Diagramming and Algebraic Formulation relating to Neutral Operations.
Detailed Guide
Save the workbook into a logical file folder.
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. , 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. , 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. , B1: a; C1: b:z; D1:
Result c; E1:
Goal; F1: 0 (which would appear with many trailing zeroes). , B2: 5; D2: (w/o quotes) "=B2^2/(B2-1)"; E2: "=2*PI()"; F2: "=E2-D2" w/o quotes. , 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). , Do Edit Go To cell range F3:
F51 and do Edit Fill Down. , D4: "=B4^2/(B4-1)"; E4: " "=D4". , w/o quotes:
A5: c; B5: "=B4"; C5: "=B5/(B5-1)"; D5: "=B5+C5" E5: "=B5*C5". , 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. ,, 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. ,,,, Done. , In Tips. you'll learn how to change it to take other numbers.
Save the workbook. , For more art charts and graphs, you might also want to click on Category:
Algebra, Category:
Mathematics, Category:
Spreadsheets or Category:
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.
About the Author
Nathan Murphy
Professional writer focused on creating easy-to-follow DIY projects tutorials.
Rate This Guide
How helpful was this guide? Click to rate: