Assignment 3: Excel – Advanced Topics
Spreadsheets are useful for many other things besides financial tasks, such as progress reports and for keeping track of descriptive data. For this assignment, you will create at least one table and an accompanying graphic visual that will help you write functions and learn simple programming techniques using advanced Excel features.
- Create a file in Excel that describes a grading system for a class.
- One Excel workbook file with 3 separate, appropriately labeled worksheets.
- Tables, a graph, and a list of descriptive statistics.
- For this exercise, the correct values in each cell are important and will be graded.
- The graph will be graded on appropriate content, appropriate axis scaling, and clear labeling.
You should form a table on a worksheet titled “class list” that includes the names and test scores of your students. You have 7 students in your class, their names are: Allen, Borlin, Catlin, Dorsey, Eugene, Finneran, and Greco. Their scores on the first 3 tests are as follows:
Test 1 Test 2 Test 3 Allen Test 89 Test 78 Test 89 Borlin Test 67 Test 56 Test 66 Catlin Test 78 Test 76 Test 76 Dorsey Test 56 Test 34 Test 45 Eugene Test 26 Test 100 Test 99 Finerran Test 99 Test 98 Test 97 Greco Test 78 Test 87 Test 88
- Using an Excel function, show each student’s average in an additional column labeled “Average”
- Using an Excel function, show each student’s rounded average in an additional column labeled “Rounded Average”
- If a student’s rounded average is above “95”, he/she has received “honors” in the class. In an additional column titled “Honors”, insert a function that will return the word “Yes” if they have received honors, otherwise would return the word “No”
- If a student’s rounded average is 90 or greater, they receive an “A”. Between 80 and 90 is a “B”, between 70 and 80 is a “C”, between 60 and 70 is a “D”, and lower than 60 is an “F”. Somewhere on your sheet, enter this information in cells. Create an additional column titled “Grade” and insert a nested IF function that returns the appropriate grade for each student. Use an absolute cell references in your nested IF function to indicate cut-off points between grades. Hint: You will need to place the “cut-off grade” values in cells somewhere on your worksheet…
- Below your table, create a graph showing the students’ rounded averages. Be sure to include appropriate labeling and spacing, so that the graph is non-repetitive and the scale is appropriate. Hint: A score of 100 is the highest possible…
- Insert a new worksheet. Use the Goal Seek feature to find the value that Eugene needed on Test 1 in order to earn honors for the course. Show your work by displaying an updated table. Title the worksheet “Eugene’s dream.” Hint: Do not worry about rounding Eugene’s new “score” for Test 1. If done properly, the rest of your table should update accordingly.
- From the data on your “class list” worksheet, provide the Descriptive Statistics of your students’ rounded averages. For the output, create a new worksheet and choose to display “summary statistics”. Rename this worksheet “Descriptive Statistics”.
- Offer a chance to practice some advance functions in Excel
- Further practice of graphing and tabularizing information using Excel
- For all functions, create it once and use a fill-in procedure to complete the sheet for all students.
- For the “Grade” column, be sure to remember to use an absolute cell reference when determining comparison values.