Using Excel

1
Using Excel

• Biostatistics 212
• Lecture 4

2
Housekeeping

• Questions about Lab 3?
• Do file development, workflow
• White space and comments
• Spelling, capital letters, commas
• Use help and drop-down menus for syntax help
• recode vs replace
• Final Project Dataset!

3
Today...

• Why are we talking about spreadsheets?
• Pros and Cons of using a spreadsheet for
• Data management, Statistics, Calculating,
  Modeling, Tables, Figures
• Cells
• Formulas
• Cutting and pasting formulas
• Spreadsheet style
• Examples

4
Why spreadsheets?

• Excel is widely used, and for good reason
• Store numbers and text
• Calculations
• Desktop graphics Tables and Figures
• Flexible creation of ledgers, models, other
  complex programs

5
Why spreadsheets?

• How is a spreadsheet different than Statas data
  editor?
• Less structured
• Formulas
• Formatting

6
Why spreadsheets?

• How is a spreadsheet different than a database
  program like Access?
• Less structured
• Formula chains
• Formatting

7
Pros and Cons of spreadsheets

• For data management
• Pros
• Easy start just name columns and start typing
• Cons
• No structure
• Cant sort, filter or query data
• Flat file no relational table structure
  allowed

8
Pros and Cons of spreadsheets

• For statistical analysis
• Pros
• Easy start, if you know how to do formulas
• Cons
• Extremely limited range of options
• Difficult to document

9
Pros and Cons of spreadsheets

• For calculating, or modeling
• Pros
• Repetitive calculations easy
• Complex calculations easy
• Cons
• Simple, 1-time calculations not as fast as a
  calculator
• Sometimes hard to decipher in retrospect

10
Pros and Cons of spreadsheets

• Tables and Figures will discuss in Sessions 5
  and 6

11
Cells

• The basic building block of a spreadsheet
• Can contain
• Numbers
• Text
• Dates, times, other special formats
• blanks
• Start with 46 million blank cells!
• (230 cols x 66536 rows x 3 worksheets)

12
Cells, cont

• Enter anything you like into each cell (numbers,
  text, symbols, etc) using keyboard
• Contents displayed on spreadsheet
• Organized and named by column/row

13
Formulas

• Use when you want the contents of one cell to
  depend on the contents of other cells
• ALWAYS starts with
• (an equals sign)

14
Formulas

• Can contain
• Numbers
• Text
• References to cells
• The usual math operators ( - / )
• Built-in functions

15
Formulas

• Cell contents update automatically when a
  referenced cell content changes
• Chains of formulas make for flexible calculating

16
Formulas

• Contents of a cell displayed on spreadsheet
• The formula determining that content is displayed
  in the formula box
• Example

17
Formulas

• Types of formulas
• Arithmetic
• , -, , /,
• Logic
• IF(boolean, value 1, value 2)
• Returns value 1 if TRUE, value2 if FALSE
• AND(boolean, boolean, boolean)
• Returns TRUE if all booleans are true, otherwise
  FALSE
• OR(boolean, boolean, boolean)
• Returns TRUE if any booleans are true, otherwise
  FALSE

18
Formulas

• Types of formulas, cont
• Functions, for example
• SUM(range of cells)
• Returns the sum of the values in the range
• SUM(A5A10)
• AVERAGE(range of cells)
• Returns the average of the values in the range
• STDEV(range of cells)
• Returns the standard deviation
• NORMINV(probability, mean of dist, SD of dist)
• Returns the z-value associated with a given
  probability

19
Formulas

• Types of formulas, cont
• Functions, for example
• LN(number)
• Returns the natural log of a number
• ABS(number)
• Returns the absolute value of a number
• LEFT(text, number of charactersx)
• Returns x number of characters from the text in
  the cell, starting at the left side
• NOW()
• Returns the current date, time

20
Formulas

• Tips
• Use parentheses
• IF(SUM(A5A10)5,1,IF(C9y,2,3))
• Or do in multiple steps

21
Cutting/Copying and Pasting

• Cutting and Copying treat formulas differently!

22
Cutting and pasting formulas

• Excel assumes the cell references are ABSOLUTE,
  and youre just moving the location of the
  formula cell
• Example

23
Copying and pasting formulas

• Excel assumes the cell references are RELATIVE
• Example
• Shortcut drag little square in the corner

24
Copying and pasting formulas

• If you want to FIX the position of a referenced
  cell, use s
• A5 B6
• Example

25
Examples

• Repetitive calculations
• Back-transforming linear regression coefficients
• Complex calculations
• 2 x 2 template
• Modeling
• Mortgage calculator
• Risk integrator
• Figure 1 for LDL-lowering paper

26
Spreadsheet style

• Formatting
• Text
• Column width
• Borders
• Placement of stuff on the page

27
Spreadsheet style

• For models
• Inputs on the left, in red
• Outputs on the right, in blue, boxed, bolded, etc
• Calculations on other sheets
• Protect all cells besides inputs
• Format/Cells/Protection
• Tools/Protect

28
Take home points

• Understand cells and formulas
• Use copy/paste with and without fixed cells
  (A45)
• Good formatting adds significant value to your
  spreadsheet

29
Lab 4

• Practice with
• A repetitive calculation spreadsheet
• A complex calculation spreadsheet
• Introduction to making a figure with Excel
• Due before lecture next week
• Extra credit puzzle challenge 2x2 excel
  template
• Due Sept 18th email to mpletcher_at_epi.ucsf.edu

30
To come

• Next lecture
• Epidemiologic analysis with Stata
• 2 x 2 tables, confounding and interaction
• Epitab commands
• Logistic regression introduction