Hi, I'm trying to replicate the Excel TREND() function in SQL Server to get the same result. I'm using SQL Server 2016.

Below is the code to create the temporary table of values that I'm working with:

In excel I can calculate the TREND using the following equation:

But I'd like to calculate the same result of '88.23' in SQL Server?

Thanks

Below is the code to create the temporary table of values that I'm working with:

```
DECLARE @t TABLE
(
x FLOAT,
y FLOAT
);
INSERT INTO @t
SELECT *
FROM
(
SELECT '44.51' [x],
'12.95' [y]
UNION ALL
SELECT '66.31' [x],
'6.77' [y]
UNION ALL
SELECT '84.6' [x],
'4.55' [y]
UNION ALL
SELECT NULL [x],
NULL [y]
) T1;
```

In excel I can calculate the TREND using the following equation:

```
=TREND(B2:B4,C2:C4,LN(4))
```

But I'd like to calculate the same result of '88.23' in SQL Server?

Thanks

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQL

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

andxheaders. At a minimum the headers are not compatible with the order you passed them to the TREND() function, they are reversed. I assumed the headers were wrong and the TREND() usage was correct.yI don't think there is any easy way to do this SQL Server like there is in Excel, unless you have the SQL Server Analysis Services installed? Then you may find some tools in there that can help.

If you don't, like me, then you have to write your own. Or find someone that already has, which is the path I went for a quick test.

I did a little searching and found this page:

T-SQL Starter Statistics Package for SQL ServerPay particular note to the section titled "

A stored procedure to compute the slope and intercept" since I think that is key to what you need.(notice you can download some of the code ina ZIP file at the end of the article.)

So I took that code and added in your data (from Excel not SQL clip since I wanted to get to the answer you showed with TREND() in Excel. I ran the code below, and got output values of:

slope = -37.868587619818

intercept = 140.734839841636

These will be used in the basic linear formula of

y = mx + bwhere

= slope andm= interceptbPlugging in these results from the code below and solving for you LN(4) value of

give:x= (-37.868587619818 * 1.386294361) + 140.734839841636y= 88.23783036yWhich was the result you seeked per your Excel example.

Okay, that's about all the time I can ghive it, hopefully this gives you a foothold up on how you might approach this, and you can do the remaining stuff to wrap and use as you need. Good luck!

Open in new window

»

bp