EXCEL-lence: Strategies for using Microsoft EXCEL
On a sheet of paper, ask students to calculate the average of the following list of numbers: 12.3, 23.7, 35.8, 41, 67.13, 68.3, 74.759, 82.1, 89, 93.7, 99.99 (Answer: 62.525363)
Ask a student volunteer to describe the steps that one must take to calculate an average (or mean) from a series of numbers. Record student responses on a large instructional writing space. (Student responses will vary but should indicate comprehension by statements that include finding the sum of all numbers in a set or series of data, counting the number of values, data points, in the set then dividing the sum by the number of values.)
Basic strategies for analyzing small data sets-Formulas and Charts
Copy the series of numbers into a column in EXCEL with each data point in a separate cell in a vertical column. In EXCEL, the column default label is alphabetical and the row default is numerical. The first column is labelled "A" and the first row is labelled "1". After entering the series, type "Average"
in column A, row 15.
Move the cursor to column B, row 15. Click the EDIT FORMULA
button at the top of the window ("=").
Direct students to choose the AVERAGE
function. Provide students with a FOCUS FOR MEDIA INTERACTION by asking students to identify the location of the data to be analyzed. (Students should state the data is located in column A from rows 1 through 11. Explain to students that EXCEL uses the colon to establish the start and stop points as it reads data in a series.)
Provide students with a FOCUS FOR MEDIA INTERACTION by asking them to verify that the average they calculated earlier is the same as the average calculated by the software. Point out to students that the data values appears parenthetically next to the Number1
field as a way of checking while constructing the formula to be used.
Ask students to graph the data using the "Chart Wizard" by clicking on the
Highlight the data points from A1 to A11 (A1:A11) and choose line graph. Change the way the graph looks by formatting the PLOT AREA
and the CHART AREA
using pre-set fill effects (example shown here is Desert) or colors.
Provide students with a FOCUS FOR MEDIA INTERACTION by asking students to perform the following analyses on the same set of data in column B: remind students to indicate the range of data points between A1 and A11 as A1:A11
- SUM-Find the sum of the values in the set.
- COUNT-Count the number of data points that are greater than 80 (>80).
- MEDIAN-Find the middle value within the set. (The MEDIAN function can be accessed in the drop down menu under "More functions".)
- MAX-Identify the largest number in the set.
| Advanced strategies for data analysis-Sorting and Table Organization
Lead students in a discussion in challenges that might arise when trying to analyze data sets that contain more than eleven data points. (Student responses will vary but should indicate the challenges of data entry-possibilities of human error are introduced the more a person manipulates values.)
Provide students with a FOCUS FOR MEDIA INTERACTION by asking them to review the Novell test data
document being projected on overhead; the data was collected from the Novell training website. Students could log onto the site if an overhead of the document is not available. Ask the students to identify challenges that may be expected in the analysis of this data. (Student responses will vary but should indicate that if test data is to be analyzed, general organization is the most important characteristic of the information. Since most people want to compare test data by score, a better way to analyze the information might be to sort the data into
Copy the data from the table into EXCEL. Use the Crib sheet document
to find keyboard shortcuts to speed up the process of copying the information (From the website, click on Ctl + A or highlight the table text using the mouse to copy and paste into a new document. Make sure to also copy the URL for proper citation and later reference). Paste the data into a new EXCEL worksheet. (Teachers can use the prepared Culminating Activity file
- data is pasted into an EXCEL file).
In EXCEL, highlight the first eight columns of data (Column A through H) by dragging the mouse over the column labels at the top of the screen while holding down the left mouse button. Provide students with a FOCUS FOR MEDIA INTERACTION by asking them to identify the cut score and time for the "CNI Service and Support" test. (Cut score: 686; time: 90 minutes)
Provide students with a FOCUS FOR MEDIA INTERACTION by asking them to consider how the information could be organized to give information about the "type of test" being administered. (Student responses will vary but make sure to point out that there are two types of test-adaptive (A) and traditional (P). Indicate to students that while raw data is similar to what is shown here is easier to process if the key information is aggregated or lumped together to form fewer points of distinction.)
The ideal method used to showcase data is called a pivot table. Log onto the census data websites (http://www.census.gov/epcd/mwb97/us/us.html
) or (http://factfinder.census.gov/servlet/QTTable?
) to identify the characteristics of good data organization. Point out to students that both tables are organized to summarize large sets of data by categories of interest. Advanced students can use the "pivot wizard" to create various data displays. Students whose skills are still emerging, can practice analysis functions conducted in earlier parts of the lesson.
Create an electronic financial journal. Use EXCEL software to create a spreadsheet of spending data for yourself. Analyze your average spending for a week. Identify the most amount of money you spent for a lunch meal or the least amount of money you spent on entertainment expenses in a month.
Log onto the Academic Techology website of Drew University to practice basic chart building or progress toward more advanced techniques useful for presentations.