Excel Activity 1: Summation, Subtraction and Division
Purpose: The purpose of this exercise is to become familiar with some of the basic functions used in Excel, such as summation, subtraction, and division.
Problem: A high school math teacher needs to calculate the quiz grades for her class of 30 students. There were four quizzes during the quarter, but she will be dropping the lowest score before calculating the final grade. In this exercise, you will calculate the total scores on the four quizzes, locate the lowest score, re-compute the quiz grade with the lowest score deleted, and then convert that score into a percentage.
Calculate Summation
Locate Minimum Values
Re-Calculate Summation After Subtracting Lowest Score
Convert Score into a Percentage: Division and Percentage
SUMMATION
(Back to Top) (Back to Main Page)Place the cursor in cell F2 under the label TOTAL. Select Insert from the menu bar and scroll down to Function. A dialog box titled Insert Function will appear displaying an alphabetical list of functions to choose from. Under Or select a category select Math & Trig. This will change the available functions in the list so that only functions pertaining to mathematical and trigonometric procedures will be displayed. Scroll down to the function named SUM and highlight it. This is the function that calculates the sum total of the selected data. Click OK and a new dialog box titled Function Arguments will appear. In the field labeled Number1 specify the range of cells containing the values of the variable you want to sum. The shorthand method for specifying a range is the label for first cell containing data, followed by a colon, then the label for the last cell containing data (e.g., A2:A23). This notation says, "I'm specifying a range of values that begins with cell A2 and ends with cell A23, and I want to include the values of all the cells in between." In the case of the current variable of interest, the cell range to specify is cell B2 through cell E2 (i.e., B2:E2). The field labeled Number2 should be left blank. Once a valid range is specified the results of the function will be displayed at the bottom of the dialog box, in this case 21. A small box with an exclamation point within a yellow diamond will appear. This indicates that Excel thinks you have made an error. Click on the box. The first entry will say "Formula Omits Adjacent Cells." Excel thinks you meant to include column one, which also has a number in it (the ID). Click on "Ignore Error," because you do not want to include the ID number in the sum.
To complete the sum for all students, click on the sum you calculated in cell F2. Now go to the top menu bar, and click on Edit. A drop-down menu appears. Go down the menu and click on Copy. The F2 cell will become surrounded by moving dashes. This indicates that cell F2 is active, and it's contents will be copied. Move your cursor to cell F3 and left-click on it. Now, with the left mouse button held down, drag your cursor from cell F3 to F31. These cells become highlighted. Press the enter key. The cells will become filled with the sums for each of the other students in the class. The small error box once again appears. Click on it (while all of the cells are highlighted), and then click Ignore Error to make the error box go away (because you do not want to include the ID number in the sums).
MINIMUM VALUES
(Back to Top) (Back to Main Page)We now will instruct Excel to find the lowest quiz score of the four values, and then place it in a cell. Place the cursor in cell G2 under the label LOWEST. Select Insert from the menu bar and scroll down to Function. Change the category to Statistical and locate the function named MIN and highlight it. This is the function that calculates the minimum value of a selected series of data. Click OK to open the Function Arguments dialog box. Under the label Number1 enter the same range of cells as you did for the SUM function (i.e., B2:E2). The field labeled Number2 should be left blank. Once a valid range is specified the results of the function will be displayed at the bottom of the dialog box, in this case 1.
To complete the minimum calculation for all students, click on the minimum you calculated in cell G2. Now go to the top menu bar, and click on Edit. A drop-down menu appears. Go down the menu and click on Copy. The G2 cell will become surrounded by moving dashes. This indicates that cell G2 is active, and it's contents will be copied. Move your cursor to cell G3 and left-click on it. Now, with the left mouse button held down, drag your cursor from cell G3 to G31. These cells become highlighted. Press the enter key. The cells will become filled with the minimums for each of the other students in the class.
SUBTRACTION
(Back to Top) (Back to Main Page)Now we must calculate the new total quiz score after subtracting out the lowest grade. Place the cursor in cell H2 under the label TOTAL2. Type =F2-G2 directly into the cell and press enter. The result of the formula (20) should be displayed. The equal sign at the beginning of the cell instructs Excel that you want it to treat the statement as a formula. This formula states, "subtract the value of cell G2 from the value of cell F2." In other words, this function is subtracting out the lowest score that we calculated with the MIN function from the total score based on the four quizzes.
To complete the subtraction calculation for all students, click on the subtraction you calculated in cell H2. Now go to the top menu bar, and click on Edit. A drop-down menu appears. Go down the menu and click on Copy. The H2 cell will become surrounded by moving dashes. This indicates that cell H2 is active, and it's contents will be copied. Move your cursor to cell H3 and left-click on it. Now, with the left mouse button held down, drag your cursor from cell H3 to H31. These cells become highlighted. Press the enter key. The cells will become filled with the subtractions for each of the other students in the class.
DIVISION & PERCENTAGE
(Back to Top) (Back to Main Page)The final step is to calculate the percent correct of the three highest quiz scores. Place the cursor in cell I2 under the label PERCENT. Type =H2/21 directly into the cell and press enter. Doing this instructs Excel to divide the value in cell H2 by the number 21, which is the total number of possible points (3 quizzes at 7 points each = 21 points). The number in cell H2 is the percent the student got correct on the total of the three quizzes (.952381). However, the number is not in percent form. To fix this, be sure the cursor is in cell I2 and go to Format on the menu bar and select Cells. On the Number tab, select Percentage from the Category list on the left. Then change the Decimal places to 0 and press OK. The value in cell I2 should now be displayed as a percentage rounded to the nearest whole number (95%).
To complete the percentage calculation for all students, click on the percentage you calculated in cell I2. Now go to the top menu bar, and click on Edit. A drop-down menu appears. Go down the menu and click on Copy. The I2 cell will become surrounded by moving dashes. This indicates that cell I2 is active, and it's contents will be copied. Move your cursor to cell I3 and left-click on it. Now, with the left mouse button held down, drag your cursor from cell I3 to I31. These cells become highlighted. Press the enter key. The cells will become filled with the subtractions for each of the other students in the class.