How to view the values of a trend line

[trevian3969]

Hello,

I have data about the sales of a period of time, I created a graph and a trendline based on that data.


How can I found the values of the trendline, I need to know exactly the values.


And, How much data do I ned to have in order to create a trend line, I know that much is better, but exist some rule, for example 100 values, 200 ?


Thanks

 

[Hui]

Trevian

One of the Options when you set a Trendline is to display Option on Chart

You can also access this by selecting the Trendline and rightclick select Format trendline


depending on what type of Trendline you chose you will get a equation like

y=0.3x + 12.9 etc

of course the formula will be totally different if you chose logarithmic, exponential trendlines


You can take the equation and setup a cell where you enter the equation

B10: =0.3*A10+12.9


Now the good thing about this is that you can do 2 things

Add the new data as a new series to your chart

Add a slider which is linked to the Cell A10 which will allow you to interactively change the new point on the chart

 

[keymaster]

@Hui .. interesting technique on the slider controlled point to show interactive trendlines. I will write about this sometime

 

[Hui]

Trevian


The following User Defined Function UDF will do what you want

Copy and paste it into a Code Module in VBA


To use it just enter =TrendY(value) in a cell


ie: =TrendY(20)


It is assuming that your chart is Chart1 and that there is only 1 trendline on the chart

The function works with all the trendline types and variants of those

If you don't have the Trendlines equation shown it will do that for you.

That is actually where the function gets the equation from in the first place.


Special Thanx to Daniel from http://www.excelhero.com/blogs/ for assistance with a memory leak and some code optimisation.


I have posted a working example of this at http://rapidshare.com/files/421807902/Trendline_Estimator.xlsb


It has been tested with Excel 2007 & 2010 an is ok, no promises on earlier versions

Function Trendy(ByVal XVal As Double) As Variant
'
' A Function to return a Y Value to a Charts Trendline equation
'
' Written by Ian Huitson
' Sept 2010
'
' Code improvements by Daniel Ferry
' http://www.excelhero.com/blogs/
'

Dim m As Double, a As Double, b As Double, c As Double
Dim num As Double, tval As Double, movav As Double
Dim myTrend As String
Dim lngPtr As Long

Const F = 5 'Formula Start position in strings
Const EXPO = 5
Const LINEAR = -4132
Const LOGO = -4133
Const POLYNOMIAL = 3
Const POWER = 4

DoEvents
DoEvents

With Sheet1.ChartObjects(1).Chart.SeriesCollection(1).Trendlines(1)

.DisplayEquation = True
.DataLabel.NumberFormat = "#,##0.0000"
myTrend = .DataLabel.Text

