calculate unknown X values from known y values from trend line the polynomial option

[ffulton5]

I have a set of data of known X and y values and want to calculate unknown X values from known y values. When I put my data into excel and draw a graph with a trend line the polynomial option seems to give the best fit (best r squared value) and looks to follow the data best by eye. The trend line is of the form, Y=ax2+bx+c, eg y = 0.0015x2 - 0.514x + 47.137 RČ = 0.9958
I need to be able to estimate the unknown values of x from observed values of y, but I do not know how to use this formula to do the calculation.
What is the easiest way to do this?
I can tell roughly were the answer of the calculation should be by looking at the graph by eye but I would need a way to get a formula that I can use to solve for X in excel to do this for me.
Example data
X |Y
Substance Concentration | Assay reading
0.0 | 50.01
9.5 | 42.48
19.0 | 38.28
39.0 | 22.73
78.0 | 7.18

known Values of Y from test samples
40.8
37.8
34.8
34.8
35.8
7.7
32.4

[BB1972]

ffulton5, welcome to the forum.

This sounds like homework/an assignment. Is it?

[ffulton5]

Hi,
It is actually part of an experiment I produced the results with a standard linear curve, but my boss has asked me to use the polynomial curve as it gives a better fit to the data. Problem being I can't figure out how to get x I need to get a formula that will do this as I need to work out nearly 400 values.
I found the following formula x = [-b ±√(b^2 - 4ac)]/2a which I translated into excel formula of
=(-b-SQRT(b^2-4*a*c))/2*a but it does not work. Also it does not appear to use the y value.
Someone also suggested using Rad instead of SQRT but it does not appear to be a valid function.
Regards,
Frances

[MrShorty]

I would venture to suggest that this is more of a math question than an Excel question. Once the math is explained, you should not have trouble with Excel.

Recall from algebra that this " Y=ax2+bx+c"=0 is a quadratic equation. http://en.wikipedia.org/wiki/Quadratic_equation

This " x = [-b ±√(b^2 - 4ac)]/2a" is the roots or zeros or solutions of the quadratic equation -- The values of x for which y is 0.

If you are given a non-zero y (ax2+b2+c=y), then you need to rearrange the equation into something of the form ax2+bx+c=0 by subtracting y from each side:
ax2+bx+c-y=0.

Now plug into the quadratic formula where a=a, b=b, and c=c-y.

[ffulton5]

Thanks the c=c-y explains a lot, it may be my maths I just cannot see where I am going wrong.
When I substitute c-y for c the formula I get is =(-(b)-SQRT( (b^2)-(4*a*(c-y)))/2*a)
Using the following values a=0.0015, b=-0.514, c=47.137 and y=43.48 for which I know the x value should be x=9.5 that gives me
=(-(-0.514)-SQRT( (-0.514^2)-(4*0.0015*(47.137-43.4838709677419)))/2*0.0015)
This gives the result of 0.514369162483896 which is now where near what it should be. Have I got the brackets in the wrong place or is it more fundamental than that?

[MrShorty]

Have I got the brackets in the wrong place

Short answer, yes. If you follow the calculation (maybe using the formula evaluation tool http://office.microsoft.com/en-us/ex...la_one_step_at), you will see that it is not dividing the initial 0.514 by 2a. You will also see that it is not dividing by 2a at all, but dividing by 2 then multiplying by a.

I don't have any magic solutions for building formulas like this that require a lot of brackets. I often find it easier to build them if I use multiple cells for the calculation. For example, in this case, I would probably have separate cell for A, B, and C (=c-y). Then I would have a cell that calculates the "discriminant" (b^2-4ac). This can be a useful calculation, because the sign on the discriminant will tell you if the roots of the equation are real or complex. Having the discriminant calculate separately, you can then test the discriminant's sign and either block the calculation (if you are only interested in real roots) or branch off into complex roots when needed.

With individual cells for A, B, C and the discriminant, then the final formulas are much easier to write. =(-B-sqrt(disc))/2/A or -B/2/A-sqrt(disc)/2/A or -B/(2*A)-sqrt(disc)/(2*A) or whatever configuration you want. I just find it easier to write formulas like this in smaller bites rather than put the whole thing into a single cell.

[ffulton5]

Just to let you know that I found the following Alternative quadratic formula x = 2c / -b +-srqt(b^2 - 4ac), which gives much better results or is just easier to get in formula, on the link that you give me.
I cannot thank you enough for your help I could not find any other site with this invaluable information. You might just have saved my job. Thanks a million again for taking the time to look at this and for all your helpful tips.