How to Solve a Difficult Neu Ops Problem by Goal Seeking in Excel
Create a new workbook in Microsoft Excel with 2 worksheets: Data and Saves., Set Preferences under the Excel Menu, with Calculation - Goal Seeking Preferences being particularly important., Click in the upper left hand corner of the Data worksheet...
Step-by-Step Guide
-
Step 1: Create a new workbook in Microsoft Excel with 2 worksheets: Data and Saves.
Save the workbook into a logical file folder. -
Step 2: Set Preferences under the Excel Menu
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. ,, 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. , 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). , 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. , 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. , until all are done including cell J35. , 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 , 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. , 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. , 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. , 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.. , 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. , 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. , 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: with Calculation - Goal Seeking Preferences being particularly important.
-
Step 4: Click in the upper left hand corner of the Data worksheet to select the entire worksheet
-
Step 5: between the 1 and the A
-
Step 6: and Format Cells Font 9 or 10 if you're comfortable with that and Format Number Number Decimal Places 15.
-
Step 7: 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.
-
Step 8: Set the problem out.
-
Step 9: Set up the worksheet.
-
Step 10: Select column A and Edit Replace Find what: a Replace with: a_ Replace All
-
Step 11: and do this for every letter through i_ replacing i.
-
Step 12: select the column labels that are formulas and paste them into their places.
-
Step 13: 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
-
Step 14: paste it to cell C17
-
Step 15: Select cell B1 and enter the formula note with the leading space
-
Step 16: " =B3^(1/(B3-1))"
-
Step 17: as referred to above in Step 4 as "b^(1/(b-1)) = a."
-
Step 18: Select cell C4 and enter 1.979 999 999 999 9
-
Step 19: Select C3 and enter 25.
-
Step 20: Select cell D2 and enter "=C2-B2+C2" as a simple means of decrementing.
-
Step 21: Go to cell E2 and enter "=D2-C2+D2" as a means of decrementing.
-
Step 22: Go to cell F2 and enter "=E2" and let's try another means of trickery.
-
Step 23: Go to cell G4 and enter "=E2-D2+E2".
-
Step 24: You can see the process here.
-
Step 25: 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 26: Geometric and/or Trigonometric Art
-
Step 27: Charting/Diagramming and Algebraic Formulation relating to Neutral Operations.
Detailed Guide
Save the workbook into a logical file folder.
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. ,, 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. , 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). , 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. , 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. , until all are done including cell J35. , 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 , 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. , 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. , 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. , 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.. , 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. , 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. , 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
Emma Peterson
Experienced content creator specializing in DIY projects guides and tutorials.
Rate This Guide
How helpful was this guide? Click to rate: