Lab 4 - Excel Formulas

Introduction

In this lab you will be using Microsoft Excel to manipulate some data from the 2000 Census. All of the data used in this lab was obtained from the census bureau's website at www.census.gov . The numbers used in the labs are the official counts for the state of Washington broken down by age.

The lab 4 spreadsheet

Right-click on the link to the spreadsheet file lab04.xls and download it to your system and save it to disk somewhere. After you have saved it to disk, open the file with MS Excel.

Spreadsheets are a grid of boxes that are used to hold information. Each box is called a cell. The cells are referenced by row and column. The rows are numbered one at the top and in increasing order going down the page. The columns are lettered starting with A and continuing to the right. So the cell in the top right corner is indicated by A1.

Light Reformatting

In the beginning, the spreadsheet just looks like a table of numbers without much rhyme or reason to it. Let's make it a little prettier so that it's easier to work with.

This is Census data for the state of Washington broken down by age group and gender. The way the spreadsheet looks now, it's hard to tell what the categories are and where the data starts. Let's make the column titles bold so it will be easier to tell.

Click in cell A1 and drag the mouse to the right so that the top three boxes (A1, B1, and C1) are highlighted. Then hit CTRL B to make it bold. You can also select the B from the tool bar or go into Format>Cells> and then under the font tab select bold to accomplish the same task.

The numbers are also a little hard to read because they are missing commas to denote place values. By default, Excel does not do anything to format your numbers so it is up to you to indicate what kind of numbers they are. You would want your numbers to be handled differently if they were actually time values or amounts of money rather than just counts.

Click and drag so that all of numbers in columns B and C are highlighted. Then go to Format>Cells and when the box pops up, make sure you are on the Number tab. Select Custom from the menu and then the #,##0 option as shown below. This option means that commas will be shown between the thousands and hundreds place.

Functions

Now let's use a function to calculate the total male and female populations.

