How to Solve Nonlinear Equations in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

There are many ways to solve different types of equations in Excel. But to solve the nonlinear equations, we had to use the Solver Add-in of Excel. This add-in doesn’t remain pre-enabled in Excel, needs to be enabled from the Excel Options. In this article, we’ll show how to activate it and how to use it to solve nonlinear equations in Excel.


What Is a Nonlinear Equation?

For an equation, when the change of the output doesn’t get proportional to the change of the input and if the equation appears as a curved line in the graph, then it is called a Nonlinear equation or quadratic equation.

Now, by following some easy steps, we’ll solve the two nonlinear equations given below.


Step 1: Inserting Nonlinear Equation

First, we’ll have to insert the left side of the equations in two cells using the cell reference of the variables. But remember, we’ll have to consider the equation by keeping the right side zero.

Steps:

  • For the first equation, insert the following formula in Cell B9
=D9^2+D10^2-25
  • Then hit the ENTER button to finish.

Insert Equation to Solve Nonlinear Equations in Excel

  • And for the second equation, insert this formula in Cell B10
=D10-D9^2


Step 2: Creating Objective Equation to Solve Nonlinear Equation

For using the Solver Add-in, we need an objective equation. So, we’ll sum the two cells where we inserted the left side of the equation to use it as an objective equation.

Steps:

=SUM(B9:B10)

Create Objective Equation to Solve Nonlinear Equations in Excel


Step 3: Activating Solver Add-in to Solve Nonlinear Equation

The input process is done, now we’ll enable the Solver Add-in. If you have enabled it already or know how to do it, then skip this step.

Steps:

  • Click on the File tab first.

Activate Solver Add-in to Solve Nonlinear Equations in Excel

  • Next, select Options from the appeared menu.

Activate Solver Add-in to Solve Nonlinear Equations in Excel

  • Later, click as follows: Add-ins > Excel Add-ins > Go.

  • Finally, mark Solver Add-in and press OK.

Read More: How to Solve an Equation for X When Y is Given in Excel 


Step 4: Inserting Objective and Variable Cells in the Solver Add-in

Now see, the Solver Add-in is available in the Analyze section of the Data ribbon.

Steps:

  • Click as follows to open it: Data > Solver.

Open Solver Add-in to Solve Nonlinear Equations in Excel

Soon after you will get a window of Solver Add-in like the image below

The first action is to set the objective equation and variable cells.

  • Click on the upside arrow button from the Set Objective box as shown above and an input box will open up.
  • Select Cell D12 and hit ENTER.

Insert Objective and Variable Cells to Solve Nonlinear Equations in Excel

  • After that, mark the Value Of option and insert 0 in the value box.
  • Next, click on the upside arrow button from the By Changing Variable Cells

  • Select the cells of variables and press ENTER.

Read More: How to Solve 2 Equations with 2 Unknowns in Excel


Step 5: Enter Cell Reference and Constraint to Solve Nonlinear Equation

Here, we’ll insert the cell reference of the left side of the nonlinear quadratic equations and the constraints.

Steps:

  • Click on the Add button.

Insert Cell Reference and Constraints to Solve Nonlinear Equations in Excel

  • Insert Cell B9 in the Cell Reference box for the first equation.

Insert Cell Reference and Constraints to Solve Nonlinear Equations in Excel

  • Then choose = from the drop-down box and insert 0 in the Constraints box.
  • Next, press OK.

Insert Cell Reference and Constraints to Solve Nonlinear Equations in Excel

  • Following the same way, insert the cell reference and constraints for the second equation.

The Solver add-in can extract the solutions with a detailed answer report. Also, there are other options too, you can choose your most convenient one.

Steps:

  • Mark the Make Unconstrained Variables Non-Negative
  • And then just press the Solve button.

Output of the Nonlinear Equations

  • After a few moments, a dialog box will appear like this. Mark the Keep Solver Solution option and select Answer from the Reports section.
  • Finally, just press OK.

Output of the Nonlinear Equations

Here are our calculator variables.

nonlinear equation solved answer

The solver will show you the answer report in a new sheet like the image below.

Annual report

Read More: How to Solve Polynomial Equation in Excel


Things to Remember

  • To solve nonlinear equations, you must have to set an objective equation.
  • Don’t forget to mark the Make Unconstrained Variables Non-Negative
  • GRG Nonlinear should be selected by default as the solving method, if not you need to adjust that.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

That’s all for the article. I hope the procedures described above will be good enough to solve nonlinear equations in Excel. Feel free to ask any questions in the comment section, and please give me feedback.


Related Articles


<< Go Back to Excel Solve Equation | Excel Solver Examples | Solver in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

2 Comments
  1. x and y are switched in your solution. x is actually 4.524938 and y is 2.12719

    • Reply Avatar photo
      Fahim Shahriyar Dipto Nov 17, 2022 at 12:37 PM

      Hello Mr Martin. Thanks for your valuable feedback. We are grateful to you for informing us about the factor. According to your feedback, we cross-checked the issue. Unfortunately, the x and y values are switched unexpectedly somehow. we have updated the article. Thanks again.
      Regards,
      Fahim Shahriyar Dipto
      Excel and VBA Content Developer.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo