Solve Equation to Get X , Goal Seek or Scenario Manager or LINEST() Function?

[mrmmickle1]

It's been a while since I have had to use Goal Seek or Scenario Manager... I have an equation in which I need to solve for X. I know the value of Y. Can someone please show me how I can get the Value of X?

The equation is:

Y=B0 + B1*X + B2*X*X + B3*X*X*X

<tbody>
</tbody>

So if I had for Example:

Y = 1.86
B0 = 1.134
B1 = .294
B2 = 0.005607
B3 = -0.000001183

Then I would want to solve for X in this equation: 1.86 = 1.134 + .294*X + .005607*X*X + -0.000001183*X*X*X

Here is the spreadsheet example. The Below Portion that says Plug in Y = X are examples in a software program I have that does this calculation.
I would like to be able to "reverse engineer" this process.... so I can use the formula myself in Excel. Can someone please help me to arrive at my solution? It may be possible with the LINEST() FUNCTION... Not sure how to use it though.

I will always have the Values: Y, B0,B1,B2 & B3

Excel 2012
	A	B	C	D	E
1	Y=B0 + B1*X +B2*X*X + B3*X*X*X			y	x
2	B0	1.134		1.86
3	B1	0.294			1.134
4	B2	0.005607
5	B3	-0.000001183
6
7	Plug In Y = X
8	Y	X
9	1.86	2.363
10	2.79	5.131
11	3.14	6.11
FORMULA_EXAMPLE

Cell Formulas
Range	Formula
E3	=B2+(B3*E2)+(B4*(E2)^2)+(B5*(E2)^3)

Any help with my issue would be much appreciated.

 

[ExcelGuyDude]

You'll want to use a Goal Seek function (Data > Data Tools > What-If Analysis > Goal Seek...)

In this example, you have the "Set cell:" as E3, "To value:" as 1.86, and "By changing cell:" as E2. In this example, E2 will be the value that X should be, and Y is E3

 

[mrmmickle1]

Thanks. Works perfectly.

 

[mrmmickle1]

Is there a second way to arrive at the above value using a mathematical formula and not goal seek?

While goal seek works, it seems that I may need to enter a mathematical formula into another software (SalesForce) in order to do these calculations on a mass scale.

The issue is I would have data in a DB that looks like this with hundreds of rows:

Excel 2013
	A	B	C	D	E	F
1	B0	B1	B2	B3	Y	X
2	1.134	0.294	0.005607	-0.000001183	1.86	MATHEMATICAL FORMULA HERE to = 2.363
Example_2

This has to do with 3rd Order Polynomial Regression meant to correct a distorted Curve and plot it accordingly...

In this example we are trying to correct for DNA methylation in order to get the "true" value which is X

 

[pgc01]

Hi

As you may remember a third degree equation has 3 roots. With real coefficients you may have either 3 real roots or 1 real root and 2 complex conjugates.

In this last case it has 3 real solutions.

I posted a solution for the 3rd degree equation with real coefficients. I posted this some time ago and, unfortunately, I'm not sure this was the last version.

This udf returns the 3 solutions of the equation in 6 cells, real and imaginary values of each solution in each row.

This is an array formula that returns an array.

In this case I selected F2:G4, entered in the formula bar:

=ROUND(CubicEq(D2,C2,B2,A2-E2),10)

and confirmed with CTRL-SHIFT-ENTER.

I used the Round() because of the vba float rounding errors.

You can see the 3 solutions of your equation, in this case 3 real numbers: -54.203, 2.363, 4791.485

In I2, just to confirm the results I used:

=$A$2+$B$2*F2+$C$2*F2^2+$D$2*F2^3

Copy down.

' PGC 2008 - UDF to solve a cubic equation with real coefficients
' Equation - a*x^3+b*x^2+c*x+d=0
' Returns a 3 rows * 2 columns array with the three solutions
' To use in the worksheets:
' - select a 3 rows * 2 columns range (ex. D1:E3)
' - in the formula bar enter =CubicEq(a,b,c,d) and confirm with CTRL-SHIFT-ENTER
Function CubicEq(a As Double, b As Double, c As Double, d As Double)
Dim p As Double, q As Double, disc As Double
Dim u(1 To 2) As Double, uc(1 To 2) As Double, i As Integer, arrResult(1 To 3, 1 To 2) As Double
 
p = -b ^ 2 / 3 / a ^ 2 + c / a
q = 2 * b ^ 3 / 27 / a ^ 3 - b * c / 3 / a ^ 2 + d / a
disc = q ^ 2 / 4 + p ^ 3 / 27
If disc >= 0 Then
    u(1) = -q / 2 + disc ^ (1 / 2)
    If u(1) = 0 Then u(1) = -q
Else
    u(1) = (q ^ 2 / 4 - disc) ^ (1 / 2)
    If q = 0 Then u(2) = [pi()] / 2 Else u(2) = Atn(((-disc) ^ (1 / 2) / (-q / 2))) + IIf(q > 0, [pi()], 0)
End If
 
