y = 1.083333333x4 - 7.3333333x3 + 16.416667x2 - 6.16667x + 4

Above is the polynomial equation of a trend line as shown on my chart,
I have extracted the numbers into separate cells using LINEST, so now
I have in cells A20 to E20 the numbers 1.083333333, -7.333333,
16.416667, -6.16667 and 4 respectively.
With a "y" value of 25 how do I go about solving for a value of "x".

I've been stubbornly struggling with this on and off for a couple of weeks
now and I'm not getting any closer to working it out and the websites
I've found explaining the procedure all seem to go over my head
by the time I get into the fourth or fifth paragraph. Any help would be
greatly appreciated.

Thank you
Martin

Martin, consider the following:
1: Is this y=1.083333333x^4-7.3333333x^3+16.416667x^2-6.16667x+4 the correct
equation? This would be a polynomial equation with the largest exponent of
four. If not, then the equation has a degree of one and is a linear equation.
2: Also, for information sake, there is a previous entry on this chat site
concerning solving quadratic equations by Dave Braden. He referred to a
particular textbook that is accessable on the web; quoting him, "You can get
this straight off of the web by searching for "Numerical Recipes in C", then
look for Chapter 5. section 6 (Quadratic and Cubic Equations), pp 183 ff.
<quote If either a or c (or both) are small, then one of the roots will
involve the subtraction of b from a very nearly equal quantity (the
discriminant); you will get that root very inaccurately. The correct way
to compute the roots is
q = -(b + sgn(b)*sqrt(b^2-4a*c))*0.5

Then the two roots are
x1 = q/a and x2 = c/q
<end quote

let us know how you manage,
CJ


"MartinW" wrote:

y = 1.083333333x4 - 7.3333333x3 + 16.416667x2 - 6.16667x + 4

Above is the polynomial equation of a trend line as shown on my chart,
I have extracted the numbers into separate cells using LINEST, so now
I have in cells A20 to E20 the numbers 1.083333333, -7.333333,
16.416667, -6.16667 and 4 respectively.
With a "y" value of 25 how do I go about solving for a value of "x".

I've been stubbornly struggling with this on and off for a couple of weeks
now and I'm not getting any closer to working it out and the websites
I've found explaining the procedure all seem to go over my head
by the time I get into the fourth or fifth paragraph. Any help would be
greatly appreciated.

Thank you
Martin

You can use Solver

Name a cell x
Name a cell y, enter formula:
=A20*x^4+B20*x^3+C20*x^2+D20*x+E20

Lauch Solver (ToolsSolver)
Target: y
Tick Value and enter 25
In Variable cells, enter : x
Click Solve

Et voilà!

HTH
--
AP

"MartinW" a écrit dans le message de news:
...
y = 1.083333333x4 - 7.3333333x3 + 16.416667x2 - 6.16667x + 4

Above is the polynomial equation of a trend line as shown on my chart,
I have extracted the numbers into separate cells using LINEST, so now
I have in cells A20 to E20 the numbers 1.083333333, -7.333333,
16.416667, -6.16667 and 4 respectively.
With a "y" value of 25 how do I go about solving for a value of "x".

I've been stubbornly struggling with this on and off for a couple of weeks
now and I'm not getting any closer to working it out and the websites
I've found explaining the procedure all seem to go over my head
by the time I get into the fourth or fifth paragraph. Any help would be
greatly appreciated.

Thank you
Martin

Hi CJ

You wrote
<<1: Is this y=1.083333333x^4-7.3333333x^3+16.416667x^2-6.16667x+4 the
correct equation? This would be a polynomial equation with the largest
exponent of four.

Yes that's correct, a fouth order polynomial. I want to calculate the value
of "x" for a given value of "y" (in this example I am using y = 25)

also
<<by searching for "Numerical Recipes in C", then look for Chapter 5.
section 6 (Quadratic and Cubic Equations), pp 183 ff.

Yes I found this reference but once again I was way out of my depth
in a very short time. I'm starting to think that this is beyond my
mathematical capabilities which I admit are not very high. I only went
to grade 10 at school and that was 25 years ago.

Thanks for taking the time to post.
Martin

Thanks Ardus. That sounds like exactly what I need unfortunately solver is
an add-in that requires the CD-ROM to install and I haven't got my disc
here at the moment. I'll try it tomorrow when I have the disc and report
back how it goes.

Cheers
Martin

You can accomplish the same thing without the Solver Add-In by using
Tools|Goal Seek. The precision of the numerical solution is controlled by
Tools|Options|Calculation|Maximum Change.

An nth degree polynomial has n roots, some of which may be complex. Since
all coefficients are real, complex roots must come in pairs. Therefore there
are either 0, 2, or 4 real roots of your polynomial. If you plot the
polynomial over the range -1 to 4, you will see that there real roots with
-1<root<-0.5 and 3<root<3.5. The change in slope between 1 and 2 implies
that the other two roots must be complex. Which of the two real roots
Solver/Goal Seek finds will be determined by the initial guess that you
supply.

Jerry

"MartinW" wrote:

Thanks Ardus. That sounds like exactly what I need unfortunately solver is
an add-in that requires the CD-ROM to install and I haven't got my disc
here at the moment. I'll try it tomorrow when I have the disc and report
back how it goes.

Cheers
Martin

You're perfectly right.
Works like a charm!

Cheers
--
AP

