This assignment will familiarize you with the following Excel properties:
Make sure that you are familiar and comfortable with each of these concepts. These are the foundations for the rest of our work during this semester. E-mail me at spochron@unm.edu with any questions you have.
When these instructions ask you to enter data in the spreadsheet (for example: 'Age') DO NOT INCLUDE THE QUOTATION MARKS. The quotation marks simply demarcate the information that you enter in your data base.
Be sure to save your work often because the machines may crash occasionally.
1. First, we create a simple database.
Open Excel by clicking on Excel in the Apple Menu Icon on the desktop. The small icon at the top left that looks like a multi-colored apple is the Apple Menu Icon. After a few moments you see a blank worksheet in a new workbook window.
At the very top of the workbook you see a row of words ( 'File', 'Edit', 'View', 'Insert', etc.) associated with pull-down menus. Below the menu you see two rows of icons. These icons are on toolbars and they quickly take you to commonly used commands. Notice as you slowly move your mouse pointer across these icons a small yellow flag indicates the function of each icon. Below the toolbar you see a blank space for editing called the edit bar. Below this is the worksheet organized into rows (indicated by numbers) and columns (indicated by letters).
Move the mouse pointer across the screen. Notice how it changes shape depending on where you locate it. Keep in mind that the shape of the mouse pointer tells you what kinds of actions you may perform on the data.
Open your 'Examples' workbook. Do this by clicking on the second icon from the left in the top row of the tool bar. Choose 'Desktop' and then 'LAB161' from the menu. Finally, click on 'Examples' and select 'Open'.
Select the 'Assignment#1' tab to begin this project.
Using the mouse or the arrow keys, highlight cell B3. You should see a double line surrounding this cell, as well as its reference written in the edit bar (below the toolbar).
Type the word 'Name'. Hit tab. You should now be in cell C3. Type the word 'Sex'. Hit tab. You should now be in cell D3. Type the word 'Age'. Hit return. This row will be your Variable Name row.
Blocking:
You want the Variable Name row to look different from the rest. Place your mouse pointer in the middle of cell B3. It should look like a large, white 'plus' sign. Press and hold the mouse button and drag the pointer across cells B3, C3, and D3. Release the mouse button. The cells should now be highlighted in a dark color. The color indicates that these particular cells are blocked. You can perform actions on data in all of the blocked cells simultaneously.
Click on the icon that looks like a large 'B' (about the middle of the second row of the toolbar). This enters all of the data in boldface. Click on the Center icon (a few icons to the right of the bold face icon). This centers the variable names in their columns. Finally, click on the Borders icon. This creates a line underneath all of your variable names. Your database should now look like this:
Now you are ready to enter data. Think of ten people that you know. In each row enter their first name, their age, and their sex. For the sex variable enter a 1 for females and a 0 for males. For example:
To make the data look neater block all of the data and center it just as you did before. You should now see something like this:
2. Manipulating the data.
You now have your raw data. Next create some new, synthetic variables. A synthetic variable extracts information from the raw data to form a new variable. You can create synthetic variables by performing calculations or using pre-assigned functions.
Inserting Rows and Columns:
Place the mouse pointer on top of the letter 'D' at the very top of column D. Click once on the mouse button. All of column D should now be blocked. You can verify that all of column D is blocked by placing the mouse pointer on the right scrollbar and moving down through the worksheet.
While column D is blocked, click and hold the mouse button on the 'Insert' menu. Continuing to hold the mouse button down, highlight the word 'Column', then release the mouse button. A blank column appears between the Sex and Age columns. Age shifted to the right, becoming column E. (The same process enters rows into your database).
In cell D3 enter the word 'Sex Name'. Your data base should now look like this:
In cell F3 enter the word 'Year Born' and in cell G3 'Age in 2000'. You will have to block these last two entries, bold, center, and border them as you did before. You should now have:
3. Calculations:
You will now use various calculations and Excel functions to enter values for the synthetic variables.
In cell G4 enter '=E4+2'. Hit Return. You should see the age of the person in the first row of your database when the year is 2000. You should not see the formula that you entered. In cells with formulas, Excel displays the ANSWER to the formula in the cell. When you click on a cell with a formula Excel displays the formula in the edit bar.
Formula syntax deserves some explanation. The = symbol tells Excel that the cell contains a formula. In other words, the equals sign says: 'This cell equals...'. An asterisk indicates multiplication, a '/' indicates division, a '+' indicates addition, and a '-' subtraction. In cell G4 your formula tells Excel to add the Age of the person to 2 (for 2 years). Two years from now will be the year 2000.
You want this formula calculated for everyone in your database. Click on cell G4. Click on the Copy icon. Now block cells G5 through G13. Click on the Paste icon. This pastes the formula for age plus two to all rows in your database.
4. Cell References:
Click on cell G5. Look in the editing field and notice that the formula does not say '=E4+2', but rather says '=E5+2'. Excel did not copy the formula exactly as you originally specified. This property, fundamental to all spreadsheets, is called Relative versus Absolute cell references. Without some other commands (discussed below), Excel shifts the reference of a formula along with the movement of that formula among the cells of the worksheet. So, you entered '=E4+2' in cell G4, but when you copy the formula and then move down one cell, the formula is pasted as '=E5+2'. This means 'E4' is a Relative Cell Reference. Understanding this concept is very important in your future work with Excel.
Now, move to cell E1. Enter '1998' and hit return. You now can use Excel's calculation abilities and the idea of Absolute Cell Reference to calculate the year everyone in your spreadsheet was born.
In cell F4 enter '= E1 - E4'. This formula tells Excel to subtract the age of the first person in your spreadsheet from the current year. This gives that person's year of birth.
Now copy cell F4 and paste the formula to cells F5 through F13. What is wrong? The values are not correct at all! You should have something that looks like this:
What is the problem? Click on cell F5. Notice the formula now reads '=E5-E2'. Excel shifted the reference cells down one cell because you pasted the entire formula down one cell. Cell E1 in your formula is a Relative Cell Reference. You want Cell E1 to be an Absolute Cell Reference. This is very easy to fix.
Enter the formula again in cell F4 as follows '= $E$1 - E4'. Now copy this formula and paste it to cells F5 through F13. You now see the correct year of birth for everyone. The dollar signs anchor the cell reference. This tells Excel that no matter where you paste a function, the column and/or row with a preceding dollar sign always remains the same. This is an Absolute Cell Reference. You should now have something like:
Note that when you are editing a formula (e.g. adding the $ in the example above), you must use your mouse rather than the arrow keys to move. If you use the arrow keys, Excel moves you from one cell to the next. Only the mouse allows you to move WITHIN a cell.
5. Pre-assigned Functions:
Finally, let's look at some pre-assigned functions that Excel makes available. There are many. Some are statistical functions that calculate averages and standard deviations. Some are financial for calculating interest rates and so forth. Let's look at some logical functions.
Say you want to convert the values of 0 and 1 into words that more clearly specify the meaning of the Sex variable. You want the Sex Name variable to list the word 'Male' if the Sex variable is a zero, and 'Female' if it is a one. This is called a logical operation.
Move the pointer to cell D4 and click. Now select the Function Wizard icon located in the middle of the top row of the toolbar (the symbol on the icon looks like this: fx). A dialogue box appears, at the top of which is written 'Function Wizard - step 1 of 2'.
You should also see two windows. In the left window is listed 'Most Recently Used', 'All', 'Financial', etc. Towards the bottom of this list is written 'Logical'. Click once directly on this word.
In the right window is written 'And', 'False' etc. Click on the word 'If' in the right-hand window. Then click on the Next button.
There are three blank fields next to the phrases (in order): logical_test, value_if_true, value_if_false. Place the mouse pointer on top of the "logical_test" blank field. Type 'C4=1'. Do not hit return!! Position the mouse pointer on top of the "value_if_true" blank field and click once. Type 'Female'. Do not hit return!! Position the mouse pointer on top of the "value_if_false" blank field and click once. Type 'Male'. You are telling Excel the following command: If cell C4 equals a one, then enter the word Female. If C4 does not equal a one, enter the word Male.
Click FINISH and you've completed this section.
Copy and paste this function to the rest of the cells. Your spreadsheet should now look like this:
6. Finish the assignment by clicking on the Print icon.
This will print out your database. Make sure to save your work. You are now finished.