How to Solve an Equation in Excel

Posted on September 19th, 2013 in Huis , Posts by Hui - 21 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

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 Data, What-If Analysis, Goal 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 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:

 

 

Written by Hui...
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

21 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

    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

    • 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'.
    Quite clean and helpful.
    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?

  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!

  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

Leave a Reply