Pulling Constants from a 3rd Degree polynomial Graph

[sapheri]

Hello,

I have a spreadsheet where I input values and then these values are graphed into a 3rd degree polynomial. The equation of the polynomial is displayed on the graph. I'd like to make it to where the values of the constants are transcribed into separate cells.

I've put the spreadsheet into dropbox so you can see what I'm talking about: https://www.dropbox.com/s/hc9w6clw65p489d/New Protein Spreadsheet.xltm?dl=0

Thanks for the help.

 

[joeu2004]

I have a spreadsheet where I input values and then these values are graphed into a 3rd degree polynomial. The equation of the polynomial is displayed on the graph. I'd like to make it to where the values of the constants are transcribed into separate cells.

I've put the spreadsheet into dropbox so you can see what I'm talking about: https://www.dropbox.com/s/hc9w6clw65p489d/New Protein Spreadsheet.xltm?dl=0

Thanks for the help.

Refer to my response (#3) in http://www.mrexcel.com/forum/excel-questions/838539-trend-line.html#post4086894 (click here).

Is that sufficient?

If not, I can tailor the explanation to your example. Let me know.

 

[sapheri]

Sorry I can get into the Format Trendline Label I just cant figure out how to get each coefficient to go into a cell from there.

 

[joeu2004]

Sorry I can get into the Format Trendline Label I just cant figure out how to get each coefficient to go into a cell from there.

Right-click on the trendline "label" (equation), click on Format Trendline Label, then select and enter the options shown below.

The result is something like:

Highlight each constant as shown and paste into B6, B7, B8 and B9 of the "Standard Curve Preparation" worksheet.

When you paste into Excel, be mindful of the "sign" (operator) before the constant in the equation, circled above. For example, to paste the x^2 coefficient into B7, select B7, press "-", then paste so you actually enter -5.26348651348642E-01.

When you are done, the constants might look the same in B6:B9 due to formatting. But if you look at them in the Formula Bar, you will see they have greater precision. For example:

I will post the LINEST alternative in a separate response. I am worried about potential limits on the size of postings in this forum. I ran into that problem once before.

 

[joeu2004]

Sorry I can get into the Format Trendline Label I just cant figure out how to get each coefficient to go into a cell from there.

See my previous posting (#4) for a manual procedure.

Alternatively, we should be able to use LINEST to calculate the coefficients directly in the Excel worksheet.

But beware: sometimes the LINEST coefficients are different from the chart trendline coefficients. Usually, they are equivalent; the difference is due to differences in the internal algorithms. But sometimes, LINEST fails where the chart trendline succeeds.

Ostensibly, you would select B6:B9 and array-enter the following formula (press ctrl+shift+Enter instead of just enter):

=TRANSPOSE(LINEST('Standard Curve Preparation'!$A$17:$A$33,'Standard Curve Preparation'!$C$17:$C$33^{1,2,3}))

I would like to test that myself. I cannot because the range C17:C33 contains the values "N/A" instead of numbers. In fact, C32:C33 is outside the table; they are part of the instructions(!).

Honestly, given that fact and the fact that Automatic calculation mode is selected, I do not understand how the chart works. It continues to work even after I press ctrl+shift+alt+F9(!).

 

[sapheri]

Thank you for the help i really appreciate it!!