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,
Select C3
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
31 Responses
I’ve used goal seek to solve equations, but it is not very useful when there are several possible solutions and one needs them all. I also have used trendlines in charts to find the equation for the line of best fit. Not quite the same as solving equations, but easier than running linear regressions to find the same result.
Yes! I have solved a variety of equations using various techniques. The one I like to demonstrate, though, is the solution of relatively complexsimultaneous equations using
=MINVERSE() and =MMULT() functions
I also solve simultaneous equations in a work sheet using the substitution method as it is direct and easy to program!
I tried two alternative approaches:
first, good old algebra (my kids are doing a lot of expression rearranging at school just now)…
So x = (y / 2*10E+07) ^ (-1/2.146)
second, I’ve been playing with a little program called “Calca” (http://calca.io). It’s a little hard to describe (but only costs $9.99 to try) but think of a text document that understands math and recalculates on the fly. Something like that. So I start by typing:
y = 2*10^7*x^-2.146
which let’s me use the “evaluate” operator, “=>”. I type “x =>” (without the quotes) and calca adds what it knows:
x => 2524.3961/y^0.466
.. so it restates the expression in terms of y. Pretty cool. For a solution, I typed
60 = 2*10^7*x^-2.146
and then asked for x:
x=> 374.6009
It’s a fun little program. The Windows version is only semi-officially available and there’s no obvious link on the site, but I found it here: http://calca.io/store
This.
While I appreciate the craftiness of teaching to use Excel’s Goal Seek function to solve an equation, I find it somewhat dangerous. I’m aware that the question explicitly asked to use Excel for this, but I would liken the answer to teaching a hungry man to fish — with dynamite!
If you need to do algebra but have trouble dealing with formulas, you should try to polish up your algebra, ask someone who does, or use a software package like suggested above. When you are handling algebraic formulas by applying tricks and hacks, you are bound to make mistakes which will bite you later.
So, how do you arrive at a solution by using school algebra?
The goal is to have x standing on one side, and the rest on the other side.
Starting with
y = 2*10^7*x^-2.146,
you can move the factor “2*10^7” to the left by dividing by 2*10^2:
y/(2*10^7) = (2*10^7*x^-2.146)/(2*10^7)
y/(2*10^7) = x^-2.146
The next step is a little trickier. You need to get rid of the exponent “-2.146” on the right side. If the exponent were “2”, we know that we could simply extract the square root. Analogously, in our case, we would need to extract the “-2.146″th root. But there’s no function to extract an arbitrary root. We need to know that, in the example of square roots, “extracting the square root of a value” is the same as “raising the value to 1/2”. In short, sqrt(4)=4^(1/2). Correspondingly, “extracting the ‘-2.146’th root” is the same as raising to “1/-2.146”, which is the same as -1/2.146. That’s what we need to do to get rid of the exponent on the right side:
(y/(2*10^7))^(-1/2.146) = (x^-2.146)^(-1/2.146)
(y/(2*10^7))^(-1/2.146) = x
Swap left and right sides and we get:
x = (y/(2*10^7))^(-1/2.146)
I heard of this function but never tried it before, but this simple explanation give me more thought about it, i will dig into it further & learn more your other related posts. thanks so much. chandoo…
I learned how to solve simultaneous equations in Excel from Alan Beban’s posts many years ago using MINVERSE and MMULT (same thing Duncan is describing). I have actually used it a few time in my work.
Great stuff!! I really appreciate the step by step with images!! Super helpful!!
used the goal seek function to solve for equation
a= CF*1/(1+x)^1+CF*1/(1+x)^2+…….+CF*1/(1+x)^n for a changing ‘a’.
Quite clean and helpful.
Thanks !
I need to create a function with regular addition and subtraction of digits to/from a variable, say “x”. as I type x in the cell i get result as “#value!” instead of “x-2” . What should i do?
This is urgent. If you can help!
This is probably a formatting error where Excel thinks a number is a text character. If you post an example of your work it will help us to help you
Sorry. I do not know how to post my excel sheet here for example. I have made .jpg but how to post?
Send it to me at duncanwil at gmail dot com and I will report back here
@Rahul
For urgent questions start a post at the Chandoo.org Forums
http://forum.chandoo.org/
Attaching a sample file will get you a quicker response
I have sent excel jpg at duncanwil@gmail.com. Please check and reply!
I have replied to Rahul off list but have not had a response as yet.
I accept that Rahul could have uploaded his work here but since he didn’t, all’s still well I think.
I have to wait for Rahul to reply otherwise anything I say might be pointless … and not the answer to his question!
Duncan
I have responded to you and Rahul directly via email
with no response?
Rahul and I are still chatting. I think the problem is solved but I am waiting for Rahul’s final say so.
Thanks a lot.
My problem is solved through another method.
Kudos!
Hi,
I have a table in excel. I have a total sum with 6 countries and % each of these countries represent of the whole. Also within same table I have 5 segments with percent of of segment as a whole and then percent by each country with breakdown % allocated by segment. I know the total value and I am trying to populate table so values tie out on coulmns to equal country sum and then on rows to sum to segment sum. Not sure if this makes sense but been struggling for hours with this!!! Help please
@Oihonde
Can you please ask the question in the chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file to give a more targetted answer
Some genuinely fantastic posts on this web site, thanks for contribution.
how i find x and y value in the following equation by use of excel
x+y =136.96
28x/100+18y/100 =16
then what is the value of x and y
how it find
how i find x and y value in the following equation by use of excel
x+y =120.56
28x/100+18y/100 =32.4
then what is the value of x and y
how it find
@Jithin
This is a simultaneous equation
I would solve this manually like:
(1) x+y =120.56
(2) 28x/100+18y/100 =32.4
(3) = (1) x 18/100
= 18x/100 +18y/100 = 18*120.56/100
= 18x/100 +18y/100 = 21.7008
(4) = (2) – (3)
28x/100+18y/100 =32.4
– 18x/100 +18y/100 = 21.7008
= 10x/100 + 0 = 32.4 – 21.7008
= x/10 = 10.6992
(5) Multiply both sides by 10
x = 106.992
(6) Substitute x into (1)
x+y =120.56
106.992 + y = 120.56
y = 120.56 – 106.992
y = 13.568
Enjoy
Thanks so much for your response to my inquiry, but I will appreciate a further detailed information in a vivid description.
i just want to put a formula like below:
Ex. 1
time- 2:30
formula want to make : =2+30/60
Ex 2
Time – 0.30
formula : =0+30/60
Help me if possible
I need to solve 2a+7a=9a in excel. So, How can I do it??
hmm, I am confused. What is there is to solve. 2a+7a is 9a
Emp. MONTH Date BEAT NAME DISTR-NAME
xyz jan 1-1-2022 LEAVE LEAVE
entire year record is here in excel .
suggest formula for each Employee every month how many leaves are there???