If u(1) <> 0 Then
    For i = 1 To 3
        uc(1) = Abs(u(1)) ^ (1 / 3) * IIf(u(1) > 0, 1, -1) * Cos((u(2) + i * 2 * [pi()]) / 3)
        uc(2) = Abs(u(1)) ^ (1 / 3) * IIf(u(1) > 0, 1, -1) * Sin((u(2) + i * 2 * [pi()]) / 3)
 
        arrResult(i, 1) = uc(1) * (1 - p / 3 / (u(1) ^ 2) ^ (1 / 3)) - b / 3 / a
        arrResult(i, 2) = uc(2) * (1 + p / 3 / (u(1) ^ 2) ^ (1 / 3))
    Next i
End If
CubicEq = arrResult
End Function

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >F</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >G</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >H</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >I</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >J</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">B0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">B1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">B2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">B3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Y</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Confirm</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1.134</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">0.294</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">0.005607</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">-1.2E-06</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1.86</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">-54.20337757</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1.86</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2.362954494</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1.86</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">4791.485393</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1.86</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=11 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table><br>
<br>
<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >Addr</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >Formula</td></tr><tr><td colspan=2 style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >[Book1]Sheet1</td></tr><tr><td rowspan=2 style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center; " >F2:G4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em; " > =ROUND(CubicEq(D2,C2,B2,A2-E2),10) </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;; " > This in an array formula that is returning an array. Select the WHOLE range, paste the formula into the formula bar <br> and confirm with CTRL+SHIFT+ENTER and not just ENTER</td></tr></table>

 

[mrmmickle1]

pgc01,


First Off. Thanks so much for the response. All though I don't fully comprehend it...ahahah


My colleagues were very impressed by your solution. I think they are using a program called Graph Pad to come up with the same calculation. I believe there issue is doing it in Mass...

They did mention that Goal Seek is sometimes off by a small amount which sounds like it may be due to the rounding error that you mention. I understand that this gives me three answers.

How is Goal Seek able to distinguish the correct answer (real root)? Is there anyway to achieve a single result or is this a pipe dream?

I am sharing your communication with my colleagues seeing as this is a little beyond me. Thank you tremendously for your help!

 

[ExcelGuyDude]

Goal seek is a very rudimentary method of guessing until it's close enough. If you have a slow enough computer, you can watch what the Goal Seek does... It's changes the numbers up and down by predetermined increments until it comes up with the exact answer, or an answer close enough. Like pgc01 stated previously, the equation has 3 real roots, and to create an inverse formula is quite complex.

This post explains the same concept.

algorithm - Polynomial inverse - Stack Overflow

 

[pgc01]

How is Goal Seek able to distinguish the correct answer (real root)? Is there anyway to achieve a single result or is this a pipe dream?

Hi

Sorry, I don't understand what this means.

All the 3 roots are real roots.

If you ask excel the solutions of an equation and the equation that has 3 roots how can excel decide on which one you want?
You'd have to add more information.

Example: you throw a ball vertically with a speed of 8 m/s from the a height of 4m up vertically. How much time until the ball gets to the ground?
You remember from school that with the usual simplifications, for ex. g=10 instead of 9.8..., the equation to solve is 5t^2-8t-4=0.

The solutions of this 2nd degree equation are -0.4 and 2. These are both real correct solutions of the equation.

Now you, as a human, know that only the solution 2 interests you. That's not because the -0.4 is not a good solution for the equation, it's because it's not a good solution for your problem. (you could also give a logical sense to the -0.4 solution but that's another discussion)

With another example both solutions could be valid.

Conclusion: you ask for the solutions of an equation and you get them. Then you have another step that is to analyse the solutions and decide one which solutions interest you. If you want an automatic solution add conditions that choose between the roots of the equation.

 

[mrmmickle1]

pgc1,

Thanks for your response. With you help my coworkers and I have come to a similar conclusion. I will do your equation on 100 examples. 1 to 100. Which is the range of results we expect to receive. Then I will look at all of the results and figure out a way to determine which one is the correct solution based on some type of criteria.

What is the significance of the Zeros in the Worksheet formula result?

If I take the three portions of the array that distinguish the three possible solutions:

arrResult(1, 1)
arrResult(2, 1)
arrResult(3, 1)

at the bottom of the equation and run these array values through another set of tests....

how can I change the equation to only result in 1 answer instead of the entire array?

For example if I distingush that

arrResult(2,1) is the correct answer is it possible for me to put this in the one cell.... F2 rather than 6 cells for results as the equation is set up now?

Would I just do this:

 CubicEq = arrResult(2, 1)

and enter it as a regular formula?

Once I have the results from my test of 1-100. I will report back.

 

[pgc01]

Hi

You can use Index() to get the 6 elements of the array

The array returned has 3 rows and 2 columns, corresponding to the 3 solutions, real and imaginary parts.

For ex., using the example that I posted

- to get the real part of the first solution

=INDEX(ROUND(CubicEq(D2,C2,B2,A2-E2),10),1,1)

- to get the imaginary part of the third solution

=INDEX(ROUND(CubicEq(D2,C2,B2,A2-E2),10),3,2)

Hope this helps