Words: 423
You are my teaching assistant for accounting 202 and Vive asked you to determine and analyze semester grades. A. Use Excel functions, formulas and tools whenever possible. B. I want a template for the Grade Book and Grade Scale worksheets that I can reuse for all future classes and semesters by only changing the student’s personal data and grades. No more than 100 students can be enrolled in any section in any one semester. I want formulas to be easy to review and understand at a glance.
All page numbers refer to the Excel 2010 Bible. 1 . Review the feedback from assignment 8, open your assignment 8 Grade Book, and make all corrections and changes necessary. 2. Students are assigned to exam rooms based on the first letter of their last name. Class section does not impact room assignments. Download the “Communicant” worksheet, and copy it to the end of the Blackboard Grade Book workbook. Apply instructions 3 -7 to the Communicant worksheet 3.
You need to count the number of students with last names beginning with the same letter. For example, count all students with last names beginning with A, then count last names beginning with B, etc. Simply adding a Subtotal function to the worksheet will result in completely useless results since the data is not categorized yet. * Review Text Category Functions on page 932 * In the Alpha column, use a function to pull Just the first letter of text from each record in the Student Name column .
Use the Subtotal function to count the total number of records within each letter. * Add subtotals to the First Letter column 5. Read Chi. 13 pages 299-307. 6. In the “UCM Sum” column, calculate the cumulative sum (running total) of the subtotal count. You need to write a nested formula that combines IF, Sum, and SINISTER functions. * If your First Letter column is a number, you want to add the count * Do not include the row for Grand Count * Your count must be a cumulative sum 7.
Assign students to exam rooms according to the first letter of their last name. Do NOT do this manually. You do NOT need to display or identify which specific students are assigned to each room, Just the first letter of last names assigned to rooms. * Use conditional formatting * It is K to manually enter the cell values for room capacities in the conditional formatting dialog box, since room capacities do not change * Indicate room assignments by different colors 8. Save your workbook and submit in Blackboard.