Retrieve the equation of trendline from an Excel graph into Labview?

[Carla]

I plot two 1d arays in an Excel graph and generate a trendline equation and R2 value. I would like to use these in Labview for further data processing. For example, the y = -1.8424x - 0.0788
R2 = 0.9913 are generated by Excel. I would like to retrieve this information into Labview.

[Liz F]

Hello Carla,

Thank you for contacting National Instruments. The first idea that comes to mind for solving your problem is to delve into the ActiveX controls provided by Microsoft for Excel and try to locate the one that will return a stored equation or specific cell value. This would likely not be overly difficult, but it could take some time. You might take a look at the below links to get you started on that route. The links are long, so be sure to cut and paste the entire thing.

How Do I Use NI Software to Control Microsoft Excel with ActiveX?
http://digital.ni.com/public.nsf/websearch/56308664EB597E9E8625696500631D92?OpenDocument

List of Example Code for Excel via ActiveX and LabVIEW
http://search.ni.com/query.html?col=alldocs&qp=%2Bcontenttype%3AExa
mple&lk=1&qt=activex+excel+labview&layout=Example

Microsoft help on Excel and ActiveX
http://search.microsoft.com/search/results.aspx?View=msdn&st=a&qu=activex+excel&c=0&s=1

Another idea would be to read the spreadsheet information into LabVIEW from Excel and compute the trendline equation and "R2" value in LabVIEW rather than Excel. You would have to save the Excel spreadsheet as a text file (unless you have the Report Generation Toolkit, which allows direct interface with *.xls and *.doc files.) You could then use the VIs under the "Curve Fit" subpalette, found in the Functions >> Analyze >> Mathematics subpalette.

I hope this helps! Please let me know if I can be of any further assistance. Have a great day!

Liz F
National Instruments

[Carla]

Thanks Liz. I have already tried computing the trendline in LV using the Curve Fit VIs but I get different results in Excel. I shall try the ActiveX solutions now.

[anchovie]

Hi Carla,

Please have a look at the attached VIs, as I have had to complete a similar task in LabVIEW this week so you may well be in luck! The code works for charts that are embedded in worksheets, it's a slightly different set of properties and methods for charts that occupy a whole sheet but I haven't had to implement that yet.

Best regards,
James.

[Carla]

Hi anchovie,
Thanks for your response. The way you suggest is similar to what I have. However, I am unable to add a trendline and then retrieve it from the embedded chart as suggested by the Microsoft Graph help. I have attached a VI and Excel example with an embedded chart. Any suggestions are welcome???

[Perseus76]

Hi,

I have been trying to figure out on this trendline equation. Is there anyway where I can use labview to equate the trendline, instead of doing it in Excel chart itself?

[shoneill]

Hi Perseus,

Keep trying with the curve fitting VIs in Labview. There's no reason why you should be getting different results.

If you really need to do this with Excel, try placing curve parameters in the Excel table, calculate the error squared compared to the "real" results, and optimize with solver. This should give the same results as the trendline itself. The advantage is that you can simply copy the coefficients out of the excel table instead of fiddling around with the trendline.

I HAVE done what you're looking for before, but I have only been able to extract the TEXT of the trendline equation and then process the string. If you don't know EXACTLY how the string looks, this can be problematic.

Hope this helps

Shane.

[Perseus76]

Hi Shane,

Thanks for your reply, but I think you got the wrong idea. I am not getting different result, nor do I wants to do it in Excel, I am trying to do everything in Labview.

My idea is to extract data from the excel spreadsheet, and with that data, form a chart, and then the trendline equation, everything done in labview.

I am able to to almost everything, other than getting the trendline in LabVIEW. Is this possible in the first place?

[shoneill]

Yes, of course everything can be performed in LV.

Sorry, I understood from "I have already tried computing the trendline in LV using the Curve Fit VIs but I get different results in Excel" that you were giving up on calculating the fitted curve in LV because it was giving different results to Excel.

If you can tell us how you want to fit your data, we'll surely be able to show you a method of how to fit the proper curve.

Hope this helps

Shane.

Message Edited by shoneill on 06-28-2005 04:59 PM

[Ramkumar Damodaran]

Hi,

I am trying to export a monochrome picture (a simple curve) to Excel, plot a chart, perform a curve fit, calculate the Trendline equation in Excel and then show the equation in LabVIEW. I succeeded in exporting the picture to Excel and plotted the chart. But I can't able to calculate the equation and export it back to LabVIEW. I found the VIs posted in NI site but it is not working fine.

Hereby I have attached the Excel files with the VI I used to calculate the equation. Kindly let me know where did I went wrong. I experienced an error in specifying the Chart Index or name. I specified it correctly but still it returns an error.

LabVIEW code I used => ExcelTrendline.zip
Chart I plotted via LabVIEW => Charted via LabVIEW.xls
Manually fitted and equated in Excel => Fitted & Equated in XL.xls

Please help me to get out of this issue.