"Jerry W. Lewis" a écrit dans le message de
news: ...
You can accomplish the same thing without the Solver Add-In by using
Tools|Goal Seek. The precision of the numerical solution is controlled by
Tools|Options|Calculation|Maximum Change.

An nth degree polynomial has n roots, some of which may be complex. Since
all coefficients are real, complex roots must come in pairs. Therefore
there
are either 0, 2, or 4 real roots of your polynomial. If you plot the
polynomial over the range -1 to 4, you will see that there real roots with
-1<root<-0.5 and 3<root<3.5. The change in slope between 1 and 2 implies
that the other two roots must be complex. Which of the two real roots
Solver/Goal Seek finds will be determined by the initial guess that you
supply.

Jerry

"MartinW" wrote:

Thanks Ardus. That sounds like exactly what I need unfortunately solver
is
an add-in that requires the CD-ROM to install and I haven't got my disc
here at the moment. I'll try it tomorrow when I have the disc and report
back how it goes.

Cheers
Martin

Hi Jerry,

It was your post on LINEST in another thread a couple of weeks ago
that started me chasing this.
The equation in this thread comes from the data range

A1 : 0 B1 : 4
A2 : 1 B2 : 8
A3 : 2 B3 : 16
A4 : 3 B4 : 23
A5 : 4 B5 : 50

=LINEST(B1:B5,A1:A5^{1,2,3,4")) when entered as an array
across five cells returns 1.083333333, -7.333333,
16.416667, -6.16667 and 4 respectively.
All these values coincide with the equation from the chart as I posted
earlier.

Where do I go from here with Goal Seek? I'm guessing that the "set cell"
value would be any cell containing the Linest formula above but I
haven't got a clue what to put in "To Value" and "By changing cell"

Also when you start talking about coefficients, real roots and complex
roots and the like welllll... I'm afraid you're leaving me way behind there.

Thanks
Martin

Use Goal Seek the same way that you would Solver. Put the polynomial formula
in one cell, written so that it gets the x value from a different cell. Then
use Goal Seek to set the formula cell equal to 25 by changing the x cell.

I am generally skeptical of a 4th degree polynomial fit to only 5 data
points, unless there is good reason to believe a priori that a 4th degree
polynomial really is the correct model.

Jerry

"MartinW" wrote:

Hi Jerry,

It was your post on LINEST in another thread a couple of weeks ago
that started me chasing this.
The equation in this thread comes from the data range

A1 : 0 B1 : 4
A2 : 1 B2 : 8
A3 : 2 B3 : 16
A4 : 3 B4 : 23
A5 : 4 B5 : 50

=LINEST(B1:B5,A1:A5^{1,2,3,4")) when entered as an array
across five cells returns 1.083333333, -7.333333,
16.416667, -6.16667 and 4 respectively.
All these values coincide with the equation from the chart as I posted
earlier.

Where do I go from here with Goal Seek? I'm guessing that the "set cell"
value would be any cell containing the Linest formula above but I
haven't got a clue what to put in "To Value" and "By changing cell"

Also when you start talking about coefficients, real roots and complex
roots and the like welllll... I'm afraid you're leaving me way behind there.

Thanks
Martin

MartinW wrote:
The equation in this thread comes from the data range
A1 : 0 B1 : 4
A2 : 1 B2 : 8
A3 : 2 B3 : 16
A4 : 3 B4 : 23
A5 : 4 B5 : 50
=LINEST(B1:B5,A1:A5^{1,2,3,4")) when entered as an array
across five cells returns 1.083333333, -7.333333, 16.416667,
-6.16667 and 4 respectively. All these values coincide with
the equation from the chart as I posted earlier.

If you have N data points, I believe there is always(?) a polynomial
of degree N-1 that fits the data exactly. That does not make it right.

Looking at a "scatter" chart of the data, they appear to grow
exponentially. An exponential trendline fits the data with
R2 = 0.9906, which is "close enough for government work".

So y = 4.20244*exp(0.61075*x) seems to fit your data. In that
case, x = ( ln(y) - ln(4.20244) ) / 0.61075 -- a much more tractable
equation to work with. Of course, neither formula may be right for
data outside the sample range.

Hi Martin,
You may be interested to know that help to use the solver can be obtained
from the following website:
http://www.vertex42.com/ExcelArticle...-examples.html
cheers,
CJ


" wrote:

MartinW wrote:
The equation in this thread comes from the data range
A1 : 0 B1 : 4
A2 : 1 B2 : 8
A3 : 2 B3 : 16
A4 : 3 B4 : 23
A5 : 4 B5 : 50
=LINEST(B1:B5,A1:A5^{1,2,3,4")) when entered as an array
across five cells returns 1.083333333, -7.333333, 16.416667,
-6.16667 and 4 respectively. All these values coincide with
the equation from the chart as I posted earlier.

If you have N data points, I believe there is always(?) a polynomial
of degree N-1 that fits the data exactly. That does not make it right.

Looking at a "scatter" chart of the data, they appear to grow
exponentially. An exponential trendline fits the data with
R2 = 0.9906, which is "close enough for government work".

So y = 4.20244*exp(0.61075*x) seems to fit your data. In that
case, x = ( ln(y) - ln(4.20244) ) / 0.61075 -- a much more tractable
equation to work with. Of course, neither formula may be right for
data outside the sample range.

Jerry, CJ, Ardus and Joe,

A very big thank you to all of you. You have all been a tremendous
help to me.

Thank you again,
Martin