by Allen Wyatt
(last updated October 12, 2019)
Clyde wonders if there is a function in Excel that solves quadratic equations. The short answer is that there is no such worksheet function built into the program. You can, however, use a couple of formulas to solve a quadratic equation, or you can develop your own user-defined function.
This is the general form of a quadratic equation:
ax^2 + bx + c = 0
In the equation, values a, b, and c are constants and you need to solve for x. There are always two possible solutions for x, which means that there are two quadratic formulas that derive from the quadratic equation:
This is easy enough to place into a worksheet. Assuming that your values for a, b, and c are in cells A1, B1, and C1, you could use the following formulas:
You can, at this point, change the values in A1, B1, and C1 and end up with the answers you need. These formulas will work for any value where (B1^2)>(4*A1*C1). If this is not true, there are still answers, but you start getting into imaginary numbers. At this point things can get sticky in a hurry, and mere mortals run into the real possibility of their heads exploding. If you want to adjust your formulas to deal with imaginary numbers, you might find this short discussion helpful:
I should note that I've also seen people use Excel's Goal Seek tool to solve a quadratic equation, but for my money, the formulas described above work just fine. My feeling is that if you can use a formula to derive a solution, it seems to me that the whole need for doing the iterative work at which the Goal Seek tool is so adept is removed. If you prefer to work with the Goal Seek tool, however, you may find this web page helpful:
I mentioned earlier that you could build your own user-defined function to derive your solutions to a quadratic equation. Here is one that you could easily implement:
Function Quadratic(a As Double, b As Double, _ c As Double, r As Integer) As Variant Quadratic = CVErr(xlErrValue) If r = 1 Then Quadratic = CVErr(xlErrNA) If b ^ 2 > (4 * a * c) Then Quadratic = (-b + Sqr(b ^ 2 - (4 * a * c))) / (2 * a) End If End If If r = 2 Then Quadratic = CVErr(xlErrNA) If b ^ 2 > (4 * a * c) Then Quadratic = (-b - Sqr(b ^ 2 - (4 * a * c))) / (2 * a) End If End If End Function
The function requires four parameters: the values for A, B, and C, plus an indicator of whether you want the first (positive) answer or the second (negative) answer. Given three values in cells A1, B1, and C1, here's how you would get the second (negative) result in your worksheet:
=Quadratic(A1, B1, C1, 2)
If your indicator (the fourth parameter) is not 1 or 2, then the function returns a #VALUE! error. If the constants you provide for the first three parameters result in an imaginary answer, then the function returns a #NUM! error.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13686) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
If you have a lot of values in a single row, you might want to pull the last non-zero value from that row. There are a ...Discover More
Remember your number line from your early years in school? Some numbers can be below zero (negative numbers) and others ...Discover More
Sometimes it is helpful to look at data that is rotated 90 degrees. Excel allows you to use Paste Special to transpose ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.