# Need some excel help...

Discussion in 'General' started by Dr_kazza, 16 Oct 2002.

1. ### Dr_kazzaWhat's a Dremel?

Joined:
2 Nov 2001
Posts:
395
0
Need some Excel help...

If you have a nice pretty chart and you have fit a 3rd order Polynomial best fit....

How do you get the data of that polynomial into a cell...
for example if the poly = 5X^3 +2X^2 - 34X + 7

Then either I'm after a cell that would say
y = 5X^3 +2X^2 - 34X + 7

or better than that

Cubic multiple = 5
Square multiple = 2
linear multiple = -34

HEEEEELP

Joined:
6 Jul 2002
Posts:
324
0

3. ### ƒlamikaWhat's a Dremel?

Joined:
12 Sep 2002
Posts:
192
0
I remeber the hell i had trying to plot graphs like that and have them credited by my uni tutor... excel i found pretty basic and have been informed by people its mailny used for pretty graphs to show on powerpoint to managers...

anyways.. if you go to www.download.com and search for a program called 'rjs graph' you should get a free microsoft (i think its microsoft) graph program... its not too shabby for plotting graphs and stuff

hopefully its of some help. im not sure if you want a graph or to solve the polynomial equations. id used the 'minus b' formula, much to the desgust of my math tutor

-ƒlamika

4. ### ƒlamikaWhat's a Dremel?

Joined:
12 Sep 2002
Posts:
192
0
y = 5X^3 +2X^2 - 34X + 7

isnt it possible to put this formula in a cell ?

y = 5(x^3) + 2(x^2) - 34x + 7

or perhaps

y= ((5(x*x*x))+(2(x*x))-(34*x)+7)

?? not 100% , if all else fails you could put the cube and square answers in seperate cells and then reference them in the equation...

good luck, glad we finished maths in the first year

-ƒlamika

5. ### FatboyBored

Joined:
8 Oct 2001
Posts:
1,268
0
i have been doing this recently in maths.
do what flamika said, thats what we do, seperate the different bits then add them or - them in the last cell which would be Y.
if im right these are the graphs that go in a sort of s shape.

6. ### Dr_kazzaWhat's a Dremel?

Joined:
2 Nov 2001
Posts:
395
0
sorry I didn't explain properly...

The numbers I input come from some other program. I use excel to find the best fit line (which could be something like the equation I gave) the problem I get is that once excel has found the best fit polynomial it will display it in the graph, but I can't dynamically link the formula....

Lets say that If I input the numbers and I need to know the X^2 term... in the case I gave = 2
Now the numbers change and the new X^2 term is -53 for example...

how do I link that term into another cell?

7. ### ƒlamikaWhat's a Dremel?

Joined:
12 Sep 2002
Posts:
192
0
you mean you want to draw one graph... then input new x values and have the graph re-drawn ?

i dont think excel can do that without going through those fantastic wizards again.

rjs graph isnt a bad tool, its free should check it out. it allows graphs values to change and be re-drawn. and the graphs are more accurate than those in excel.

and yeah with a cube of x in the equation you should see an 's' shaped graph (most the time) ...and if you really want you can do the second differential of the differential of the equation to find out max and min turning points... oh rapture.

-ƒlamika

8. ### Dr_kazzaWhat's a Dremel?

Joined:
2 Nov 2001
Posts:
395
0
No not quite... the point is I don't know what the formula is...

Excel can guess a best fit formula by plotting a trendline on a graph (I don't need the graph itself)

What I need to know is the formula itself... for example

Numbers -> Graph -> excel fits 5th order polynomial best fit -> equation for best fit line gets spat out into a new cell.

The reason I'm after is is lets take an example:
x = 0.25 y = -83.5139
x = 0.5 y = -91.6203
x = 0.9 y = -22.2562
x = 1.0 y = 4.41456
x = 1.5 y = 160.778
x = 2.0 y = 327.673
x = 3.0 y = 625.171
x = 5.0 y = 1022.93

The best fit for that is
y = -3.9646x^5 + 53.552x^4 - 276.96x^3 + 667.01x^2 - 419.21x - 16.578

I need to know the slope and curvature of this graph...
Slope = dy/dx = 5*-3.9646x^4 + 4*53.552x^3 - 3*276.96x^2 + 2*667.01x - 419.21

Curvature = d2y/dx2 = 5*4*-3.9646x^3 + 4*3*53.552x^2 - 3*2*276.96x + 2*667.01

I would like a cell that has these formulae in them but i need to dynamically link in the numbers in bold since they change if I change the initial y values.

9. ### ƒlamikaWhat's a Dremel?

Joined:
12 Sep 2002
Posts:
192
0
:snip:

I did make a spreadsheet... but after reading carefully your posts again... i dont think my sheet will be any help.

im not really sure how to do it... i thought i had it figured but it does seem harder than i first thought.

-ƒlamika

Last edited: 17 Oct 2002
10. ### A[L]CWhat's a Dremel?

Joined:
23 Oct 2001
Posts:
293
0
im pretty good at excel (well coding the VBA within it) but I dont have a clue what you are on about with regards to the maths

sorry

11. ### Dr_kazzaWhat's a Dremel?

Joined:
2 Nov 2001
Posts:
395
0
had to resort to approximating the best fit using the least squares method and getting solver to work out the coefficients and taking it from there... thx for the help anyway guys

Kaz

Joined:
17 Oct 2002
Posts:
1