Accounting 309 Excel Assignment 9
Accounting 309
Excel Assignment 9
You are my teaching assistant for accounting 202 and I’ve 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.
c. 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 “ExamRoomCount” worksheet, and copy it to the end of the Blackboard Grade Book workbook.
Apply instructions 3 -7 to the ExamRoomCount 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
4. Use the Subtotal function to count the total number of records within each letter. * Add subtotals to the First Letter column
5. Read Ch. 13 pages 299-307.
6. In the “Cum Sum” column, calculate the cumulative sum (running total) of the subtotal count. You need to write a nested formula that combines IF, Sum, and ISNUMBER functions. * If your First Letter column is a number, you want to add the count *
Excel Assignment 9
You are my teaching assistant for accounting 202 and I’ve 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.
c. 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 “ExamRoomCount” worksheet, and copy it to the end of the Blackboard Grade Book workbook.
Apply instructions 3 -7 to the ExamRoomCount 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
4. Use the Subtotal function to count the total number of records within each letter. * Add subtotals to the First Letter column
5. Read Ch. 13 pages 299-307.
6. In the “Cum Sum” column, calculate the cumulative sum (running total) of the subtotal count. You need to write a nested formula that combines IF, Sum, and ISNUMBER functions. * If your First Letter column is a number, you want to add the count *