asked on

Calculate Trendline

Hi
I need to calculate the trend line like the one in the attached document. In the document you can see the result in excel and that is what i have to end up with.

This is the data I got to calculate the trendline.

Year      Month      Week      Day            Open      Closed
2014      7      29      16            13      20
2014      7      29      17            28      29
2014      7      29      18            21      24
2014      7      30      21            24      30
2014      7      30      22            23      22
2014      7      30      23            25      40
2014      7      30      24            17      22
2014      7      30      25            24      31
2014      7      31      28            17      22
2014      7      31      29            12      16
2014      7      31      30            24      22
2014      7      31      31            21      25
2014      8      31      1            28      23
2014      8      32      4            20      24
2014      8      32      5            18      23
2014      8      32      6            22      30
2014      8      32      7            23      26
2014      8      32      8            16      12
2014      8      33      11            15      17
2014      8      33      12            14      24
2014      8      33      13            24      19
2014      8      33      14            12      25
2014      8      33      15            16      14
2014      8      34      18            21      30
2014      8      34      19            9      28
trendlinetest.xlsx

Brian Crowe

try the following

For a set of data points (x, y), this algorithm can be used to fit the data to any of the following curves:

1. Straight line (linear regresion); y = A + b*x
2. Exponential curve; y = A*EXP(b*x); nb a > 0
3. Logarithmic curve; y = A + b*LN(x)
4. Power curve; y = A*x^b; nb a > 0

The coefficient of determination is R2 (how well does the curve fit)
-- Prepare test data
CREATE TABLE	cf
		(
			x decimal(38, 10),
			y decimal(38, 10)
		) 

-- Calculate Linear regression
INSERT	cf
SELECT	40.5, 104.5 UNION ALL
SELECT	38.6, 102 UNION ALL
SELECT	37.9, 100 UNION ALL
SELECT	36.2, 97.5 UNION ALL
SELECT	35.1, 95.5 UNION ALL
SELECT	34.6, 94

SELECT	'Linear regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

-- Calculate Exponential regression
DELETE
FROM	cf

INSERT	cf
SELECT	.72, 2.16 UNION ALL
SELECT	1.31, 1.61 UNION ALL
SELECT	1.95, 1.16 UNION ALL
SELECT	2.58, .85 UNION ALL
SELECT	3.14, .5

SELECT	'Exponential regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

-- Calculate Logarithmic regression
DELETE
FROM	cf

INSERT	cf
SELECT	3, 1.5 UNION ALL
SELECT	4, 9.3 UNION ALL
SELECT	6, 23.4 UNION ALL
SELECT	10, 45.8 UNION ALL
SELECT	12, 60.1

SELECT	'Logarithmic regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

-- Calculate Power regression
DELETE
FROM	cf

INSERT	cf
SELECT	10, .95 UNION ALL
SELECT	12, 1.05 UNION ALL
SELECT	15, 1.25 UNION ALL
SELECT	17, 1.41 UNION ALL
SELECT	20, 1.73 UNION ALL
SELECT	22, 2 UNION ALL
SELECT	25, 2.53 UNION ALL
SELECT	27, 2.98 UNION ALL
SELECT	30, 3.85 UNION ALL
SELECT	32, 4.59 UNION ALL
SELECT	35, 6.02

SELECT	'Power regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

DROP TABLE cf

Open in new window

source: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77311

ASKER CERTIFIED SOLUTION

Henrik Klindt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Crowe

I am glad you found what you were looking for. Just request that the topic be deleted or closed without awarding points since you figured it out on your own.

Good Luck

Henrik Klindt

ASKER

I figured it out on your own.