Pulling Constants from a 3rd Degree polynomial Graph

sapheri

New Member
Joined
Feb 27, 2015
Messages
3
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.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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

New Member
Joined
Feb 27, 2015
Messages
3
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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):
Code:
=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(!).
 

Watch MrExcel Video

Forum statistics

Threads
1,099,520
Messages
5,469,119
Members
406,637
Latest member
Mbsmbs

This Week's Hot Topics

Top