#1 Excel tutorial on the net
- Quadratic Equation
A quadratic equation is of the form ax 2 + bx + c = 0 where a ≠ 0. A quadratic equation can be solved by using the quadratic formula. You can also use Excel's Goal Seek feature to solve a quadratic equation.
1. For example, we have the formula y = 3x 2 - 12x + 9.5. It's easy to calculate y for any given x. For x = 1, y = 0.5
2. For x = 2, y = -2.5
3. But what if we want to know x for any given y? For example, y = 24.5. We need to solve 3x 2 - 12x + 9.5 = 24.5. We can solve the quadratic equation 3x 2 - 12x + 9.5 - 24.5 = 0 by using the quadratic formula.
4. You can use Excel's Goal Seek feature to obtain the exact same result. On the Data tab, in the Forecast group, click What-If Analysis.
5. Click Goal Seek.
The Goal Seek dialog box appears.
6. Select cell B2.
7. Click in the 'To value' box and type 24.5
8. Click in the 'By changing cell' box and select cell A2.
9. Click OK.
Note: Excel returns the solution x = 5. Excel finds the other solution if you start with an x-value closer to x = -1. For example, enter the value 0 into cell A2 and repeat steps 5 to 9. To find the roots, set y = 0 and solve the quadratic equation 3x 2 - 12x + 9.5 = 0. In this case, set 'To value' to 0.
- What-If Analysis
Learn more, it's easy
- Data Tables
Download Excel File
- quadratic-equation.xlsx
Follow Excel Easy
Become an Excel Pro
- 300 Examples
Most Popular
- Pivot Tables
- Formulas and Functions
- Conditional Formatting
- Find Duplicates
- Drop-down List
- Index and Match
Next Chapter
Quadratic Equation • © 2010-2023 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA
Statistics Made Easy
How to Solve a Quadratic Equation in Excel (Step-by-Step)
A quadratic equation takes the following form:
ax 2 + bx + c = y
Often you will be given the value for y and will be asked to solve for the value of x .
For example, suppose we have the following quadratic equation:
4x 2 – 20x + 16 = -8
It turns out that setting x = 3 or x = 2 will solve this equation.
To solve quadratic equations in Excel, you can use the Goal Seek function.
The following step-by-step example shows how to use the Goal Seek function in practice.
Step 1: Enter the Equation
First, let’s enter some random value for x and the formula for the quadratic equation for y:
Step 2: Find the First X Value Using Goal Seek
Next, click the Data tab along the top ribbon, then click the What-If Analysis button, then Goal Seek :
In the new window that appears, specify that you’d like to set cell B2 equal to -8 by changing the value in cell A2 :
Once we click OK , the Goal Seek function will automatically find the value for x that solves the equation:
Goal Seek finds that the value x=2 (assuming 1.9999 rounds to 2) solves the quadratic equation.
Step 3: Find the Second X Value Using Goal Seek
To find the second x value that solves the quadratic equation, set the initial x-value to a different number.
For example, we could choose to set the initial x-value to 4:
We can then run the Goal Seek function again and see that it finds a new solution of x=3 :
Thus, the two x-values that can solve this quadratic equation are x=2 and x=3 .
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Solve a System of Equations in Excel How to Plot an Equation in Excel
Published by Zach
Leave a reply cancel reply.
Your email address will not be published. Required fields are marked *
How To Solve a Quadratic Equation
Generic Formula
There are two possible solutions for any quadratic equation. This is one possible solution.
This is the other possible solution.
- A – This is the coefficient of the squared term in the quadratic equation.
- B – This is the coefficient of the single powered term in the quadratic equation.
- C – This is the constant in the quadratic equation.
What It Does
These formulas will give the solutions to a quadratic equation of the form Ax^2 + Bx + C = 0 .
How It Works
This is a simple algebraic formula and uses the SQRT function which returns the square root of a given number and the ^ operator which raises a given number to a given power.
In our example x^2 – 3x + 2 = 0 the solution is given by =(-(-3)+SQRT(-3^2-4*1*2))/(2*1) which results in 2 . Similarly the other solution is 1 .
Not every quadratic equation has a solution in the real numbers, when this is the case the formula will result in a #NUM! error.
About the Author
John MacDougall
Subscribe for awesome Microsoft Excel videos 😃
I’m John , and my goal is to help you Excel!
You’ll find a ton of awesome tips , tricks , tutorials , and templates here to help you save time and effort in your work.
- Pivot Table Tips and Tricks You Need to Know
- Everything You Need to Know About Excel Tables
- The Complete Guide to Power Query
- Introduction To Power Query M Code
- The Complete List of Keyboard Shortcuts in Microsoft Excel
- The Complete List of VBA Keyboard Shortcuts in Microsoft Excel
Related Posts
9 Ways to Show Formulas in Microsoft Excel
Aug 12, 2022
This post is going to show you all the different ways you can show the...
How To Conditionally Concatenate A Range
Jan 6, 2018
This formula will conditionally concatenate a range based on a criteria in another range.
How To Select A Random Item From A List
Nov 28, 2017
This formula will return a random selection from a given list with each item in the list having an equal probability of being selected.
Get the Latest Microsoft Excel Tips
Follow us to stay up to date with the latest in Microsoft Excel!
- CELL REFERENCE
Post your problem and you'll get expert help in seconds
- All articles
How to solve quadratic equation in excel
We can solve quadratic equations in excel by inputting an equation that contains our variable (e.g. X), then we would define X using a formula. Thereafter, we would use this quadratic formula to solve for X using some given roots of the equation. This tutorial will teach use how to solve quadratic equation in excel.
Data for Solving Quadratic Equation
- To do this, we will type in our quadratic equation y = a + bx + cx^2 and also define the root of the variable “ X ” by typing this quadratic formula x0 = [-b ± SQRT(b^2 - 4ac]/2a
- We will now prepare a table for the roots of “X” which are “x1” and “x2”, and ascribing values for the variables in the equation of “X” which are “a, b, and c”
- We will type this formula into Cell B11 =(-B8+SQRT(B8^2-4*B7*B9))/(2*B7)
- We will now press the enter key
- We will now repeat the same operation for “ x2 ” by copying, pasting and changing “+” sign into a “ – “minus sign in the formula in Cell B12 as =(-B8-SQRT(B8^2-4*B7*B9))/(2*B7)
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment
Subscribe to Excelchat.co
Get updates on helpful Excel topics
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
Save time by asking instead! Most questions solved and answered in 10 minutes.
How to solve quadratic equations in Excel - Goal Seek
by Ilker | Oct 29, 2019 | Excel Tips & Tricks
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.
Download Workbook
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 2 nd 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) .
5x² + 6x + 1 = 0 => =5*POWER(x,2) + 6*x + 1 - y
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
- After Excel finished the Goal Seek execution, the Goal Seek Status dialog will display the calculated value
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.
Related posts
Recent Posts
- Effective Project Management with SpreadsheetWeb
- SpreadsheetWeb Hub’s New Run Feature
- Creating and Automating Workflows with SpreadsheetWeb
- Easy Data Management and Analysis with SpreadsheetWeb
- The Most Popular Spreadsheet Software: Microsoft Excel
Something went wrong. Wait a moment and try again.
- Create a ShowMe
- Community Guidelines
- Edit Profile
- Edit ShowMes
- Students' ShowMes
- My Subscriptions
- ShowMe Ambassadors
- solving-equations
- how to use function notation
- How to use geometrigraph
- excel conditional formatting
- Microsoft excel 2013
- excel macros
You must be logged into ShowMe
Are you sure you want to remove this ShowMe? You should do so only if this ShowMe contains inappropriate content.
- > Excel Tips
- > Formulas
Solving a Quadratic Equation
Written by Allen Wyatt (last updated October 12, 2019) This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
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:
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:
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 Microsoft 365.
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. Learn more about Allen ...
MORE FROM ALLEN
Determining the Month of the Year
Your macro code may need to determine the month of the year represented by a particular date. You can find the desired ...
Converting to Automatic Endnotes
When you add endnotes to a document, they are automatically maintained and renumbered by Word, as necessary. If you get a ...
Missing Row Numbers
By default, Excel shows row numbers at the left side of your program window. If you cannot see the row numbers, there are ...
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
More ExcelTips (ribbon)
Locating a Single-Occurrence Value in a Column
Given a range of cells containing values, you may have a need to find the first value in the range that is unique. This ...
Inserting Dashes between Letters and Numbers
If you need to add dashes between letters and numbers in a string, the work can quickly get tedious. This tip examines ...
Checking for Duplicate Rows Based on a Range of Columns
When working with data in Excel, you might want to figure out which rows of data represent duplicates of other rows. If ...
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
View most recent newsletter.
(Your e-mail address is not shared with anyone, ever.)
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.
(gif, jpeg or png only, 5MB maximum file size)
Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
What is 9 - 5?
2019-10-14 11:31:09
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
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!
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
View the most recent newsletter.
Links and Sharing
- ExcelTips FAQ
- Questions or Comments
- Great Branding Ideas
- Free Calendars
OUR COMPANY
- Sharon Parq Associates, Inc.
- About Tips.Net
- Advertise with Us
- Our Privacy Policy
- Author Index
OUR PRODUCTS
- Helpful E-books
- Newsletter Archives
- Video Courses
- Excel Products
- Windows Products
- Word Products
- Beauty and Style
- ExcelTips (Excel 97–2003)
- ExcelTips (Excel 2007–2021; 365)
- Home Improvement
- Money and Finances
- Pests and Bugs
- Pets and Animals
- WindowsTips (Microsoft Windows)
- WordTips (Word 97–2003)
- WordTips (Word 2007–2021; 365)
Copyright © 2023 Sharon Parq Associates, Inc.
Quadratic Equation Solver
[ back to excel vba sample code ].
We have presented to you a quadratic equation solver in the Visual Basic 6 Tutorial . You can also create a similar program using MS Excel Editor. In fact, it is easier to do it in MS Excel as you just enter the values into the cells rather than having to create the text boxes. So for those of you who are without a copy of MS Visual Basic compiler, but you have MS Office, you can copy the code and try this program out in your MS Excel
ax 2 +bx+c , where a,b,c are constants.
The number of roots depends on the determinant of b 2 -4ac
If b 2 -4ac>0 then there are two roots
If b 2 -4ac=0 then there is only one root
If b 2 -4ac<0 then there is no root.
By making use the above conditions and employ the use of If....Then...Else statements, the program is able to solve the quadratic equation. The output is as shown in Figure 12.1
Figure 12.1
Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved
Contact: [email protected] excelvbatutor.com [Privacy Policy]
IMAGES
VIDEO
COMMENTS
To calculate the discriminant of a quadratic equation, put the equation in standard form. Substitute the coefficients from the equation into the formula b^2-4ac. The value of the discriminant indicates what kind of solutions that particular...
Some examples of jobs that use quadratic equations are actuaries, mathematicians, statisticians, economists, physicists and astronomers. In math, a quadratic equation is defined as a polynomial equation that has one or more terms and the va...
According to Math Is Fun, real-world examples of the quadratic equation in use can be found in a variety of situations, from throwing a ball to riding a bike. In each example, the predictive qualities of the quadratic equation can be used t...
A quadratic equation is of the form ax2 + bx + c = 0 where a ≠ 0. A quadratic equation can be solved by using the quadratic formula.
How to Solve a Quadratic Equation in Excel (Step-by-Step) · Step 1: Enter the Equation · Step 2: Find the First X Value Using Goal Seek · Step 3:
This is a simple algebraic formula and uses the SQRT function which returns the square root of a given number and the ^ operator which raises a given number to
We can solve quadratic equations in excel by inputting an equation that contains our variable (e.g. X), then we would define X using a formula.
Subscribe Now:http://www.youtube.com/subscription_center?add_user=ehowtechWatch More:http://www.youtube.com/ehowtechThe quadratic formula is
Subscribe Now:http://www.youtube.com/subscription_center?add_user=EhowtechWatch More:http://www.youtube.com/EhowtechExcel is such a powerful
Using Goal Seek to solve quadratic equations in Excel · Select the cell that contains the formula · Open the Goal Seek dialog in Data > Data Tools
You can easily solve a quadratic equation in Excel · For Example, you have a equation, y= 4x^2 - 6x+9 · you can easily calculate the Y for any given x =2 and so.
How to Use Excel to Solve a Quadratic Equation by Rick Hoban - January 27, 2013.
Clyde wonders if there is a function in Excel that solves quadratic equations. The short answer is that there is no such worksheet function
Explanation: · The format of the quadratic equation is as below: · ax2+bx+c , where a,b,c are constants. · The number of roots depends on the determinant of b2-4ac.