Re: Getting the values of the points in a trendline

Giganews Newsgroups
Subject: Re: Getting the values of the points in a trendline
Posted by:  Peter T (peter…@discussions)
Date: Thu, 11 May 2006

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 = "0.00000000000000E+00"
    sFormula = tLine.DataLabel.Text
    tLine.DataLabel.NumberFormat = sNum
    sFormula = Application.Substitute(sFormula, _
      "y = ", "")
    sFormula = Application.Substitute(sFormula, _
      "x", "*" & ActiveCell.Offset(0, -1).Address(0, 0) & "^")
    sFormula = Application.Substitute(sFormula, _
      "^ ", " ")
    ActiveCell.Formula = "=" & 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 T

<e.alain.le…@gmail.com> wrote in message
news:1147269482.745349.2194…@i39g2000cwa.googlegroups.com...
> 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,
>
> Alain

Replies

In response to

Getting the values of the points in a trendline posted by e.alain.le…@gmail.com on 10 May 2006