Spreadsheet Modeling

Spreadsheet Modeling

Downloading R

We will begin by downloading R and setting up RStudio, followed by practicing simple commands that mirror tasks we previously performed in Excel, such as basic calculations, summaries, and data viewing. We will then transition back to Excel into foundational statistical modeling by starting our exploration of linear regression.

This site has some cool educational sources for using R. The goal is to ensure that by the end of the session, every student has R and RStudio successfully installed and can run simple commands confidently.

Why have you learn this when were using Execl? Because everything your learning in excel is transferable to other data visual programs and though I donʻ t want to make R a large portion of the class just having experience in R is a resume builder.

Basics

The biggest difference between R and Excel is where you conduct your formula commands and the manipulation of the data in the Cell.

Instead of working in the formula bar in excel:

You work in the console. Instead of being able to work within a cell within a sheet (like we do in Excel) all of the commands and manipulation are conducted in the console.

Many functional characters and operators used in Excel also appear in R, often serving similar roles in formulas and expressions. Below is a comparison table of some commonly used functional characters/operators in both:

Operator	Function	Excel Example	R Example
+	Addition	=A1 + B1	x + y
-	Subtraction	=A1 - B1	x - y
*	Multiplication	=A1 * B1	x * y
/	Division	=A1 / B1	x / y
^	Exponentiation	=A1 ^ 2	x ^ 2

Examples:

1 + 1

[1] 2

2 * 2

[1] 4

Comparison Operators

Operator	Function	Excel Example	R Example
=	Equality	=A1=5	x == 5
<>	Not equal	=A1<>5	x != 5
<	Less than	=A1<5	x < 5
<=	Less than or equal	=A1<=5	x <= 5
>	Greater than	=A1>5	x > 5
>=	Greater than or equal	=A1>=5	x >= 5

Logical Operators

Operator	Function	Excel Example	R Example
AND	Logical AND	=AND(A1>5, B1<10)	x > 5 & y < 10
OR	Logical OR	=OR(A1>5, B1<10)	x > 5 | y < 10
NOT	Logical NOT	=NOT(A1=5)	!(x == 5)

Functions (Same Concept, Different Names/Syntax)

Function	Excel Syntax	R Syntax
Sum	=SUM(A1:A10)	sum(x)
Average	=AVERAGE(A1:A10)	mean(x)
Count	=COUNT(A1:A10)	length(x) or sum(!is.na(x))
Maximum	=MAX(A1:A10)	max(x)
Minimum	=MIN(A1:A10)	min(x)
IF statement	=IF(A1>5, "Yes", "No")	ifelse(x > 5, "Yes", "No")

Text Operators / Functions

Function / Operator	Excel Example	R Example
Concatenation	=A1 & B1	paste(x, y, sep = "") or paste0(x, y)
Uppercase	=UPPER(A1)	toupper(x)
Lowercase	=LOWER(A1)	tolower(x)
Length of string	=LEN(A1)	nchar(x)

Cell References vs. Variable References

Concept	Excel	R
Reference	A1, A:A	df$A[1],df$A
Also Ref	A:A	df[1] (Indexing, not quite same)

Rstudio Interface

Here is my interface you can see this website Quarto file in the top right field.

Upper Right

• Source/Script Editor (Top-Left):

  This pane is for writing, editing, and saving R scripts. You can compose multiple lines of code, add comments, and save your work for future use and reproducibility. Commands written here are not executed until you explicitly run them.

Upper Left

Environment/History/Connections/Tutorial :

• This pane provides insights into your current R session.

  • Environment: Displays all the objects (datasets, variables, functions, etc.) currently loaded in your R workspace.

  • History: Keeps a record of the commands you have executed in the console during the current session.

  • Connections: Allows you to manage connections to external data sources or databases.

  • Tutorial: Provides access to interactive tutorials for learning R.

Bottom Left

Files/Plots/Packages/Help/Viewer (Bottom-Right):

• This multifunctional pane offers various tools:

  • Files: Navigates your file system and manages files and directories within your R project.

  • Plots: Displays any plots or visualizations generated by your R code.

  • Packages: Lists installed R packages, allows you to install new ones, and load/unload packages for use.

  • Help: Provides access to R’s extensive documentation and help files for functions and packages.

  • Viewer: Used for viewing local web content generated within R, such as interactive plots or Shiny applications.