A quadratic function is a type of equation that contains a squared variable. It is called quadratic because quad means square in Latin. The quadratic functions usually have a structure like ax² + bx + c = 0, where x represents an unknown variable, and a, b, and c represent known constants. In this guide, we are going to show you how to solve quadratic equations in Excel.
Thanks to Excel's features, we can list you 3 different way to solve quadratic equations. These ways includes Goal Seek feature of Excel as well as a manual calculation method and a custom formula which can be created via VBA.
The first part of our guide focuses on Goal Seek feature. You can find the manual and VBA methods in the the second part.
Goal Seek is among Excel's most important features. Its purpose is exactly what we're looking to do here - to find the variable in an equation. Goal Seek uses an iterative calculation process to "guess" the value of a cell value used in the workbook formulas. Based on this, a quadratic equation can be formulated to be run in Excel. Let's now see how you can do this.
Creating a quadratic equation in Excel
A quadratic equation should at least have one squared variable. To do this, you can simply multiply the variable by itself, calculate he 2nd power of the variable using the power operator ^ or use the POWER function as in our example.
The other important part is to refer a cell as variable, x. Our formula uses named range x and y for the unknown variables and the formula result respectively (x is C7 and y is D7).
Note: Although adding y into the calculation isn't necessary if you always set it 0, we included it in our example for versatility.
Using Goal Seek to solve quadratic equations in Excel
Once the formula is ready in your spreadsheet, it is time to use Goal Seek to solve the quadratic function. Follow the steps below to find one of the variables of the equation:
- Select the cell that contains the formula
- Open the Goal Seek dialog in Data > Data Tools > What-If Analysis > Goal Seek
- Set cell is the formula cell (It should be automatically selected)
- To value should be 0, which is the right end of the equation
- Select the cell of the x value to the By changing cell
- Click OK to start Goal Seek
- After Excel finished the Goal Seek execution, the Goal Seek Status dialog will display the calculated value
- Click OK to close the dialog
Results of Goal Seek
Although the process is pretty straightforward, there are some downsides to using Goal Seek. As you may have noticed in our screenshots, Goal Seek may fail to find the exact value you want. For example, in our example we wanted Goal Seek to find a value that makes our formula 0. However, the value makes the formula return a number close to 0, 0.000121893.
If you check the x value, you will see a value very close to -0.2, -0.199969528007691. You need to manually enter -0.2 to see the formula return 0.
This difference comes from calculation logic of Goal Seek. Goal Seek continues searching a certain number of times until it finds a value closer to the target value within the precision limits specified. The default limits for the iteration number and precision value are 100 and 0.001. You can adjust these from File > Options > Formulas.
Note: Decrease the Maximum Change value to increase the precision.
Another important point while using Goal Seek is that you need to choose (guess) a starting point for Goal Seek. Goal Seek finds a single variable, but a quadratic function can have 2 variable options. We found -0.2 value when we set 0 to x. If you're satisfied with the results, you can change the x and run Goals Seek again. For example, setting 10 to x, and running Goal Seek finds another x value, -1.
Please see out the second part to see how to solve quadratic equations using regular functions and VBA macros.