- Add columns and rows of numbers
- Calculate rates
- Do date arithmetic
- Calculate averages and medians using spreadsheet functions
Datamania (1:00)
1) Creating a new variable: in a new column add a variable name and in the row below add the formula for the new variable
Always sort the new variable after creation to see if there is anything interesting.
1a) Adding two variables:
- Every Excel formula starts with an ‘equal’ sign or =
- In the cell for the new variable add =cell name+cell name
- Datamania example: number of violent crimes =D2+E2
- Place the cursor on the bottom right of the cell with the new formula and double click or drag it down
1b) Adding several variables: (3:29)
- =SUM(first cell name:last cell name) allows the variable from the first to the last cell in the formula to be added up
- Datamania: number of total crimes =SUM(D2:G2)
1c) Percentage of variables: (4:41)
- = ‘part’ cell name/’total’ cell name
- Datamania: violent crimes of total crimes percentage =H2/I2
- Make this decimal into a percentage
- =H2/I2*100
- Or go to ‘Format’ and click ‘Cells’
- Go to Number and click percentage
- Choose the number of decimals and click ‘OK’
Freeze Panes can also be used to set columns: (7:12)
- Place the cursor in the cell after what needs to be frozen
- Datamania Cell C2
- Go to ‘Window’ and click ‘Freeze Panes’
1d) Adding up columns: (8:12)
- Similar to adding variables the formula is =SUM(first cell name:last cell name)
- Datamania total population =SUM(C2:C104)
- Place the cursor on the bottom right of the cell with the new formula drag to the right
1e) Don't add up percentages! (9:18)
Copy formulas:
- Go to cell J104, which has the percentage formula and copy with Command C
- CTRL C for Windows
- Paste it in J106 (Command V), applying the formula to the Datamania totals
- This is the average percentage of violent crimes in Datamania
2) Calculate rates (10:19)
Rates allow comparisons between places of different size:
- Formula: EVENTS / POPULATION * ‘Per’ Unit
- Spreadsheet formula =event cell name/population cell name*100000
- Datamania crime rates =I2/C2*100000
- This gives the number of events per 100.000 people
- Number of crimes per 100.000 people
The Fx button or ‘Insert Function’ shows the list of all Excel functions. (12:30)
3) Date arithmetic (13:12)
Date arithmetic allows for the calculation of days between 2 dates:
- =end date cell name-begin date cell name gives the number of days
- Datamania sentence years =D2-C2
- =(end date cell name-begin date cell name)/average number of days in the year
- =(D2-C2)/365.25
4) Calculate averages (mean) and median (16:06)
- =AVERAGE(first cell name:last cell name)
- Datamania average sentence years =AVERAGE(F2:F16758)
- =MEDIAN(first cell name:last cell name)
- Datamania median sentence years =MEDIAN(F2:F16758)