How to Solve an Equation in Excel

Posted on September 19th, 2013 in Huis , Posts by Hui - 27 comments

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

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

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:

 7 reasons why you should get cozy with INDEX() What are best Excel interview questions? [survey]
 Written by Hui... Tags: advanced excel, downloads, goal seek, Learn Excel, Microsoft Excel Formulas Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

27 Responses to “How to Solve an Equation in Excel”

1. Vitalie says:

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.

2. Duncan Williamson says:

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!

3. 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

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

• Andreas says:

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)

4. Kiev says:

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...

5. Jason M says:

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.

6. Ryan C says:

Great stuff!! I really appreciate the step by step with images!! Super helpful!!

7. Monika says:

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'.
Thanks !

8. rahul sudan says:

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?

• rahul sudan says:

This is urgent. If you can help!

• Duncan Williamson says:

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

• rahul sudan says:

Sorry. I do not know how to post my excel sheet here for example. I have made .jpg but how to post?

• Duncan Williamson says:

Send it to me at duncanwil at gmail dot com and I will report back here

• Hui... says:

@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

9. Duncan Williamson says:

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!

• Hui... says:

Duncan

I have responded to you and Rahul directly via email
with no response?

• Duncan Williamson says:

Rahul and I are still chatting. I think the problem is solved but I am waiting for Rahul's final say so.

• rahul sudan says:

Thanks a lot.

My problem is solved through another method.

Kudos!

10. oihonde says:

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

11. Brainapsyl Review says:

Some genuinely fantastic posts on this web site, thanks for contribution.

12. jithin says:

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

13. jithin says:

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

• Hui... says:

@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

14. g*G-Anfy Jensen says:

Thanks so much for your response to my inquiry, but I will appreciate a further detailed information in a vivid description.

 7 reasons why you should get cozy with INDEX() What are best Excel interview questions? [survey]