How do I get the trendline equation from Excel to script?
 

Hi!
I want to display a bargraph including a trendline on the Internet
(using an asp-page). It doesn't matter what I use to display it. I
have found some graph-components (for example FusionCharts 2.2) that
seems ok.

First: What is the easiest way to go?
1. Calculate the trendline values (with vbscript or javascript) and
using a graphcomponent
2. or using a excelcomponent and somehow manipulate it to show the
graphs (bargraph and trendline) that I want.

IF 1: Then the problem I have is that I can not figure out how to
calculate the trendline values.

I have found a page (http://www.j-walk.com/ss/excel/tips/tip101.htm)
that has some code used in Excel (see below). But the INDEX, LINEST
functions doesn't work in vbscript.
__________________________________________________ ____
Logarithmic Trendline
Equation: y = (c * LN(x)) - b

c: =INDEX(LINEST(y,LN(x)),1)

b: =INDEX(LINEST(y,LN(x)),1,2)

Power Trendline
Equation: y=c*x^b

c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))

b: =INDEX(LINEST(LN(y),LN(x),,),1)
__________________________________________________ ____

IF 2: I am not very used to excel-programming so how can I do this
trendline using a component? Oh, the person that will see this graph
does not have the excel program.

Best regards,
Mattias in Sweden

Mattias,

My suggestion would be to keep the graph inside of an Excel workbook. You
can compute the components of the trend using the formulas that you've
documented using Excel's regular workbook functions.

Once your graph with trend is created inside of the workbook, you can then
save the graph as a picture and upload it to your site. Saving the graph at
a picture and then uploading the picture to the web can be automated using
VBA. Jon Peltier at:

http://www.peltiertech.com

has some good information on transferring Excel charts to PowerPoint
presentations. The same concepts can be used for transferring files to the
web.

Another option might be to save the sheet containing the graph as a web page
and then linking that page to your site. I've not done a whole lot of work
in this area - just another possible suggestion.


"Mattias" wrote:

Hi!
I want to display a bargraph including a trendline on the Internet
(using an asp-page). It doesn't matter what I use to display it. I
have found some graph-components (for example FusionCharts 2.2) that
seems ok.

First: What is the easiest way to go?
1. Calculate the trendline values (with vbscript or javascript) and
using a graphcomponent
2. or using a excelcomponent and somehow manipulate it to show the
graphs (bargraph and trendline) that I want.

IF 1: Then the problem I have is that I can not figure out how to
calculate the trendline values.

I have found a page (http://www.j-walk.com/ss/excel/tips/tip101.htm)
that has some code used in Excel (see below). But the INDEX, LINEST
functions doesn't work in vbscript.
__________________________________________________ ____
Logarithmic Trendline
Equation: y = (c * LN(x)) - b

c: =INDEX(LINEST(y,LN(x)),1)

b: =INDEX(LINEST(y,LN(x)),1,2)

Power Trendline
Equation: y=c*x^b

c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))

b: =INDEX(LINEST(LN(y),LN(x),,),1)
__________________________________________________ ____

IF 2: I am not very used to excel-programming so how can I do this
trendline using a component? Oh, the person that will see this graph
does not have the excel program.

Best regards,
Mattias in Sweden