This week at the Chandoo,org Forums, Usman asked,
“ 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 Data, What-If Analysis, Goal Seek menu
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
Note that C3 will show the solution of the formula for when x=10 or = 2*10^7*10^-2.146 = 142,899.277
Using Goal Seek
To use Goal Seek to find what value of x (B3) will result in y (C3) = 60,
Goto the Data, What-If Analysis, Goal Seek menu
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
Excel shows us that it has found a solution and that y (C3) =60 when x (B3) = 374.60
Select OK to save the result
Select Cancel to return to the previous value
You can download a sample of the above here: Download Sample File
How have you solved Formulas using Excel or other techniques
How have you solved Formulas using Excel or other techniques?
Let us know in the comments below:
Learn more about Goal seek and solver:
- Introduction to Goal seek and building a retirement calendar
- Introduction to Solver
- Using solver to assign items to buckets
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« 7 reasons why you should get cozy with INDEX()||What are best Excel interview questions? [survey] »|