software

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 quot;yquot; value of 25 how do I go about solving for a value of quot;xquot;.

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, quot;You can get
this straight off of the web by searching for quot;Numerical Recipes in Cquot;, then
look for Chapter 5. section 6 (Quadratic and Cubic Equations), pp 183 ff.
lt;quotegt; 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
lt;end quotegt;

let us know how you manage,
CJquot;MartinWquot; wrote:

gt; y = 1.083333333x4 - 7.3333333x3 16.416667x2 - 6.16667x 4
gt;
gt; Above is the polynomial equation of a trend line as shown on my chart,
gt; I have extracted the numbers into separate cells using LINEST, so now
gt; I have in cells A20 to E20 the numbers 1.083333333, -7.333333,
gt; 16.416667, -6.16667 and 4 respectively.
gt; With a quot;yquot; value of 25 how do I go about solving for a value of quot;xquot;.
gt;
gt; I've been stubbornly struggling with this on and off for a couple of weeks
gt; now and I'm not getting any closer to working it out and the websites
gt; I've found explaining the procedure all seem to go over my head
gt; by the time I get into the fourth or fifth paragraph. Any help would be
gt; greatly appreciated.
gt;
gt; Thank you
gt; Martin
gt;
gt;
gt;

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 (Toolsgt;Solver)
Target: y
Tick Value and enter 25
In Variable cells, enter : x
Click Solve

Et voilà!

HTH
--
AP

quot;MartinWquot; gt; a écrit dans le message de news:
...
gt;y = 1.083333333x4 - 7.3333333x3 16.416667x2 - 6.16667x 4
gt;
gt; Above is the polynomial equation of a trend line as shown on my chart,
gt; I have extracted the numbers into separate cells using LINEST, so now
gt; I have in cells A20 to E20 the numbers 1.083333333, -7.333333,
gt; 16.416667, -6.16667 and 4 respectively.
gt; With a quot;yquot; value of 25 how do I go about solving for a value of quot;xquot;.
gt;
gt; I've been stubbornly struggling with this on and off for a couple of weeks
gt; now and I'm not getting any closer to working it out and the websites
gt; I've found explaining the procedure all seem to go over my head
gt; by the time I get into the fourth or fifth paragraph. Any help would be
gt; greatly appreciated.
gt;
gt; Thank you
gt; Martin
gt;
Hi CJ

You wrote
lt;lt;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.gt;gt;

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

also
lt;lt;by searching for quot;Numerical Recipes in Cquot;, then look for Chapter 5.
section 6 (Quadratic and Cubic Equations), pp 183 ff.gt;gt;

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
-1lt;rootlt;-0.5 and 3lt;rootlt;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

quot;MartinWquot; wrote:

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

You're perfectly right.
Works like a charm!

Cheers
--
AP

quot;Jerry W. Lewisquot; gt; a écrit dans le message de
news: ...
gt; You can accomplish the same thing without the Solver Add-In by using
gt; Tools|Goal Seek. The precision of the numerical solution is controlled by
gt; Tools|Options|Calculation|Maximum Change.
gt;
gt; An nth degree polynomial has n roots, some of which may be complex. Since
gt; all coefficients are real, complex roots must come in pairs. Therefore
gt; there
gt; are either 0, 2, or 4 real roots of your polynomial. If you plot the
gt; polynomial over the range -1 to 4, you will see that there real roots with
gt; -1lt;rootlt;-0.5 and 3lt;rootlt;3.5. The change in slope between 1 and 2 implies
gt; that the other two roots must be complex. Which of the two real roots
gt; Solver/Goal Seek finds will be determined by the initial guess that you
gt; supply.
gt;
gt; Jerry
gt;
gt; quot;MartinWquot; wrote:
gt;
gt;gt; Thanks Ardus. That sounds like exactly what I need unfortunately solver
gt;gt; is
gt;gt; an add-in that requires the CD-ROM to install and I haven't got my disc
gt;gt; here at the moment. I'll try it tomorrow when I have the disc and report
gt;gt; back how it goes.
gt;gt;
gt;gt; Cheers
gt;gt; 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,4quot;)) 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 quot;set cellquot;
value would be any cell containing the Linest formula above but I
haven't got a clue what to put in quot;To Valuequot; and quot;By changing cellquot;

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
MartinUse 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

quot;MartinWquot; wrote:

gt; Hi Jerry,
gt;
gt; It was your post on LINEST in another thread a couple of weeks ago
gt; that started me chasing this.
gt; The equation in this thread comes from the data range
gt;
gt; A1 : 0 B1 : 4
gt; A2 : 1 B2 : 8
gt; A3 : 2 B3 : 16
gt; A4 : 3 B4 : 23
gt; A5 : 4 B5 : 50
gt;
gt; =LINEST(B1:B5,A1:A5^{1,2,3,4quot;)) when entered as an array
gt; across five cells returns 1.083333333, -7.333333,
gt; 16.416667, -6.16667 and 4 respectively.
gt; All these values coincide with the equation from the chart as I posted
gt; earlier.
gt;
gt; Where do I go from here with Goal Seek? I'm guessing that the quot;set cellquot;
gt; value would be any cell containing the Linest formula above but I
gt; haven't got a clue what to put in quot;To Valuequot; and quot;By changing cellquot;
gt;
gt; Also when you start talking about coefficients, real roots and complex
gt; roots and the like welllll... I'm afraid you're leaving me way behind there.
gt;
gt; Thanks
gt; Martin
gt;
gt;
gt;
gt;
gt;

MartinW wrote:
gt; The equation in this thread comes from the data range
gt; A1 : 0 B1 : 4
gt; A2 : 1 B2 : 8
gt; A3 : 2 B3 : 16
gt; A4 : 3 B4 : 23
gt; A5 : 4 B5 : 50
gt; =LINEST(B1:B5,A1:A5^{1,2,3,4quot;)) when entered as an array
gt; across five cells returns 1.083333333, -7.333333, 16.416667,
gt; -6.16667 and 4 respectively. All these values coincide with
gt; 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 quot;scatterquot; chart of the data, they appear to grow
exponentially. An exponential trendline fits the data with
R2 = 0.9906, which is quot;close enough for government workquot;.

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.