An R tutorial for Microsoft Excel users

If you currently use a spreadsheet like Microsoft Excel for data analysis, you might be interested in taking a look at this tutorial on how to transition from Excel to R by Tony Ojeda. The tutorial explains how to use R functions in place of Excel formulas, including tools like =AVERAGE and =VLOOKUP. For the most part, it uses modern R packages to keep the R code clear and concise.

You'll likely still be using Excel as a data source, though, so you'll also want to check out this guide to importing data from Excel to R from MilanoR.

Comments

[Tony Ojeda]

Thanks for the mention, David!

[Egbert]

Thanks for this useful and clear tutorial!

[Robert Brown]

This tutorial really provided some great insights into how to use R more effectively, not just for data analysis, but for business case analysis as well. The issue, I think, lies with the proper use of data frames and tables and array abstraction.

Excel and CSV files are good to use for data sources, but Excel is just simply a horrible modeling environment for at least the following reasons:
1. model logic is not transparent
2. the cell referencing formula syntax restricts flexibility and dimensional-extensibility as models become more complex; i.e., the developing structure of a model becomes a function of the prior structure of the model.
3. Lack of transparency and cell referencing increases the likelihood of error introduction and propagation.

R helps to avoid many of the pitfalls associated with using spreadsheets, but the feature that it lacks is that it doesn't provide an influence diagram to help modelers and model clients to understand the context and flow of logic in a model. My advice is to always create an influence diagram first before you attempt to do any modeling in any environment. OMNIGraffle or Visio are good tools for creating influence diagrams.

To the degree that anyone is interested, I published a tutorial on using R as a replacement for spreadsheets during typical business case analysis.
https://leanpub.com/bizanalysiswithr/
I think it provides a unique perspective on how to use R for a type of analysis that is atypical for R, and it points a way to getting away from spreadsheets as the modeling environment of choice for most finance people.

[Zeeshan Majeed Bhatti]

What about for just a 1 year growth rate? In excel, if I use (1,,-X,Y), then that is one iteration, but isn’t that what you would use for a 2 year growth rate? On the calculator website, it says to input the number of years minus 1 for the number of years in between. If I put in 0 I get an error in excel. Any suggestions? excel formulas: Excel Formulas

[Uzmi]

Microsoft excel formulas excel tutorial, It is a route for you to make estimations on information that is as of now written in the spreadsheet excel formulas.