Click in cell B21. We're going to put the total sum of all the numbers above this one in column B. The SUM function is used to compute the sum of a range of cells and places it in the cell where the function was typed.
To indicate that you are typing in a function, you must start with =. Otherwise Excel just assumes you are entering ordinary data.
The sum function is of the form =SUM(number1,number2) Where number1 is the first cell or range in the list and number2 is another cell or range. There can be any number of these cells or ranges specified. To indicate a range, all we need to to do is highlight the cells that we want added up and Excel will fill in the range entry for us.
Type in =sum( and then highlight all of the numbered cells in column B. Notice that Excel puts in the values for the range based on what cells you have highlighted. It should now look like =sum(B2:B20)
Hit enter and the sum, instead of the function, will appear in the cell. However, if you click on the cell, the function will be shown in the function box above the spreadsheet even though the data is shown in the cell.
Repeat the same steps to total the female population in cell C21.
Since the A column has the titles for each row of data, let's title this row TOTAL. Make this cell bold also.

Math Functions and Filling

Excel does regular infix math operations as functions also. You can use + for add, - for subract, * for multiply, and / for divide just as you would use them in a math function. Also, Excel preserves the regular order of operations for math, so make sure to use parentheses to indicate mathematical precedence.

Now let's add up the female and male populations to get the total for each age group and put them in Column D.

Click on cell D2. This is where we will put the total population under age 5.
Since we are only adding two numbers let's use the + operator in our function instead of the whole sum function.
First type = to indicate we are using a function. Then click cell B2 as our first cell to add. Then type + and then click on cell C2. It should appear as =B2+C2
Hit enter to make the total population appear in this cell

We've done this now for one age group but it would be a pain to type all that out for 18 more. Fortunately, Excel provides a short cut. It can fill in functions for you when you are performing the same operations on the same cells in each row or column.

Click on D2 and drag down to D20 to highlight the cells you want to fill with functions
Go to Edit>Fill>Down to fill the selected cells with their correct function.
Give D1 the title Totals

Copying and Pasting Functions

You can also copy and paste functions in Excel. It behaves the same way as fill where it replaces the cells in the formula with those relative to the pasting location.

Notice that we have not yet calculated the GRAND total population of Washington State in 2000. Let's do so now and put the value into D21.

Click on cell D2 again. Copy the function with Edit>Copy or CTRL C
Now click on cell D21 and paste the function with Edit>Paste or CTRL V

The total population is now in D21.

Automatic Updating

When you change data in cells that are used in the function, the function is automatically recomputed and the answer adjusted accordingly.

Let's say you found out the count for the 20 to 24 age group was incorrect and there were actually only 189,370 females in that age group! You need to change your data and have all of the totals reflect those changes.

Click on C6, the female cell for the 20 to 24 age group and type in the number 189370.
Watch D6 as you hit enter. The number in that cell will update, as will the grand total at the bottom. This is a huge advantage to using functions in Excel. You don't have to go back through your data and find everything that is dependent on the value in C6 and update it by hand. Excel does it for you!

Boolean Operators and the If Function

Excel doesn't just do operations on numbers. It can operate with values of true and false as well.

Suppose you need to know if an age group has more males than females and record which gender has the greater population. You could use math and subtract females from males and use numbers to represent the difference but that would be more confusing to someone who was reading your data. It would be much simpler if it just read "males" if there were more males or "females" if there were more females.

The "if" function allows you to make a logical test to which Excel can assign either true or false as the answer. Then you can tell it what to display if the answer was true and what to display if the answer was false. The if function is of the format =if(logical test, result if true, result if false)

Look at the under 5 age group again. Let's put the gender that has a greater population in the next available cell E2. And type in the first part of the function =if(
Now we need a logical test. Since we want to know which gender has a greater population, let's test to see if the value in the males column is greater than the value in the females column. Your test is B2>C2. The function should now read =if(B2>C2,
Now we need to specify what should go in if the test turns out to be true. If our test is true it means that there are more males than females so we would want it to put the word males in the cell if our test is true. To indicate to Excel that you want it to print exactly what you have entered, you need to put it in quotes. Your function should now read =if(B2>C2,"males"
The last thing we need to do is specify what to put if the test is false. If it is false there are more females than males so it should be "females."
The final function is =if(B2>C2,"males","females"). Hit enter. The cell should display males
Fill down the rest of the columns so that all age groups have the same function.
Give the column the title Prominent Gender

Other Functions

Excel has tons of functions that all do different things. Feel free to go in and explore them. You can find a list of all the available functions in Insert>Function which brings up a window that looks like this:

The drop down menu provides the categories of functions and the functions themselves are displayed in list form in the box. Click on a function and a description of what it does appears at the bottom.

Formatting

Now let's clean up the spreadsheet a little bit.

Let's make the font a little bigger on the headlines. Select row 1 just clicking on the "1" in the far left margin. Select the fontsize drop down menu from the tool bar and change it to 12. Notice that the cells automatically get taller.
Notice how Prominent Gender in E1 overlaps the end of the cell. You can resize the width of the cell by clicking and dragging the line between the E and F in the header to the desired width. You can also double click on the line and it will automatically resize the width of all of the cells in the column so that the longest cell entry fits entirely in the cell. You know you can double click and have it resize when you hold the mouse over the line between them and it makes this symbol:
It's hard to tell where the data stops and the totals begin so let's put in lines to make that distinction more clear. Click on drag to select all cells from A2 to A20. Then go to Format>Cells and click on the Border tab across the top. We're going to specify a thick border along the bottom of the cells. Click on the options shown in red to the right.
Now let's put a thick border around all of our data. Select all of the cells with data in them and then go to Format>Cells and return to the border tab. This time click on the "Outline" box at the top of the window. Also, make sure you specify what thickness of border you want on the right hand side of the window.
The last thing to do is give a little bit of area above and to the left out our data. To insert a row above the top of the data, click on the 1 in the far right margin. Then Insert>Row This will make a new row above the data and move all of the data down a row. Do the same to add a column by clicking on the A on the header above and then Insert>Column

All Done!

Your final screen should look something like this. When you are all done, store the spreadsheet file on your dante account in CSS305/Lab04 so that we can look at it from the web.