Goal Seek: Solving cubic equations

Let's say you you know the desired result of some formula, but you need to find possible input value(s) to achieve that result. You may even know the approximate values of those parameters. However, it's probably nothing more than guessing, and you need exact values.

In such cases it's better to use Excel's Goal Seek tool which helps to arrive at exact solutions very efficiently.

I'm providing here an example of using the Goal Seek. The example shows how to setup your worksheet for solving a cubic equation, such as Ac*x^3+Bc*x^2+Cc*x+Dc=0, where I renamed the coefficients a,b,c and d to Ac,Bc,Cc and Dc.

In this case we want to find the roots for this specific equation:

2*x^3-15*x^2-195*x+990=0

So, you're looking for such values of X that turn the equation into correct equality.

You can try to begin with guessing some starting values for the roots, but in practice it's usually better to create a simple small table for a range of X values and get chart based on the table, like in this example:

This way you can make quite good guesses for approximate values of the roots (=crossings of X axis) and use them as the starting values in calculations.

After this initial step you can now proceed as follows:

• Enter Ac,Bc,Cc,Dc into cells B5:B8
• Use Formulas>Define Name, and in the New Name box enter values as shown here:
  
  
  
• Repeat the definition for Bc, Cc and Dc, referring them to $C$6, $C$7, $C$8.
  
• Enter the equation coefficients: 2,-15,-195,990 into cells C5:C8.
• Enter the formula =2*x^3-15*x^2-195*x+990 into cell D5 and copy it to cells D6 and D7.
• In cells E5:E7 enter the starting (guessed) X values that you can read from the chart created earlier, e.g. -10,5 and 12.
• Select cell D5 and use Data>What-if Analysis>Goal Seek...
• Fill the Goal Seek box as you see here:

Repeat the step with the Goal Seek box for cell D6, entering D6,0,E6, and for cell D7, entering D7,0,E7.

Cells E5:E7 should display the values of roots, the exact solutions you were looking for, as you can see in this table:

Column C in this setup contains values we are certain of, they are given. Column D contains desired results (expected outcome of our equation). Column E shows the solutions, values of X that satisfy the equality of both sides of the equation.

The table can be used as a template for any of polynomial functions (cubic equations) you'd need to solve. All you'd need to use it would be:

1. 'guessing' and entering the starting values for the roots in cells E5:E7
2. entering coefficients of your equation into cells C5:C8
3. entering your equation into cells D5:D7, and then
4. running Goal Seek from cells D5, D6 and D7, consecutively.