Featured Post

TNTET 2017 BREAKING NEWS

TNTET 2017 BREAKING NEWS | ஆசிரியர் தகுதித்தேர்வு நடத்த அனைத்து ஏற்பாடுகளும் தயார்...ஓரிரு நாட்களில் முறையான அறிவிப்பு வெளியாகிறது...| விண்ண...

Saturday, September 21, 2013

How to Solve an Equation in Excel

“ I have a curve. I did its fitting using Excel and got an equation.
y = 2E+07x^-2.146
For y=60 what will be the value of x?
How can we solve this equation using Excel? ”
Lets look at how this can be solved using Excel.

Define the Problem

Usman formula is y = 2E+07x^-2.146
or expanded y = 2*10^7*x^-2.146
We can use Excel’s Goal Seek function to assist us here
Goal Seek is located in the DataWhat-If AnalysisGoal Seek menu
GS00
Goal Seek is an inbuilt function in Excel that searches for a solution to a model/formula by iteratively trying source cell values until a solution is found.
Before we start, Excel doesn’t understand the concepts of x and y, but we can use cells for these instead
To use Goal Seek we need to put our formula into a cell.
Start a new file and in C3 (our y cell) type:  = 2*10^7*B3^-2.146
In B3 (our x cell): Put a value say 10
GS02
Note that C3 will show the solution of the formula for when x=10 or = 2*10^7*10^-2.146 = 142,899.277
GS01

Using Goal Seek

To use Goal Seek to find what value of x (B3) will result in y (C3) = 60,
Select C3
Goto the DataWhat-If AnalysisGoal Seek menu
GS03
Set Cell: C3  – This is our y value cell
To value: 60  This is the value we want to achieve
By changing cell: B3 – This is our x value cell
Click OK when ready
GS04
Excel shows us that it has found a solution and that (C3=60 when x (B3) = 374.60
Select OK to save the result
Select Cancel to return to the previous value

No comments: