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
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
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
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.
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?
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
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.
: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. sorry about that. -ƒlamika
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
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
I wrote this in between doing some work, see that you've figured it out anyway Get Sigmaplot Here's a way to fit a general equation to data in Excel by minimising the sum of squared deviation using solver, bear in mind that Excel is really not good software for doing this sort of thing, many of the algorithms are not well implemented..... It should be OK for what you want though. Put your x data in col A, y in col B. in col C put starting values for the parameters you want to optimise, say for a quadratic y = Ax^2+Bx+C put "any" values in C1, C2 & C3 for the parameters A, B & C. Then in col D put in the function that you want to fit - for the quadratic it would be =$C$1*A1^2+$C$2*A1+$C$3. In col E calculate the squared difference of the function and the data (i.e. =(D1-B1)^2) sum this column (e.g. for 20 data cell E21 =SUM(E1:E20)). Then start solver, (tools menu, you may need to install it first - see the solver entry in help), set the cell with the sum of squared deviations as the target, set it to minimise by changing the cells containing your parameters. It's possible it won't find exactly the same values as it gives you for the graph, as I said the algorithms aren't well implemented.....
