Use of Excel Spreadsheets in Computing Grades

Use of Excel Spreadsheets in Computing Grades

• 1.
  Use of Excel Spreadsheetsin Computing Grades PREPARED BY: ELLI MAY A. CAÑAS
• 2.
  OBJECTIVES: After the presentation,we shall be able to: ◦Know the importance of excel in computing grades ◦Recognize some functions of each formulas in excel ◦Learn the application of the formulas ◦Distinguish some statistical functions and weighted averages ◦Identify some conditional calculations and Look Ups
• 3.
  Why do weuse Excel in computing grades? Ease of inputting grades Use of formulas for automatic calculation of: -point totals -dropping lowest score from total -averages -final letter grade conversion Easy import into other programs (Web, etc.)
• 4.
  What makes Excelso powerful? Its ability to perform predetermined calculations, automatically. This is accomplished by developing formulas that do the work for us.
• 5.
  It is importantto understand how formulas work. All formulas begin with “=” Excel uses numbered rows and lettered columns for reference in formulas.
• 6.
  FUNCTION WHAT ITDOES SUM(range ) Adds a range of cells AVERAGE( range) Calculates the average of a range of cells MAX(range ) Returns the maximum value of a range MIN(range ) Returns the minimum value of a range COUNT(range ) Counts the number of values (cells containing numbers) in a range COUNTA(range ) Counts the number of non-blank cells within a range COUNTBLANK(range ) Counts the number of blank cells within a range COUNTIF(range, "string" ) Counts the number of cells that are the same as a search string STDEV(range) Calculate the standard deviation of a sample SMALL(range, n) Returns the nth smallest number in the specified range The table below lists several of the more common and helpful statistical functions in Microsoft Excel.
• 7.
  Key Notes: The SUM() and AVERAGE( ) functions are most frequently used. The COUNT( ) function ignores blank cells and does not count cells that contain text labels. To count cells containing text, you must use the COUNTA( ) function.
• 8.
  Statistical Functions andWeighted Averages A B C D E F 1 Student Exam 1 Exam 2 Exam 3 Final Average 2 Davis 88 73 85 78 =AVERAGE(B2:E2) Excel has numerous functions to summarize data. The AVERAGE( ) function calculates the average of a group of numbers. For example in cell F2 below, =AVERAGE(B2:E2) calculates the average for the student in row 2. This AVERAGE( ) function assumes that all test scores are weighted equally. It also ignores blank cells; it does not treat them as 0.
• 9.
  Weighted Averages There aremany methods to create a weighted average using formulas. For example, if you want to count each test 20% and the final 40%, use the following formula: =(B3*.20)+(C3*.20)+(D3*.20)+(E3*.40) You can easily modify this formula to accommodate a different grading scheme. A B C D E F 1 Student Exam 1 Exam 2 Exam 3 Final Average 2 Davis 88 73 85 78 =AVERAGE(B2:E2) 3 Johnson 52 71 65 67 =(B3*.20)+(C3*.20)+(D3*.20)+(E3*.40)
• 10.
  Dropping Low Scores TheMIN( ) function returns the smallest value in a range of cells. The formula in cell F4 above adds all test scores for Smith then subtracts the minimum test score and divides the result by 3. This formula also assumes that all scores are weighted equally. However, you can calculate a weighted average and drop a low score. A B C D E F 1 Student Exam 1 Exam 2 Exam 3 Final Average 2 Davis 88 73 85 78 =AVERAGE(B2:E2) 3 Johnson 52 71 65 67 =(B3*.20)+(C3*.20)+(D3*.20)+(E3*.40) 4 Smith 91 85 96 89 =(SUM(B4:E4)-MIN(B4:E4))/3
• 11.
  Excel’s SMALL( )function is also helpful in determining the second or third lowest score. The syntax of the SMALL( ) function is =SMALL(data,n) where data is the range of cells containing the data and n represents the smallest number in the data set. For example to find the second lowest test score for Connor in the previous example use SMALL(B6:E6,2); to determine the third lowest score use SMALL(B6:E6,3). If you need to calculate an average that drops the two lowest scores use MIN( ) to subtract the lowest and use SMALL( ) to subtract the second lowest. The formula in cell F6 in the table above drops the two lowest exam scores and calculates the average with the final and the remaining Exam score counting 50% each. A B C D E F 1 Student Exam 1 Exam 2 Exam 3 Final Average 2 Davis 88 73 85 78 =AVERAGE(B2:E2) 3 Johnson 52 71 65 67 =(B3*.20)+(C3*.20)+(D3*.20)+(E3*.40) 4 Smith 91 85 96 89 =(SUM(B4:E4)-MIN(B4:E4))/3 5 Jones 78 82 67 75 =(SUM(B5:D5)-MIN(B5:D5))/2*.60 + (E5*.40) 6 Connor 88 91 83 75 =(SUM(B6:D6)-MIN(B6:D6)- SMALL(B6:D6,2))*.50 + (E6*.50)
• 12.
  Conditional Calculations andLookups Excel has several logical functions that let you test cells and perform different operations depending on their contents. IF( ) function The IF() function enables you to specify two different calculations based on a certain condition. The syntax of the IF( ) function is =IF(condition, calculation if condition is true, calculation if condition is false)
• 13.
  Conditional Calculations andLookups AND( ) and OR( ) functions To specify multiple conditions within an IF( ) function, use Excel's AND( ) and OR( ) functions. The syntax of these functions is: =AND(condition 1, condition 2, ...condition n) = OR(condition 1, condition 2, ...condition n) AND( ) returns the value of TRUE if all its conditions are true, and returns FALSE otherwise. OR( ) returns TRUE if at least one of the specified conditions is true.
• 14.
  ISBLANK( ) function Excel’sISBLANK( ) function tests whether a certain cell is blank,. This function returns TRUE if the cell is blank and FALSE if it’s not. Conditional Calculations and Lookups
• 15.
  Lookup Tables The IF() function is very useful, but it is limited to either TRUE or FALSE outcomes. In many worksheets, you might want to create a function that handles multiple outcomes. Excel's VLOOKUP( ) function is ideally suited for this sort of calculation. With the VLOOKUP( ) function (short for vertical lookup) you can specify lookup values for different outcomes. For example, if you have a list of numeric averages in a worksheet, you can create a formula that assigns letter grades based on a student's numeric score (e.g. a score of 76 would be a C). To use a VLOOKUP( ) function, you must first create a lookup table with a range of values. This lookup table is similar in concept to a tax table. When using a tax table, find your income in the first column and then read across to the column that applies to you, single, married etc.
• 16.
  Using Excel inComputing Grades
• 17.
  -END-