*Before you start this section you need to be comfortable with basic features (1-11) such as freeze panes, sort, filter, subtotals, adding variables and copying formulas in multiple cells. Also Basic newsroom math and statistics covers the formulas and concepts used in this section.*

Open demodataSp18 spreadsheet:

First some review:

**1. Freeze panes to maintain headings** (top row) while scrolling down

Click on Cell A2

Go to ‘Windows’ and click on ‘Freeze Panes’

**2. Create a new variable**

In a new column in first row and add a variable name

For example, click on Cell K1 and create a new category called VIOLENT CRIMES

Create a formula in Cell K2

Add up cells you want =D2+E2+F2

**3. Copying formula in row or column**

Place the cursor on the bottom right of the cell with the new formula and double click or drag it down or across.

For big data set, place cursor (the big white cross) on corner of cell with formula until it turns into a skinny black cross.

**4. Adding several variables**

The formula is =SUM(first cell name:last cell name) allows the variable from the first to the last cell in the formula to be added up

For example, create a new variable in Cell L1 called TOTAL CRIMES

In Cell L2 add up all of the crimes using =SUM(D2:J2)

Then copy that formula for entire L Column

**5. Add up column and copying it across a row**

The formula is =SUM(first cell name:last cell name)

For example, go to cell A568 and type TOTAL

Go to cell C568 and add formula =SUM(C2:C566) to add the total population

Copy the formula across the row by Click + Hold + Drag to cell L568.

**6. Creating a percentage of variables**

The formula is = ‘part’ cell name/’total’ cell name

For example, to find out what percentage of TOTAL are VIOLENT CRIMES:

Create a new variable in Cell M1 called VIOLENT PERCENT

In Cell M2 write formula =K2/L2

Then turn this decimal into a percentage

Click on % in your tool bar or or go to ‘Format’ and click ‘Cells’

Then copy formula to entire column

Then sort by Violent Crimes smallest to largest to see range of variables

**7. Calculate rates**

The formula is EVENTS divided by POPULATION multiplied by PER UNIT

Common per units are 1 million, 100,000, 1,000, 100 or 1 (per capita)

In Excel the formula is =(event cell/pop cell)*1000

For example,

Go to Cell N1 and add a new column heading called TOTAL CRIME RATE (PER 1,000)

In Cell N2 write the formula =(L2/C2)*1000

Copy that formula to entire column

Then sort that column by largest to smallest

**8. Calculate mean or average**

The formula is =AVERAGE(first cell name:last cell name)

For example to calculate the average population,

Go to Cell A569 and type AVERAGE

Go to Cell C569 and put in formula =AVERAGE(C2:C566)

You can copy that formula across the row

Click + Hold + Drag across to N569

**10. Calculate median**

The formula is =MEDIAN(first cell name:last cell name)

For example,

Go to Cell A570 and type MEDIAN

Go to cell C570 and put in formula =MEDIAN(C2:C566)

You can copy that formula across the row

Click + Hold + Drag across to N570

**Answer the following questions:**

**11. What municipality has the highest total crime rate per 1,000 residents?**

**12. How many municipalities have less than 1 crime per 1,000 residents?**

**13. What municipality has the highest percentage of violent crime?**

**14. How many municipalities have a violent crime percent below 1%?**

**15. What is the average population of municipalities in NJ?**

**16. Using answer to number #15, what “average-size” city in NJ has the highest percent of violent crime?**

**17. What is the median population of municipalities in NJ?**

**18. What is the median number of crimes per 1,000 for municipalities in NJ?**