If .Type = EXPO Then
'y = 66.983e0.0107x
lngPtr = InStr(F, myTrend, "e&#34
a = Mid(myTrend, F, lngPtr - F)
b = Mid(myTrend, lngPtr + 1, Len(myTrend) - lngPtr - 1)
Trendy = a * EXP(b * XVal)

ElseIf .Type = LINEAR Then
'y = 1.0562x + 66.273
lngPtr = InStr(F, myTrend, "x&#34
m = Mid(myTrend, F, lngPtr - F)
c = Right(myTrend, Len(myTrend) - lngPtr - 2)
Trendy = XVal * m + c

ElseIf .Type = LOGO Then
'y = 14.195ln(x) + 48.775
a = Mid(myTrend, F, InStr(F, myTrend, "ln&#34 - F)
b = Right(myTrend, Len(myTrend) - InStr(F, myTrend, " + &#34 - 2)
Trendy = a * LOG(XVal) + b

ElseIf .Type = POLYNOMIAL Then
'y = -0.0534x2 + 3.2448x + 50.953
'with up to power 6
'y = 5E-07x6 - 1E-05x5 - 0.0016x4 + 0.0792x3 - 1.1071x2 + 5.4142x + 61.542
polyorder = .Order

'Increase decimals to improve accuracy
.DataLabel.NumberFormat = "#,##0.000000000000"
DoEvents 'Updates Equation Textbox on screen

'get formula for Polynomial equation
myTrend = .DataLabel.Text

'Reset decimals for Display
.DataLabel.NumberFormat = "#,##0.0000"
DoEvents

tval = 0 'temporary counter

If InStr(1, myTrend, "x6&#34 > 0 Then
lngPtr = InStr(1, myTrend, "=&#34
num = Mid(myTrend, lngPtr + 1, InStr(1, myTrend, "x6&#34 - lngPtr - 1)
tval = tval + num * (XVal ^ 6)
End If

If InStr(1, myTrend, "x5&#34 > 0 Then
If polyorder = 5 Then
lngPtr = InStr(1, myTrend, "=&#34
num = Mid(myTrend, lngPtr + 1, InStr(1, myTrend, "x5&#34 - lngPtr - 1)
Else
lngPtr = InStr(1, myTrend, "x6&#34
num = Mid(myTrend, lngPtr + 2, InStr(1, myTrend, "x5&#34 - lngPtr - 2)
End If
tval = tval + num * (XVal ^ 5)
End If

If InStr(1, myTrend, "x4&#34 > 0 Then
If polyorder = 4 Then
lngPtr = InStr(1, myTrend, "=&#34
num = Mid(myTrend, lngPtr + 1, InStr(1, myTrend, "x4&#34 - lngPtr - 1)
Else
lngPtr = InStr(1, myTrend, "x5&#34
num = Mid(myTrend, lngPtr + 2, InStr(1, myTrend, "x4&#34 - lngPtr - 2)
End If
tval = tval + num * (XVal ^ 4)

End If
If InStr(1, myTrend, "x3&#34 > 0 Then
If polyorder = 3 Then
lngPtr = InStr(1, myTrend, "=&#34
num = Mid(myTrend, lngPtr + 1, InStr(1, myTrend, "x3&#34 - lngPtr - 1)
Else
lngPtr = InStr(1, myTrend, "x4&#34
num = Mid(myTrend, lngPtr + 2, InStr(1, myTrend, "x3&#34 - lngPtr - 2)
End If
tval = tval + num * (XVal ^ 3)

End If
If InStr(1, myTrend, "x2&#34 > 0 Then
If polyorder = 2 Then
lngPtr = InStr(1, myTrend, "=&#34
num = Mid(myTrend, lngPtr + 1, InStr(1, myTrend, "x2&#34 - lngPtr - 1)
Else
lngPtr = InStr(1, myTrend, "x3&#34
num = Mid(myTrend, lngPtr + 2, InStr(1, myTrend, "x2&#34 - lngPtr - 2)
End If
tval = tval + num * (XVal ^ 2)

End If
If InStr(1, myTrend, "x + &#34 > 0 Then
lngPtr = InStr(1, myTrend, "x2&#34
num = Mid(myTrend, lngPtr + 2, InStr(1, myTrend, "x + &#34 - lngPtr - 2)
tval = tval + num * XVal
tval = tval + Right(myTrend, Len(myTrend) - InStr(1, myTrend, "x + &#34)

End If
If InStr(1, myTrend, "x - &#34 > 0 Then
lngPtr = InStr(1, myTrend, "x - &#34
num = Mid(myTrend, InStr(1, myTrend, "x2&#34 + 2, lngPtr - InStr(1, myTrend, "x2&#34 - 2)
tval = tval + num * XVal
tval = tval + Right(myTrend, Len(myTrend) - lngPtr)
End If

Trendy = tval

ElseIf .Type = POWER Then
'y = 55.426x0.1479
lngPtr = InStr(F, myTrend, "x&#34
a = Mid(myTrend, F, lngPtr - F)
b = Right(myTrend, Len(myTrend) - lngPtr)
Trendy = a * (XVal ^ b)

Else
MoveAv = .Period
If MoveAv <= [B43] Then
Trendy = Application.Evaluate("sum(offset(C1,B43,,-" & MoveAv & &#34)&#34 / MoveAv
Else
Trendy = "Can't calc"
End If

End If
End With

End Function