How to get Graph Trendline Equation constants and read into a cell formulae automatically?

[cruss]

Ladies, Gents,

Is it possible to get the equation from the graph trendline and put it back into the spreadsheet to use automatically?

I am currently typing the constants manually back into some cells to be picked up and used in a formulae - a bit hopeless.

Regards

Chris

 

[pgc01]

Sorry, I wrote array of indices but I meant array of exponents.

 

[happyhungarian]

That worked! Thanks for all the help

 

[pgc01]

You're welcome.

 

[gonjai]

Thanks for the link to the formulas, but I seem to be applying them incorrectly.

I tried to enter the following equation to get the c-value:

=INDEX(LINEST(OFFSET('Revised Net Flows'!$C$3,COUNT('Revised Net Flows'!$C$3:$C$100000),0,-24,1),OFFSET('Revised Net Flows'!$B$3,COUNT('Revised Net Flows'!$B$3:$B$100000),0,-24,2)),1)

This gives me a result of .021446 and the trendline gives the value as .022

For the b-value I entered the following:
=INDEX(LINEST(OFFSET('Revised Net Flows'!$C$3,COUNT('Revised Net Flows'!$C$3:$C$100000),0,-24,1),LN(OFFSET('Revised Net Flows'!$B$3,COUNT('Revised Net Flows'!$B$3:$B$100000),0,-24,2))),1,2)

This gives me a result of .041862 and the trendline gives the value as .0014

The offset functions are defined in a respective name but I copied them out so you can see what is going on inside.

Is there something I am typing incorrectly? I don't understand why the values vary from the trendline. Let me know if there is any additional information you may need to assist me with this issue.

Thanks!

 

[gonjai]

I misstated the results - I get .021446 when the result should be .0014, and .041862 when the value should be .022

 

[pgc01]

Hi

Without a working example it's difficult to know what's happening.

If you can post some rows of data it will be easier to discuss the results.

Check:

1 - the first formula uses the ranges directly while the second formula uses the logarithm on the independent variables. Maybe just a mistake when posting?

2 - the range of the independent variables overlaps the range of the dependent variable ???? (2 in the number of columns)

3 - Just a remark: I'm not a fan of using a negative value in the number of rows. I know that some people use it but it's very clearly against the help of Offset() that says it must be a positive number.

If you post some values it will be easier to understand the results.

 

[gonjai]

I will try to better explain my goal. FYI the workbook is not my creation, I am just trying to automate some manual process for a coworker. There are two columns of data, say A and B. Column A is months and Column B is a %. There are 11 separate and different versions of this on one worksheet(months with corresponding %). Below is an example of what I am working with.

With these 11 sets of data are a corresponding chart with a logarithmic trend-line for each. The ranges I used for the b and c values were the ranges I used to create a dynamic chart upon data entry that would simultaneously update the trend-line(sorry for the negative value in number of rows, I did not know another way to do it). So I would like to have an equation that updates with the changes in the chart to give the new trend-line without having to go into the chart and manually copy/paste the trendline after every update. I tried recording a macro but it only worked for the original run-through and from there only gave the original trendline even if the data had changed. The code for that macro is listed below along with the chart corresponding to the above data.


Sub SelectEquation()
'
' SelectEquation Macro


Sheets("Cycle 1 to Cycle 2").Select
ActiveChart.ChartArea.Select
ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
Sheets("Equations Used").Select
Range("I10").Select
ActiveSheet.Paste
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlLTR
.MergeCells = False
End With
Range("I11").Select
End Sub

I know the range of the independent variables overlapping the dependent variables is weird - for some reason that is the only way it allowed the chart to update correctly(I back-checked by applying it to previous month's file to figure out what would work).

Hopefully the images post correctly, I was also unsuccessful in indenting the code based on the preview even though I tried to space it out(I am brand new to this blog)

 

[dhavals]

I am using the formula that the original poster provided and it’s awesome.

There’s just one problem I face.

My set of data is populated from an API and it changes based on what the user specifies. I plot two of the variables (date and ‘some number’) on a line chart and I want to get the 3rd order polynomial tendline for that line.

The problem is in my two arrays I use #N/As to deal with some of the data I don’t want feeding in into the sheet. Now the formula will show an error when it tries to evaluate this formula because you can’t raise a #N/A to a power. I tried using IFERROR($A$1:$A$50. 0) which works as I don’t get a #VALUE error but I get the wrong constant because it’s assumig the 0 is apart of the dataset instead of just being used as a place keeper.

This is not the exact formula I’m using (just left on a trip and couldn’t deal with this error before I left and it’s bothering me!)

But the two arrays (x var, y bar) are pretty simple expect that there may be #N/As within the columns.

Hope someone can help! Thanks!