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.

 

[pgc01]

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

A third degree equation with real coefficients may have
- either 3 real roots
- or 1 real root and 2 complex conjugates

The array resulting from CubicEq() will give you always the 3 roots, even if they are complex. Each row of the array has 1 of the solutions. The first column is the real part of the number and the second one is the imaginary part.

The example you posted has 3 real roots.

This example has 1 real root and 2 roots complex conjugates

x^3-3*x^2+4*x-2=0

The roots are in this case
1 real root = 1
2 complex = (1 + i) and (1 - i)

You can get them with the array formula:

=ROUND(CubicEq(1,-3,4,-2),10)

Select a 3 row x 2 column range, paste the formula in the formula bar and confirm with CTRL-SHIFT-ENTER
You'll see the 3 roots, 1 in each row, the real parts in the first column and the imaginary parts in the second column.

 

[pgc01]

P. S.

This also means that in the cases when the equation has 1 real root and 2 complex roots, like in the second example that I posted, you know immediately which is the solution of your problem.
Only in the case of 3 real roots must you use other criteria to validate the solution.

(this is assuming that only real roots are valid for your problem)

 

[mrmmickle1]

pgc01,

Thanks so much! I appreciate the help. I think this is what I was after

 

[pgc01]

I'm glad it helped.