CT04_Table_Nelson_2009

Computer Technology Workshop (July 2009) Standard 04: Spreadsheet Lesson Plan

D. Nelson, July 2009
 * Analyzing National Education**


 * Purpose**: This lesson will help students to apply spreadsheet skills by analyzing an existing table. The focus of this lesson is numerical processing (standard 04) with specific attention to retrieving a spreadsheet, sorting data, calculating formulas, simple functions, inserting columns, inserting rows, and hiding data.


 * Grading**: There are 100 points available for this assignment.
 * Artifact: The student’s product will represent 80 points. The instructor will provide a table in electronic form. The students will copy that table into a worksheet and use spreadsheet features to analyze the data. The instructor will compare the student’s results with the key and also check the syntax of formulas and functions.
 * Assessment: There will also be a quiz worth 20 points. It will test how well the student understands the concepts illustrated during the lesson. The questions for the quiz are given with the assignment to reinforce their importance.
 * Bonus: The student may earn up to 10 extra points for attempting to find meaning in the numbers. This is a basic skills lesson but the power of spreadsheets is helping people see trends, make decisions, and analyze information. No points will be taken away for the attempt. The grading for this work is subjective (an opinion of an opinion).


 * Materials**: To complete this lesson plan the following resources need to be available.
 * Have this lesson available to students.
 * Have the source table available to students.
 * Make sure the students have access to MS-Excel. (If they are using a different spreadsheet program then substitute those features when they vary.)
 * Enter the quiz questions into your test system.


 * Introduction**: Assume that you are writing a report on National Education and need to analyze numerical data by state. Your assignment is to prepare a spreadsheet to analyze data extracted from the National Center for National Statistics. I will list below the concepts to learn and steps to perform.

Q: What do we call the horizontal sections of tables? A. Rows. Q: What do we call the vertical sections of tables? A. Columns. Q: What do we call the intersections in tables? A. Cells. Q: What identifies the horizontal sections in Excel? A. Numbers. Q: What identifies the vertical sections in Excel? A. Letters. Q: What identifies the intersections in Excel? A. LettersNumbers. Q: Identify the intersection of 3rd column and 4th row. A. C4.
 * Table Terms**: Numerical information is often arranged in a table (matrix, grid, etc.). The horizontal divisions are called rows (records, tuples, etc.). The vertical divisions are called columns (fields, attributes, etc.). The intersection of a row and a column is called a cell. Excel uses numbers for rows, letters for columns, and both for cells. For example, cell(A1) is the first cell because column(A) meets row(1). This means that each cell has its own address that can be used in formulas like a variable or constant in algebra.

Q: What is a shortcut for Select all? A. ctrl-a. Q: What is a shortcut for copy? A. ctrl-c. Q: What is a shortcut for cut? A. ctrl-x. Q: What is a shortcut for paste? A. ctrl-v. Q: What is a shortcut for switching between applications? A. alt-tab.
 * Insert Table**: Launch MS-Excel and note that a blank worksheet appears. Now use Excel to open an existing file called “NCES_Table.xml” as directed by your instructor. There are many ways of using that table in your spreadsheet. You should use the most direct method for this assignment. Select the table with the “Select all” shortcut (ctrl-a). Copy it in the clipboard with copy shortcut (ctlr-c). Go back to the blank worksheet with the switch shortcut (alt-tab). Select the top left cell (A1) to be the insertion point by clicking on it. Paste the table from the clipboard with the paste shortcut (ctrl-v). Go back to the table with the switch shortcut (alt-tab). We don’t need this table open any more so use Excel to close just that file. Your spreadsheet should now be visible. Use the “Save as” feature to store your spreadsheet in an appropriate place and with a suitable name. The instructor will discuss proper naming and storage.

