You are at: Software
Simple interface Excel R
Within several projects, ACOMED statistics was asked to provide
software solutions for application of statistical methods which are not available in MS Excel.
The customers uses MS Excel for analysis of their data, but have not the opportunity to buy (and to use)
statistical software like SPSS or SAS. An opportunity would be the open source software R, however
programming in R is too complex for many scientists. Especially, they miss simplicity of transferring data and creating graphs
known from MS Excel.
For this reason, ACOMED statistics has developed a simple tool which combines
usuability of Excel to setup an analysis and to output with the power of R to perform advanced statistical analysis.
The tool was developed in 2009. There was some interest from external statisticians, and an example of the tool
is therefore now available for downlod.
Download-Link (example kernel density curve): principle solution interface R - Excel
How the tool works
- Data and parameters of the analysis are input in Excel.
- Within Excel, a VBA-program is started by the user. Data and parameters of the analysis are automatically stored into transfer-files.
- Then R is started by the VBA program, and names of transferfiles are automatically provided to the R-script.
- MS-Excel waits until R-analysis is finished, and then loads result-files provided by R.
- Results and graphs can then be used for output as usual in MS Excel.
How to prepare the analysis (once, by statistician)
- First, the statistical analysis has to be programmed in R by the statistician resulting in a R-script.
- Within directory, three subdirectories should be created: prog (used for R-script), parameters_and_data (used for transferfiles Excel to R), results (used for result file(s) R to Excel)
- The name of the R-script is input in an Excel sheet.
- The directory of RSript.exe is input in an Excel sheet.
- Adaptation of names of files used for transfer
- Adaptation of transfer procedure if necessary
How to perform an analysis
- Input of data (input, copy and paste etc.)
- Input of parameters for statistical analysis (e.g. options, alpha-level etc.)
- Start of Analysis
- ... and wait, if analysis is time consuming (e. g. bootstrapping)
- Resultts of analysis are provided at an Excel-sheet
- if applicable, an Excel-graph is available.
The tool was used within following projects:
Auclair G, Keller T, Sinha P, Sheldon J, Rota F, Schimmel H, Zegers I (2011): Commutability study on ERM-DA472/IFCC, C-reactive protein in human serum. CCLM 49, S804
Weber S, Keller T (2009): Statistical Analysis of Commutability Experiments: Application of equivalence test as an advantageous approach. CCLM 47, A22. Download Poster
von Goessel H, Jacobs U, Semper S, Krumbholz M, Langer T, Keller T, Schrauder A, van der Velden VHJ, van Dongen JJM, Harbott J,
Panzer-Grümayer ER, Schrappe M, Rascher W, Metzler M (2008): Cluster analysis of genomic ETV6-RUNX1 (TEL-AML1) fusion sites in childhood acute lymphoblastic leukemia. Leuk Res 33(8): 1082-8
Acknowledgements: Thanks to Stephan Weber (Student in 2009, now employee of ACOMED statistics) and Maik Harteis (student) for their help.
Link to another solution: Baier T, Neuwirth E, De Meo M (2011)