Hi,
Based on a set of data, I've created a line pivot chart in Excel. I
have added a trendline, wich works perfectly! So, why this question?
Is it possible to get the specific values of a certain point on the
trendline? For example:Pivot table:
[AverageUse]
Month Percentage
1 10,1234%
2 12,4678%
3 9,4373%
Now I've create a pivot chart (that's a bit difficult to reproduce with
ASCII-art and added a trendline, now I would like to see my pivot
table like this:
[AverageUse]
Month Percentage TrendlinePercentage
1 10,1234% 9%
2 12,4678% 13%
3 9,4373% 9%Wich excel-guru has some briliant ideas?
Thanks in advance,
Best Regards,
AlainUnfortunately (as far as I know) Excel does not give you easy access to the
trendline formula or its coefficients. You can, of course, calculate these
yourself but since we know Excel has calculated them already (after all, you
can choose to display the formula) it would be nice if the Trendline object
had a .Formula property that could be used.
But the best solution is to calculate them yourself from the chart's data
series. For info on the formulas there is a handy reference he
j-walk.com/ss/excel/tips/tip101.htm
--
- K Dalesquot; wrote:
gt; Hi,
gt;
gt; Based on a set of data, I've created a line pivot chart in Excel. I
gt; have added a trendline, wich works perfectly! So, why this question?
gt;
gt; Is it possible to get the specific values of a certain point on the
gt; trendline? For example:
gt;
gt;
gt; Pivot table:
gt;
gt; [AverageUse]
gt; Month Percentage
gt; 1 10,1234%
gt; 2 12,4678%
gt; 3 9,4373%
gt;
gt; Now I've create a pivot chart (that's a bit difficult to reproduce with
gt; ASCII-art and added a trendline, now I would like to see my pivot
gt; table like this:
gt;
gt; [AverageUse]
gt; Month Percentage TrendlinePercentage
gt; 1 10,1234% 9%
gt; 2 12,4678% 13%
gt; 3 9,4373% 9%
gt;
gt;
gt; Wich excel-guru has some briliant ideas?
gt;
gt; Thanks in advance,
gt;
gt; Best Regards,
gt;
gt; Alain
gt;
gt;
Look into Linest or better still various post by David Braden on the
subject.
If you particularly want to replicate your own trendline's formula you could
try something like this - parses the formula of trendline(1) in series 1 to
a cell formula.
start by selecting a cell offset one to right of the first value you want to
calculate
Sub GetFormula1()
Dim sFormula As String
Dim ser As Series
Dim tLine As Trendline
Dim cht As Chart, sNum As String
Set cht = ActiveSheet.ChartObjects(1).Chart
Set ser = cht.SeriesCollection(1)
If ser.Trendlines.Count = 1 Then
Set tLine = ser.Trendlines(1)
tLine.DisplayEquation = True
If tLine.DisplayEquation Then
sNum = tLine.DataLabel.NumberFormat
tLine.DataLabel.NumberFormat = quot;0.00000000000000E 00quot;
sFormula = tLine.DataLabel.Text
tLine.DataLabel.NumberFormat = sNum
sFormula = Application.Substitute(sFormula, _
quot;y = quot;, quot;quot;)
sFormula = Application.Substitute(sFormula, _
quot;xquot;, quot;*quot; amp; ActiveCell.Offset(0, -1).Address(0, 0) amp; quot;^quot;)
sFormula = Application.Substitute(sFormula, _
quot;^ quot;, quot; quot;)
ActiveCell.Formula = quot;=quot; amp; sFormula
End If
End If
End Sub
In xl2K can use Replace iso Application.Substitute
In a long discussion last year it became clear that the formula is useless
without a high degree of precision.
Regards,
Peter Tgt; wrote in message oups.com...
gt; Hi,
gt;
gt; Based on a set of data, I've created a line pivot chart in Excel. I
gt; have added a trendline, wich works perfectly! So, why this question?
gt;
gt; Is it possible to get the specific values of a certain point on the
gt; trendline? For example:
gt;
gt;
gt; Pivot table:
gt;
gt; [AverageUse]
gt; Month Percentage
gt; 1 10,1234%
gt; 2 12,4678%
gt; 3 9,4373%
gt;
gt; Now I've create a pivot chart (that's a bit difficult to reproduce with
gt; ASCII-art and added a trendline, now I would like to see my pivot
gt; table like this:
gt;
gt; [AverageUse]
gt; Month Percentage TrendlinePercentage
gt; 1 10,1234% 9%
gt; 2 12,4678% 13%
gt; 3 9,4373% 9%
gt;
gt;
gt; Wich excel-guru has some briliant ideas?
gt;
gt; Thanks in advance,
gt;
gt; Best Regards,
gt;
gt; Alain
gt;
I forgot to also to say this parses a third order polynomial, would need to
adapt for other formulas.
Regards,
Peter T
quot;Peter Tquot; lt;peter_t@discussionsgt; wrote in message
...
gt; Look into Linest or better still various post by David Braden on the
gt; subject.
gt;
gt; If you particularly want to replicate your own trendline's formula you
could
gt; try something like this - parses the formula of trendline(1) in series 1
to
gt; a cell formula.
gt;
gt; start by selecting a cell offset one to right of the first value you want
to
gt; calculate
gt;
gt; Sub GetFormula1()
gt; Dim sFormula As String
gt; Dim ser As Series
gt; Dim tLine As Trendline
gt; Dim cht As Chart, sNum As String
gt; Set cht = ActiveSheet.ChartObjects(1).Chart
gt; Set ser = cht.SeriesCollection(1)
gt; If ser.Trendlines.Count = 1 Then
gt; Set tLine = ser.Trendlines(1)
gt; tLine.DisplayEquation = True
gt; If tLine.DisplayEquation Then
gt; sNum = tLine.DataLabel.NumberFormat
gt; tLine.DataLabel.NumberFormat = quot;0.00000000000000E 00quot;
gt; sFormula = tLine.DataLabel.Text
gt; tLine.DataLabel.NumberFormat = sNum
gt; sFormula = Application.Substitute(sFormula, _
gt; quot;y = quot;, quot;quot;)
gt; sFormula = Application.Substitute(sFormula, _
gt; quot;xquot;, quot;*quot; amp; ActiveCell.Offset(0, -1).Address(0, 0) amp; quot;^quot;)
gt; sFormula = Application.Substitute(sFormula, _
gt; quot;^ quot;, quot; quot;)
gt; ActiveCell.Formula = quot;=quot; amp; sFormula
gt; End If
gt; End If
gt; End Sub
gt;
gt; In xl2K can use Replace iso Application.Substitute
gt;
gt; In a long discussion last year it became clear that the formula is useless
gt; without a high degree of precision.
gt;
gt; Regards,
gt; Peter T
gt;
gt;
gt; gt; wrote in message
gt; oups.com...
gt; gt; Hi,
gt; gt;
gt; gt; Based on a set of data, I've created a line pivot chart in Excel. I
gt; gt; have added a trendline, wich works perfectly! So, why this question?
gt; gt;
gt; gt; Is it possible to get the specific values of a certain point on the
gt; gt; trendline? For example:
gt; gt;
gt; gt;
gt; gt; Pivot table:
gt; gt;
gt; gt; [AverageUse]
gt; gt; Month Percentage
gt; gt; 1 10,1234%
gt; gt; 2 12,4678%
gt; gt; 3 9,4373%
gt; gt;
gt; gt; Now I've create a pivot chart (that's a bit difficult to reproduce with
gt; gt; ASCII-art and added a trendline, now I would like to see my pivot
gt; gt; table like this:
gt; gt;
gt; gt; [AverageUse]
gt; gt; Month Percentage TrendlinePercentage
gt; gt; 1 10,1234% 9%
gt; gt; 2 12,4678% 13%
gt; gt; 3 9,4373% 9%
gt; gt;
gt; gt;
gt; gt; Wich excel-guru has some briliant ideas?
gt; gt;
gt; gt; Thanks in advance,
gt; gt;
gt; gt; Best Regards,
gt; gt;
gt; gt; Alain
gt; gt;
gt;
gt;
I forgot to also to say this parses a third order polynomial, would need to
adapt for other formulas.
Regards,
Peter T
quot;Peter Tquot; lt;peter_t@discussionsgt; wrote in message
...
gt; Look into Linest or better still various post by David Braden on the
gt; subject.
gt;
gt; If you particularly want to replicate your own trendline's formula you
could
gt; try something like this - parses the formula of trendline(1) in series 1
to
gt; a cell formula.
gt;
gt; start by selecting a cell offset one to right of the first value you want
to
gt; calculate
gt;
gt; Sub GetFormula1()
gt; Dim sFormula As String
gt; Dim ser As Series
gt; Dim tLine As Trendline
gt; Dim cht As Chart, sNum As String
gt; Set cht = ActiveSheet.ChartObjects(1).Chart
gt; Set ser = cht.SeriesCollection(1)
gt; If ser.Trendlines.Count = 1 Then
gt; Set tLine = ser.Trendlines(1)
gt; tLine.DisplayEquation = True
gt; If tLine.DisplayEquation Then
gt; sNum = tLine.DataLabel.NumberFormat
gt; tLine.DataLabel.NumberFormat = quot;0.00000000000000E 00quot;
gt; sFormula = tLine.DataLabel.Text
gt; tLine.DataLabel.NumberFormat = sNum
gt; sFormula = Application.Substitute(sFormula, _
gt; quot;y = quot;, quot;quot;)
gt; sFormula = Application.Substitute(sFormula, _
gt; quot;xquot;, quot;*quot; amp; ActiveCell.Offset(0, -1).Address(0, 0) amp; quot;^quot;)
gt; sFormula = Application.Substitute(sFormula, _
gt; quot;^ quot;, quot; quot;)
gt; ActiveCell.Formula = quot;=quot; amp; sFormula
gt; End If
gt; End If
gt; End Sub
gt;
gt; In xl2K can use Replace iso Application.Substitute
gt;
gt; In a long discussion last year it became clear that the formula is useless
gt; without a high degree of precision.
gt;
gt; Regards,
gt; Peter T
gt;
gt;
gt; gt; wrote in message
gt; oups.com...
gt; gt; Hi,
gt; gt;
gt; gt; Based on a set of data, I've created a line pivot chart in Excel. I
gt; gt; have added a trendline, wich works perfectly! So, why this question?
gt; gt;
gt; gt; Is it possible to get the specific values of a certain point on the
gt; gt; trendline? For example:
gt; gt;
gt; gt;
gt; gt; Pivot table:
gt; gt;
gt; gt; [AverageUse]
gt; gt; Month Percentage
gt; gt; 1 10,1234%
gt; gt; 2 12,4678%
gt; gt; 3 9,4373%
gt; gt;
gt; gt; Now I've create a pivot chart (that's a bit difficult to reproduce with
gt; gt; ASCII-art and added a trendline, now I would like to see my pivot
gt; gt; table like this:
gt; gt;
gt; gt; [AverageUse]
gt; gt; Month Percentage TrendlinePercentage
gt; gt; 1 10,1234% 9%
gt; gt; 2 12,4678% 13%
gt; gt; 3 9,4373% 9%
gt; gt;
gt; gt;
gt; gt; Wich excel-guru has some briliant ideas?
gt; gt;
gt; gt; Thanks in advance,
gt; gt;
gt; gt; Best Regards,
gt; gt;
gt; gt; Alain
gt; gt;
gt;
gt;
I forgot to also to say this parses a third order polynomial, would need to
adapt for other formulas.
Regards,
Peter T
quot;Peter Tquot; lt;peter_t@discussionsgt; wrote in message
...
gt; Look into Linest or better still various post by David Braden on the
gt; subject.
gt;
gt; If you particularly want to replicate your own trendline's formula you
could
gt; try something like this - parses the formula of trendline(1) in series 1
to
gt; a cell formula.
gt;
gt; start by selecting a cell offset one to right of the first value you want
to
gt; calculate
gt;
gt; Sub GetFormula1()
gt; Dim sFormula As String
gt; Dim ser As Series
gt; Dim tLine As Trendline
gt; Dim cht As Chart, sNum As String
gt; Set cht = ActiveSheet.ChartObjects(1).Chart
gt; Set ser = cht.SeriesCollection(1)
gt; If ser.Trendlines.Count = 1 Then
gt; Set tLine = ser.Trendlines(1)
gt; tLine.DisplayEquation = True
gt; If tLine.DisplayEquation Then
gt; sNum = tLine.DataLabel.NumberFormat
gt; tLine.DataLabel.NumberFormat = quot;0.00000000000000E 00quot;
gt; sFormula = tLine.DataLabel.Text
gt; tLine.DataLabel.NumberFormat = sNum
gt; sFormula = Application.Substitute(sFormula, _
gt; quot;y = quot;, quot;quot;)
gt; sFormula = Application.Substitute(sFormula, _
gt; quot;xquot;, quot;*quot; amp; ActiveCell.Offset(0, -1).Address(0, 0) amp; quot;^quot;)
gt; sFormula = Application.Substitute(sFormula, _
gt; quot;^ quot;, quot; quot;)
gt; ActiveCell.Formula = quot;=quot; amp; sFormula
gt; End If
gt; End If
gt; End Sub
gt;
gt; In xl2K can use Replace iso Application.Substitute
gt;
gt; In a long discussion last year it became clear that the formula is useless
gt; without a high degree of precision.
gt;
gt; Regards,
gt; Peter T
gt;
gt;
gt; gt; wrote in message
gt; oups.com...
gt; gt; Hi,
gt; gt;
gt; gt; Based on a set of data, I've created a line pivot chart in Excel. I
gt; gt; have added a trendline, wich works perfectly! So, why this question?
gt; gt;
gt; gt; Is it possible to get the specific values of a certain point on the
gt; gt; trendline? For example:
gt; gt;
gt; gt;
gt; gt; Pivot table:
gt; gt;
gt; gt; [AverageUse]
gt; gt; Month Percentage
gt; gt; 1 10,1234%
gt; gt; 2 12,4678%
gt; gt; 3 9,4373%
gt; gt;
gt; gt; Now I've create a pivot chart (that's a bit difficult to reproduce with
gt; gt; ASCII-art and added a trendline, now I would like to see my pivot
gt; gt; table like this:
gt; gt;
gt; gt; [AverageUse]
gt; gt; Month Percentage TrendlinePercentage
gt; gt; 1 10,1234% 9%
gt; gt; 2 12,4678% 13%
gt; gt; 3 9,4373% 9%
gt; gt;
gt; gt;
gt; gt; Wich excel-guru has some briliant ideas?
gt; gt;
gt; gt; Thanks in advance,
gt; gt;
gt; gt; Best Regards,
gt; gt;
gt; gt; Alain
gt; gt;
gt;
gt;
Tushar Mehta has enhanced code by David Braden to extract coefficients
directly from a chart trendline
groups.google.com/group/micro...harting/msg/0e...
Note that for the chart trendline, you should format the equation to display
scientific notation with 14 decimal places.
Jerry
quot;K Dalesquot; wrote:
gt; Unfortunately (as far as I know) Excel does not give you easy access to the
gt; trendline formula or its coefficients. You can, of course, calculate these
gt; yourself but since we know Excel has calculated them already (after all, you
gt; can choose to display the formula) it would be nice if the Trendline object
gt; had a .Formula property that could be used.
gt;
gt; But the best solution is to calculate them yourself from the chart's data
gt; series. For info on the formulas there is a handy reference he
gt; j-walk.com/ss/excel/tips/tip101.htm
gt; --
gt; - K Dales
gt;
gt;
gt; quot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; Based on a set of data, I've created a line pivot chart in Excel. I
gt; gt; have added a trendline, wich works perfectly! So, why this question?
gt; gt;
gt; gt; Is it possible to get the specific values of a certain point on the
gt; gt; trendline? For example:
gt; gt;
gt; gt;
gt; gt; Pivot table:
gt; gt;
gt; gt; [AverageUse]
gt; gt; Month Percentage
gt; gt; 1 10,1234%
gt; gt; 2 12,4678%
gt; gt; 3 9,4373%
gt; gt;
gt; gt; Now I've create a pivot chart (that's a bit difficult to reproduce with
gt; gt; ASCII-art and added a trendline, now I would like to see my pivot
gt; gt; table like this:
gt; gt;
gt; gt; [AverageUse]
gt; gt; Month Percentage TrendlinePercentage
gt; gt; 1 10,1234% 9%
gt; gt; 2 12,4678% 13%
gt; gt; 3 9,4373% 9%
gt; gt;
gt; gt;
gt; gt; Wich excel-guru has some briliant ideas?
gt; gt;
gt; gt; Thanks in advance,
gt; gt;
gt; gt; Best Regards,
gt; gt;
gt; gt; Alain
gt; gt;
gt; gt;
Sorry about the triple post. OE, first time a TCP error, sent once again, 3
in total!
Peter T
quot;Peter Tquot; lt;peter_t@discussionsgt; wrote in message
...
gt; I forgot to also to say this parses a third order polynomial, would need
to
gt; adapt for other formulas.
gt;
留言列表