Q. What does the “delete key” do to selected cells? A. Clear. Q. What does the “delete command” do to cells? A. Delete. Q. What operation brings up the options menu? A. Right-click.
 * Delete/Clear**: Most applications use the “delete key” to obliterate everything you have selected. Programs that work with tables (like Excel) use the “delete key” to only clear out the contents of cells, while leaving the cells intact. You must use a “delete command” to remove the actual cells, rows, or columns. It is important that you understand the difference between clearing content and deleting cells. Find the title row of the table (“Source: National Center…”). That should be the first row if you followed the instructions in the section above. To the left of that row should be the number 1 on a row button. Use the mouse to left-click on that button to select the entire row. Use the mouse to right-click on that button to bring up the options menu for your selection. The options in the middle include “clear contents” and “delete”. The “clear contents” option will do the same thing as hitting the “delete key”. The “delete” option is more powerful because it executes the “delete command”. Select the title row again and tap the “delete key” to verify that it only “clears” the contents of cells in that row. Bring up the options menu for that row and use the “delete” command to verify that it obliterates the entire row. At this point, the first row in your table should be the column headings, with “State” in cell(A1). The only thing in this worksheet should be the table so the “Select all” shortcut (ctrl-a) should select the table.

Q. What is another name for the A-Z sort? A. Ascending. Q. What is another name for the Z-A sort? A. Descending.
 * Sort Data**: Look at the table and note that the rows are sorted by State code. The column titles take up row(1), the Capital (DC) on row(9) is not a state, and Wyoming is on row (52). If you have 50 states, plus 1 title row, plus 1 capital, you should have 52 rows. Utah is ranked 45th alphabetically because it is on row(46) and there is one title row. To sort with Excel 2003, use the “Data” menu, select “Sort”, specify that your data range has a “header row”, for “sort by” select the correct column heading, and click OK. Sorts default to ascending order (A-Z) but you can specify descending order (Z-A). Complete the following 2 sorts and provide results.
 * Select the table and sort the data by the column “[04-05] Diplomas”. Which states gave out the most and least diplomas in the 2004-2005 school year? What was Utah ranked after this sort?
 * Select the table and sort the data by the column “[06-07] Exp/Student”. Which states spent the most and least per student in the 2006-2007 school year? What was Utah ranked after this sort?


 * Insert Columns**: Calculate the total number of students with diplomas, equivalencies, or other completion for both years. First we are going to make some room before the last 3 columns (H to J) that show expenses per student. Select these columns by dragging your curser across their column buttons. Bring up the options menu by right-clicking on one of these buttons. Select the “Insert” option to push those columns over and make empty columns in their place. Enter the title “” at the top of the first new column (H1). Enter the title “” at the top of the next new column (I1). Please note that the data for the school years are in alternating columns so we just need to add up every other column. Enter the formula “” into the second cell of the first new column (H2). The results of the calculation should appear in the cell even though you entered a formula. Copy this cell and paste it into the empty cells of the table for the two rows with new titles (H & I). Do not paste cells below the last row (52). (If a result shows “#VALUE!” then first clear the contents of cells that appear to have nothing in them.) Verify your results by checking some of the results.


 * Insert Rows**: Calculate the national totals for student in each column. Enter the title “Total” below the last state code (A53). Excel uses the Sum function to calculate totals. Select the cell to the right of the “Total” title (B53). Select the auto-sum tool (∑) or enter the formula “=SUM(B2:B52)” into the that cell (B53). Copy that cell and past that formula into other cells (C53 to I53) to calculate national totals for those columns. Check your formulas for accuracy.


 * Hide Columns**: Modify your spreadsheet so it does not display the columns that went into the “Finish HS” totals. There should be 6 columns you can hide (B to G). Select these columns by dragging your curser across their column buttons. Bring up the options menu by right-clicking on one of these buttons. Select the “Hide” option so they do not display. Your spreadsheet should now only display 6 columns of data (A, H, I, K, L, M).


 * Final Steps**: Your spreadsheet should still be sorted by the last column in ascending order. It should show evidence of following instructions and have accurate results. Note your sort results by circling the data on the spreadsheet. Save your work and turn in a final printed copy with your name on the back. You may also include your opinion about what the numbers suggest. Before the deadline, you must complete your work, turn it in, and take the quiz.

--Source Table


 * Source: National Center for Educational Statistics - Common Core of Data - Build a Table (http://nces.ed.gov/ccd/bat/) retrieved July 2009. ||
 * State || [04-05] Diplomas || [05-06] Diplomas || [04-05] HS Equivalency || [05-06] HS Equivalency || [04-05] Other HS Completers || [05-06] Other HS Completers || [04-05] Exp/Student || [05-06] Exp/Student || [06-07] Exp/Student ||
 * AK || 6,909 || 7,361 || 720 || 720 || 327 || 658 || 10,847 || 11,476 || 12,324 ||
 * AL || 37,453 || 37,918 || 2,149 || 2,149 || 2,537 || 6,978 || 7,073 || 7,683 || 8,398 ||
 * AR || 26,621 || 28,790 || 3,069 || 3,069 || 77 || 53 || 7,659 || 8,030 || 8,391 ||
 * AZ || 59,498 || 54,091 || 2,987 || 2,987 ||  ||   || 6,307 || 6,515 || 7,338 ||
 * CA || 355,217 || 343,515 || 7,623 || 7,623 ||  ||   || 7,905 || 8,301 || 8,952 ||
 * CO || 44,532 || 44,424 || 3,071 || 3,071 || 526 || 1,928 || 7,826 || 8,166 || 8,286 ||
 * CT || 35,515 || 36,222 || 469 || 469 || 45 ||  || 12,263 || 13,072 || 13,659 ||
 * DC || 2,781 ||  || 145 || 145 || 317 || 18 || 13,915 || 13,752 || 15,511 ||
 * DE || 6,934 || 7,275 || 173 || 173 || 168 ||  || 10,911 || 11,621 || 11,760 ||
 * FL || 133,318 || 134,686 || 13,286 || 13,286 || 8,550 || 9,671 || 7,215 || 7,812 || 8,567 ||
 * GA || 70,834 || 73,498 || 6,272 || 6,272 || 8,294 || 7,924 || 8,065 || 8,595 || 9,102 ||
 * HI || 10,813 || 10,922 || 812 || 812 || 201 || 206 || 8,997 || 9,876 || 11,060 ||
 * IA || 33,547 || 33,693 || 1,053 || 1,053 || 94 || 108 || 7,962 || 8,355 || 8,791 ||
 * ID || 15,768 || 16,096 || 1,438 || 1,438 || 109 || 84 || 6,319 || 6,469 || 6,648 ||
 * IL || 123,615 || 126,817 || 3,602 || 3,602 ||  ||   || 8,896 || 9,113 || 9,596 ||
 * IN || 55,444 || 57,920 || 3,223 || 3,223 || 1,577 ||  || 8,919 || 8,929 || 9,080 ||
 * KS || 30,355 || 29,818 || 1,488 || 1,488 ||  ||   || 7,926 || 8,640 || 9,243 ||
 * KY || 38,399 || 38,449 || 2,733 || 2,733 || 383 ||  || 7,132 || 7,668 || 7,940 ||
 * LA || 36,009 || 33,275 || 2,953 || 2,953 || 1,776 || 1,865 || 7,669 || 8,486 || 8,937 ||
 * MA || 59,665 || 61,272 || 2,706 || 2,706 || 988 || 850 || 11,642 || 12,087 || 12,857 ||
 * MD || 54,170 || 55,536 || 2,155 || 2,155 || 580 || 589 || 10,031 || 10,909 || 11,975 ||
 * ME || 13,077 || 12,950 || 915 || 915 || 330 || 66 || 10,342 || 10,841 || 11,644 ||
 * MI || 101,582 || 102,582 || 2,797 || 2,797 || 253 || 253 || 9,340 || 9,575 || 9,922 ||
 * MN || 58,391 || 58,898 || 1,203 || 1,203 ||  ||   || 8,718 || 9,159 || 9,589 ||
 * MO || 57,841 || 58,417 || 3,291 || 3,291 ||  ||   || 7,858 || 8,273 || 8,848 ||
 * MS || 23,523 || 23,848 || 2,792 || 2,792 || 1,657 || 1,825 || 6,548 || 7,173 || 7,459 ||
 * MT || 10,335 || 10,283 || 956 || 956 ||  ||   || 8,133 || 8,626 || 9,191 ||
 * NC || 75,010 || 76,710 || 3,913 || 3,913 || 1,653 ||  || 7,098 || 7,396 || 7,878 ||
 * ND || 7,555 || 7,192 || 394 || 394 ||  ||   || 8,279 || 8,728 || 8,671 ||
 * NE || 19,940 || 19,764 || 774 || 774 || 149 || 137 || 8,794 || 9,324 || 10,068 ||
 * NH || 13,775 || 13,988 || 492 || 492 || 72 || 74 || 9,771 || 10,396 || 11,037 ||
 * NJ || 86,502 || 90,049 || 2,357 || 2,357 ||  ||   || 14,117 || 14,954 || 16,163 ||
 * NM || 17,353 || 17,822 || 1,940 || 1,940 || 484 || 519 || 7,834 || 8,354 || 8,849 ||
 * NV || 15,740 || 16,455 || 1,478 || 1,478 || 2,496 || 2,177 || 6,804 || 7,177 || 7,806 ||
 * NY || 153,203 || 161,817 || 9,567 || 9,567 || 5,677 || 6,022 || 13,703 || 14,615 || 15,546 ||
 * OH || 116,702 || 117,356 || 2,786 || 2,786 ||  ||   || 9,330 || 9,692 || 9,940 ||
 * OK || 36,227 || 36,497 || 2,082 || 2,082 ||  ||   || 6,610 || 6,941 || 7,430 ||
 * OR || 32,602 || 32,394 || 3,300 || 3,300 || 4,266 ||  || 8,071 || 8,645 || 8,958 ||
 * PA || 124,758 ||  || 4,206 || 4,206 ||   ||   || 10,235 || 10,723 || 10,905 ||
 * RI || 9,881 || 10,108 || 491 || 491 || 22 || 4 || 11,667 || 12,609 || 13,453 ||
 * SC || 33,439 ||  || 2,050 || 2,050 || 2,753 ||   || 7,549 || 8,120 || 8,566 ||
 * SD || 8,585 || 8,589 || 412 || 412 ||  ||   || 7,464 || 7,775 || 8,064 ||
 * TN || 47,967 || 50,880 || 3,822 || 3,822 || 3,198 || 2,824 || 6,850 || 7,004 || 7,129 ||
 * TX || 239,717 || 240,485 || 10,665 || 10,665 ||  ||   || 7,246 || 7,480 || 7,850 ||
 * UT || 30,253 || 29,050 || 1,974 || 1,974 || 146 || 3,169 || 5,216 || 5,464 || 5,706 ||
 * VA || 69,597 || 69,597 || 5,298 || 5,298 || 4,419 || 4,419 || 8,886 || 9,452 || 10,214 ||
 * VT || 7,152 || 6,779 || 347 || 347 || 27 || 24 || 11,972 || 12,805 || 13,629 ||
 * WA || 61,094 || 60,213 || 3,878 || 3,878 || 119 || 151 || 7,717 || 7,984 || 8,524 ||
 * WI || 63,229 || 63,003 || 2,076 || 2,076 || 931 || 325 || 9,755 || 9,993 || 10,367 ||
 * WV || 17,137 || 16,763 || 1,222 || 1,222 ||  ||   || 9,024 || 9,440 || 9,727 ||
 * WY || 5,616 || 5,527 || 502 || 502 || 37 || 79 || 10,190 || 11,437 || 13,266 ||

--Final Table


 * State || [04-05] Finished HS |||| [05-06] Finished HS || [04-05] Exp/Student || [05-06] Exp/Student || [06-07] Exp/Student ||
 * UT || 32,373 || 34,193 ||  || 5,216 || 5,464 || 5,706 ||
 * ID || 17,315 || 17,618 ||  || 6,319 || 6,469 || 6,648 ||
 * TN || 54,987 || 57,526 ||  || 6,850 || 7,004 || 7,129 ||
 * AZ || 62,485 || 57,078 ||  || 6,307 || 6,515 || 7,338 ||
 * OK || 38,309 || 38,579 ||  || 6,610 || 6,941 || 7,430 ||
 * MS || 27,972 || 28,465 ||  || 6,548 || 7,173 || 7,459 ||
 * NV || 19,714 || 20,110 ||  || 6,804 || 7,177 || 7,806 ||
 * TX || 250,382 || 251,150 ||  || 7,246 || 7,480 || 7,850 ||
 * NC || 80,576 || 80,623 ||  || 7,098 || 7,396 || 7,878 ||
 * KY || 41,515 || 41,182 ||  || 7,132 || 7,668 || 7,940 ||
 * SD || 8,997 || 9,001 ||  || 7,464 || 7,775 || 8,064 ||
 * CO || 48,129 || 49,423 ||  || 7,826 || 8,166 || 8,286 ||
 * AR || 29,767 || 31,912 ||  || 7,659 || 8,030 || 8,391 ||
 * AL || 42,139 || 47,045 ||  || 7,073 || 7,683 || 8,398 ||
 * WA || 65,091 || 64,242 ||  || 7,717 || 7,984 || 8,524 ||
 * SC || 38,242 || 2,050 ||  || 7,549 || 8,120 || 8,566 ||
 * FL || 155,154 || 157,643 ||  || 7,215 || 7,812 || 8,567 ||
 * ND || 7,949 || 7,586 ||  || 8,279 || 8,728 || 8,671 ||
 * IA || 34,694 || 34,854 ||  || 7,962 || 8,355 || 8,791 ||
 * MO || 61,132 || 61,708 ||  || 7,858 || 8,273 || 8,848 ||
 * NM || 19,777 || 20,281 ||  || 7,834 || 8,354 || 8,849 ||
 * LA || 40,738 || 38,093 ||  || 7,669 || 8,486 || 8,937 ||
 * CA || 362,840 || 351,138 ||  || 7,905 || 8,301 || 8,952 ||
 * OR || 40,168 || 35,694 ||  || 8,071 || 8,645 || 8,958 ||
 * IN || 60,244 || 61,143 ||  || 8,919 || 8,929 || 9,080 ||
 * GA || 85,400 || 87,694 ||  || 8,065 || 8,595 || 9,102 ||
 * MT || 11,291 || 11,239 ||  || 8,133 || 8,626 || 9,191 ||
 * KS || 31,843 || 31,306 ||  || 7,926 || 8,640 || 9,243 ||
 * MN || 59,594 || 60,101 ||  || 8,718 || 9,159 || 9,589 ||
 * IL || 127,217 || 130,419 ||  || 8,896 || 9,113 || 9,596 ||
 * WV || 18,359 || 17,985 ||  || 9,024 || 9,440 || 9,727 ||
 * MI || 104,632 || 105,632 ||  || 9,340 || 9,575 || 9,922 ||
 * OH || 119,488 || 120,142 ||  || 9,330 || 9,692 || 9,940 ||
 * NE || 20,863 || 20,675 ||  || 8,794 || 9,324 || 10,068 ||
 * VA || 79,314 || 79,314 ||  || 8,886 || 9,452 || 10,214 ||
 * WI || 66,236 || 65,404 ||  || 9,755 || 9,993 || 10,367 ||
 * PA || 128,964 || 4,206 ||  || 10,235 || 10,723 || 10,905 ||
 * NH || 14,339 || 14,554 ||  || 9,771 || 10,396 || 11,037 ||
 * HI || 11,826 || 11,940 ||  || 8,997 || 9,876 || 11,060 ||
 * ME || 14,322 || 13,931 ||  || 10,342 || 10,841 || 11,644 ||
 * DE || 7,275 || 7,448 ||  || 10,911 || 11,621 || 11,760 ||
 * MD || 56,905 || 58,280 ||  || 10,031 || 10,909 || 11,975 ||
 * AK || 7,956 || 8,739 ||  || 10,847 || 11,476 || 12,324 ||
 * MA || 63,359 || 64,828 ||  || 11,642 || 12,087 || 12,857 ||
 * WY || 6,155 || 6,108 ||  || 10,190 || 11,437 || 13,266 ||
 * RI || 10,394 || 10,603 ||  || 11,667 || 12,609 || 13,453 ||
 * VT || 7,526 || 7,150 ||  || 11,972 || 12,805 || 13,629 ||
 * CT || 36,029 || 36,691 ||  || 12,263 || 13,072 || 13,659 ||
 * DC || 3,243 || 163 ||  || 13,915 || 13,752 || 15,511 ||
 * NY || 168,447 || 177,406 ||  || 13,703 || 14,615 || 15,546 ||
 * NJ || 88,859 || 92,406 ||  || 14,117 || 14,954 || 16,163 ||
 * Total || 2,990,525 || 2,842,701 ||  ||   ||   ||   ||

--The End