Database Operations, Graphing Data, and Embedding a Spreadsheet in a Word Processing Document

Database Operations, Graphing Data, and Embedding a Spreadsheet in a Word Processing Document

Written by Hayden Porter and David Bykowski for the 1999 Johnson/Mellon Introductory Workshop.

Overview: It can be helpful to see the final grade distribution for the class as you decide how to assign grades that fall near the boundaries. You can use Excel's database functions to help you count up the number of grades in the A range, the B range, and so on. These same database techniques are directly applicable for analyzing the results of surveys or data acquired from the Internet or other on-line sources. The examples in the following section make use of a built-in function named DCOUNT. Use Help>Search for Help on... and type in the function name to learn more about DCOUNT. There are many other database functions in Excel that provide useful database capabilities. For example, DAVERAGE will compute average values for selected database items. The Excel Help System documents all of these special functions.
Objectives: This tutorial will show you how to use one of Excel’s database functions, how to produce charts to view data, and how to embed a spreadsheet into a Word document. You will experiment with changing grades and seeing how the database information and the charts are automatically changed as a result of your data modifications.
Conventions used: In the following, actions you are to perform appear within a shaded background. Parenthetical remarks associated with actions are indented within the shaded areas. Where menu operations are to be performed, they are indicated as menu>selection. For example, File>Save would mean select the Save option under the File menu. This tutorial will take approximately 20 minutes.
Using Database and Graphing Features of Excel
DCOUNT is a function that requires three arguments. The first argument is a range of cells containing both the data to be analyzed along with its field names. In the case of the grades spreadsheet you completed in the previous tutorial, you might take the range A2..P17. That range includes both the names of the columns and all the grades. Alternatively, if you only wanted to analyze the final grade distribution, you could take the database range to be P2..P17. Cell P2 contains the field name Final. As the second argument, you must define the range of field names for which the analysis will be performed. For analyzing only the final grades the field name is Final and is contained in cell P2. For the third argument, you must specify a range containing the criteria to be used for counting. For example, to count the number of A's in the Final column the grade must be greater than or equal to 90.
In Excel a criteria is entered by placing a condition such as >=90 in a cell. Criteria may be put together using AND or OR operations. For example to define a criteria for a B letter grade we require that the grade be <90 and >=80. Criteria ranges in the same row are treated as AND in evaluation while criteria in the same column range are treated as OR in evaluation.

• 
  If Excel isn’t running, start Excel.
  
• 
  If Netscape isn’t running, start Netscape.
  
• 
  Navigate to http://www.furman.edu/~porter/cadre/tutoriallist2.html
  
• 
  Follow the instructions for downloading grades2.xls
  
• 
  Minimize Netscape.
  
• 
  Open the file grades2.xls in the mellon folder on your hard drive.
  
• 
  Observe that cells B25..B29 show letter grades and cells C25..C29 contain the number of final grades that fall into each letter grade.
  
• 
  Double click the entry for cell C29 to see the formula there. It has the following formula
  

=DCOUNT(P2:P17,P2,C22:C23)

As noted above, the range P2..P17 is the database range, P2 is the field name, and C22:C23 is the criteria range. Looking at the cells in the range C22:C23 you find the entries

Final

>=90

The field name must appear in the cell above the selection criteria. In this case, Excel compares each grade in the Final column to see if it is greater than or equal to 90. DCOUNT increments the value in cell C25 by one each time a grade satisfies the criteria. After all values are tested, C25 contains the number of As. Examine the formulas in the other cells C26 to C29 and see how those criteria are defined. Note that the criteria for C26 include two adjacent columns and rows. Consequently the count is incremented only if the grade is less than 90 AND greater than or equal to 80.

• 
  Change the exam grades and/or other grades and see how the final average is changed.
  

Note that the count of the number of grades in each grade range reflects your changes.
In the following, you'll set up a chart to display the final grades. You'll create a bar chart showing the number of grades in each grade range. Before proceeding, however, use Help to learn about Chart Wizard. The Chart Wizard will guide you through creating a Chart or graph.

• 
  Select Help>Examples and Demos> Creating a Chart>Creating a Chart with Chart Wizard.
  

When you've finished reading about the Chart Wizard continue with the next step.

• 
  Highlight the range from B25 to C29 and Select Insert>Chart>On This Sheet.
  

A small + cursor appears on the screen.

• 
  Move the cursor to cell E25 and click.
  

A dialog box appears.

• 
  Click Next>.
  

A palette of graph types appears.

• 
  Click the 3D Bar chart icon and click Next.
  
• 
  In the dialogue box presented click on chart 4 and then click Finish.
  

A simple chart appears on the screen. Note that each row of the chart shows the letter grade. The length of the bar is determined by the number with that letter grade.

• 
  Double click inside the frame containing the chart.
  

You are selecting the chart for editing. Note the handles that appear on the chart frame.

• 
  Click on the small box containing the name legend.
  

Handles appear along its borders.

• 
  Hold down the right mouse button, and drag down to clear.
  

The legend is removed from the chart.

• 
  Move the mouse cursor into the white area surrounding the bars of the chart but inside the frame.
  
• 
  Press the right mouse button and select Insert Titles.
  
• 
  In the dialogue box presented, click Chart Title and click OK. Now click the title to select it.
  
• 
  Type Grade Distribution and press enter.
  

The title Grade Distribution appears on your chart.

• 
  You may experiment with other variations on this chart by producing labels, etc. See the Excel Help system for more details.
  

In completing this tutorial, you'll see how use the object linking and embedding (OLE) features of the Microsoft Office. You can embed your Excel spreadsheet inside your syllabus document and subsequently access the spreadsheet from your syllabus.

• 
  Save the spreadsheet as finalgrd.xls on your hard drive.
  
• 
  Close Excel and Open Word.
  
• 
  Open your final copy of syllabus.doc on your C drive.
  
• 
  Place the Insertion Point at the beginning of a blank line above your lecture table.
  
• 
  Select Insert>Object>Create from File. Highlight finalgrd.xls and click OK.
  
• 
  The spreadsheet is now embedded in your word processing document.
  
• 
  Double click the spreadsheet.
  

You can now alter the spreadsheet data and view results from within your word document.
If you need to distribute materials to students that include both Word document and Excel documents, you may embed an Excel document inside the Word document or alternatively, you can embed a Word document into a spreadsheet. For example, you can include extensive documentation about problems and/or exercises that you want students to perform using a set of spreadsheet data. As you will see soon, you may also embed a Powerpoint presentation into your Word or Excel document.

Project:
Many students are interested in learning how their final course grade will depend on their final exam score. You can easily set up a spreadsheet to compute, for each student, final course grades for a set of final exam grades. You might, for example, enter four exam scores 75, 85, 95, and 100 in the cells Q2 to T2 of the example spreadsheet. Copy the formula for computing the grade in P3 into Q3 and modify the formula in cell Q3 to reference cell Q2 for the exam score. Once you are have this formula correctly modified, copy it down column P. Perform this same process for each of the other columns Q, R, and S. You can now show your students how well they will do in your course for a given final exam grade. I often print out and distribute to each student the appropriate row of my spreadsheet to help him/her understand how he/she is doing in my class. Going one step beyond this, it is possible to let a student access his or her grades interactively from the Web (using a password you define). If you are interested in trying this, let us know and we’ll try to help you set this up.