Solving a Quadratic Equation

by Allen Wyatt
(last updated October 12, 2019)

2

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:

(-b+SQRT(b^2-4ac))/2a
(-b-SQRT(b^2-4ac))/2a

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:

=(-B1+SQRT(B1^2-4*A1*C1))/(2*A1)
=(-B1-SQRT(B1^2-4*A1*C1))/(2*A1)

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:

https://www.excelforum.com/excel-formulas-and-functions/1116652-how-to-view-complex-number-from-quadratic-equation.html

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:

https://www.wikihow.com/Solve-a-Quadratic-Equation-Using-the-Goal-Seek-Feature-on-Microsoft-Excel

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Conditional Format that Checks for Data Type

Conditional formatting can be used to highlight cells that contain the improper type of data for your needs. This tip ...

Discover More

Using Alternating Styles

Alternating styles can come in handy when you have to switch between one type of paragraph and another, automatically, as ...

Discover More

Controlling How Excel Interprets Percentages

When entering data in a worksheet, Excel tries to figure out how your entry can best be shown on the screen. When it ...

Discover More

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!

More ExcelTips (ribbon)

Calculating an Average within a Date Range

Need to find an average of the values that fall within a date range? Excel provides a number of ways you can do this, ...

Discover More

Dealing with Long Formulas

If your worksheet formulas seem to go on forever, here's a handy way to make them more understandable. (All you need to ...

Discover More

Counting Occurrences of Words

If you would like to determine how many instances of a particular word appear within a range of text, there are several ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 7?

2019-10-14 11:31:09

Roy

Also, there is an "inverse" (probably there is a better word, I got that one with Goal Seek...) form of the equation that used to be important given the calculational tools of the day (pen and paper, sliderules).

Its use was suggested when "a" in the equation was very small compared to the other values. Given Excel's difficulties with digits past 14 or so (binary math), one could probably find that one better to use for what is effectively the same reason. A simple IF() test could let your formula select between the usual version and that one. Again, the point would be to work within the accuracy of the calculational tool, not that either is right or wrong (one just uses algebra to shift things about, rearrange them, not use a whole different formula, so essentially it is the same formula, just rearranged).


2019-10-14 11:23:49

Roy

Yes, using the QF is ALWAYS the way to go.

Goal Seek's only role in life is for things that don't have precise formulas or require math beyond your skill set and you can't find an exact solution which you can adapt, usually for the same reason, skill set-wise.

That's because it simply isn't precise and it's certainly not a two second throwaway thing like typing =54+82 instead of firing up a calculator or doing it in your head is. So if a precise formula exists which requires no real adaptation to your needs...

By the way, if that seems harsh on Goal Seek, part of it is that I have never seen a real question that could use Goal Seek that wasn't a troll where the questioner will preach about it if no answerer does. Only professional Excel people seem to treat it as something to solve real things, rather than something to solve anything and everything. The other part is that using Goal Seek here is just as... sad... as using handwritten graphing to "solve" the equation. Only matches the right answer by accident. The fact that one worked to get that accident doesn't make it "correct." Since the QF is simple, needs no real adaptation, and one can simply round at whatever digit one pleases, why use a hit-or-miss method that is more work? So... harsh on good ol' Goal Seek. Use it where it is STRONG, not where it is lucky at best and more trouble anyway!


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.