Math formulas and functions in Excel

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?

This entry was posted in Excel Tips, Lecture. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s