How to Solve Cubic Equation in Excel: with 3 Easy Steps
Prologue
In this article, you’ll learn how to solve the cubic equation in Excel with simple steps.
It takes a lot of time to solve the polynomial equation by hand. You can find the answer instantly if you use Excel.
In cubic equations, you’ll have to deal with the imaginary and real roots. Moreover, there is no built-in way to do this in Excel. You can use the Solver or the Goal Seek feature to get a single root, but if you utilize the calculator from this article, you’ll find all three roots of a cubic equation with ease.
The calculator can be seen in use in the image below.
You can download the Excel file for free at the end of this article.
Solve Cubic Equation in Excel
There are 3 steps to solving a cubic equation in Excel. The formulas are quite large and complex. You can read this article from Pearson to learn how the formulas were derived.
You can live preview the cubic equation calculator in Google Sheets from below.
Let’s see how to build this.
Typing Coefficients
In this first step, you’ll type the coefficients of your equation. The equation is x^3-8x^2-18x+72=0, and the model equation is ax^3+bx^2+cx+d=0. Compare the two equations and enter the values a, b, c, and d in cell range C8:C11.
To avoid problems with the plus and minus signs, make sure to rearrange your equation in a way that is similar to the reference equation.
Identifying Root Type
Three different root configurations are possible for a cubic equation: all real, two real, or just one real root. In this step, you’ll learn how to identify the root type of a cubic equation.
- Firstly, use this formula in cell C13.
=C10-((C9)^2/3)
This formula finds a variable known as ‘p’, which will be used to find the discriminant.
- Secondly, insert another formula in cell C14 to find the value of variable ‘q’.
=C11-((C9*C10)/3)+((2*(C9)^3)/27)
- Thirdly, use this formula in cell C15 to return the discriminant value for the cubic equation.
=-4*C13^3-27*C14^2
- Finally, apply the following formula to determine the root type of your cubic equation.
=IF(C15=0,”2 Equal Roots”, IF(C15<0,”Single Real Root”,”All Real Roots”))
- Based on the value of the determinant, the root type changes.
- Determinant 0: 2 Equal roots.
- Determinant Negative: Single real root.
- Determinant Greater than Zero: All real roots.
Next, this result will be used to choose which formula to use for solving cubic equations.
Finding Cubic Roots in Excel
In this final step, you’ll get three formulas to find the three roots of the cubic equation. Type this formula in cell C19 to return the first root of the equation. This formula uses the IF, SQRT, and ACOS functions.
=IF(C16=”2 Equal Roots”,(-4*C14)^(1/3)-(C9/3),IF(C16=”Single Real Root”,((-C14/2)+SQRT((C13/3)^3+(C14/2)^2))^(1/3)+((-C14/2)-SQRT((C13/3)^3+(C14/2)^2))^(1/3)-(C9/3),2*SQRT(-C13/3)*COS((1/3)*ACOS((-C14/2)*(3/-C13)^(3/2)))-C9/3))
After that, insert this formula in cell C20 to get the second root of the cubic equation. This formula uses the IF, IMSUB, COMPLEX, SQRT, ACOS, and PI functions. The IMSUB and COMPLEX functions deal with the imaginary number.
=IF(C16=”2 Equal Roots”,(C14/2)^(1/3)-(C9/3),IF(C16=”Single Real Root”,IMSUB(COMPLEX(-0.5*(((-C14/2)+SQRT((C13/3)^3+(C14/2)^2))^(1/3)+((-C14/2)-SQRT((C13/3)^3+(C14/2)^2))^(1/3)),0.5*(((-C14/2)+SQRT((C13/3)^3+(C14/2)^2))^(1/3)-((-C14/2)-SQRT((C13/3)^3+(C14/2)^2))^(1/3))*SQRT(3)),COMPLEX(C9/3,0)),2*SQRT(-C13/3)*COS((1/3)*ACOS((-C14/2)*(3/-C13)^(3/2))+(2*PI()/3))-C9/3))
Lastly, type the following formula in cell C21 to calculate the third and final root.
=IF(C16=”2 Equal Roots”,(C14/2)^(1/3)-(C9/3),IF(C16=”Single Real Root”,IMSUB(COMPLEX(-0.5*(((-C14/2)+SQRT((C13/3)^3+(C14/2)^2))^(1/3)+((-C14/2)-SQRT((C13/3)^3+(C14/2)^2))^(1/3)),-0.5*(((-C14/2)+SQRT((C13/3)^3+(C14/2)^2))^(1/3)-((-C14/2)-SQRT((C13/3)^3+(C14/2)^2))^(1/3))*SQRT(3)),COMPLEX(C9/3,0)),2*SQRT(-C13/3)*COS((1/3)*ACOS((-C14/2)*(3/-C13)^(3/2))+(4*PI()/3))-C9/3))
Let’s try another equation and see if our calculator can solve it effectively.
We’ve used the equation x^3-X^2+x-1=0. The roots of the equation are 1, i, and -i. If we input 1 into the equation, then 1^3-1^2+1-1=1-1+1-1=0. So, the calculator successfully finds the root of this equation.
Things to Remember
- You’ll only input the equation and the coefficients in cell ranges B3 and C8:C11 respectively. Other cells are locked. This will prevent accidental changes to the calculator.
- The equation is based on the derivation of the quadratic equation.
- You can create three formulas for each root type to keep the formulas short.
- Online calculators like CalculatorSoup can be used to validate the findings of this article.
Answers to Commonly Asked Questions
Question 1: How do I solve quadratic equations with Excel?
ExcelFlash: Firstly, you need to list all the coefficients of the quadratic equation. Then, you’ll need to apply the following two formulas to the two roots.
=(-C6+SQRT(C6^2-4*C5*C7))/(2*C5)
=(-C6-SQRT(C6^2-4*C5*C7))/(2*C5)
Question 2: How do I solve linear equations?
ExcelFlash: You can use the following formula to solve 2 linear equations in Excel. If you’re using Excel 365, then you can simply hit Enter for this formula. However, if you’re using earlier versions, first you’ll need to select the cell range C8:C9 and type the formula. After that, press Ctrl+Shift+Enter to input the array formula.
=MMULT(MINVERSE(B5:C6),D5:D6)
Question 3: What are the forms of quadratic equations?
ExcelFlash: There are three forms of quadratic equations. The following table lists them in brief, considering the base equation is ax^2+bx+c.
Form | Equation |
Standard Form | y = a*x^2+b*x+c |
Factored Form | y = (a*x+c)*(b*x+d) |
Vertex Form | y = a*(x+b)^2+c |
Download Section
You can download the Excel file for this article from below.
Epilogue
You’ve seen 3 steps to solve the cubic equation in Excel. If you have any feedback for us, feel free to comment below. Additionally, You can also follow ExcelFlash on Twitter and YouTube.
Wοw, this piece of writing is fastidious,
my youngeг sister is analyzing these kinds of things, so I am going to convey her.
Thank you. Glad to hear this.
Nice bⅼоg here! Also your web site lоads up fast! What host are you using?
Can I get your ɑffiliate link to yⲟur host? I wish my site loaded up
as quickly aѕ yours lol