Share
Explore BrainMass

Analyzing a spreadsheet

Alternate Grade Book: The following figure displays a possible layout of your homework one. Open the partially completed version of this worksheet given to you, and then complete the worksheet as follows:
a. The test average is computed by dropping the student's lowest score, then giving equal weight to the three remaining tests. Steve Weinstein's test average, for example, is computed by dropping the 70 on test 1, then taking the average of 80, 90, and 100, his grades for tests 2, 3, and 4. You will need to use the SUM, MIN and COUNT functions to implement this requirement.
b. Students are required to complete a designated number of homework assignments (12 in the figure), then receive a bonus or penalty for every additional or missing homework assignment. Andrea Carrion completed 9 homework assignments, rather than 12, and thus has a 6-point penalty (2 points per each missing assignment). Steve Weinstein, however, receives a 3-point bonus (1 point for each extra assignment). The bonus or penalty is added to the test average to determine the semester average.
c. The grade for the course is based on the semester average and table of grading criteria according to an HLOOKUP function within the worksheet. Please use the correct formulas for ALL of your calculations.
d. Format the worksheet in an attractive manner. You do not have to duplicate our formatting exactly, but you are to use conditional formatting to display all failing grades and homework penalties in red.
e. Add your name to the worksheet. Print the worksheet twice, once with displayed values and once with cell formulas. Use the Page Setup command to display gridlines and row and column headings. Add a custom header with your name, the course you are taking and your instructor's name. Create a custom footer with today's date and the current time.
f. Print the worksheet a second time to reflect the comment in cell E19. Add a cover sheet, and then submit both copies of the worksheet together with the cell formulas to your instructor.

Please help me with the attachment and A, B and C only.

Attachments

Solution Preview

Solution:
a. Regularly, average = total of all scores/ number of scores
Here we need to amend this computation slightly. We still want to add scores but drop the lowest. =sum(B13:E13) will add all the scores while min(B13:E13) will find the smallest one.
Both, sum(B13:E13) and min(B13:E13) will go in the numerator. Will you add the two or subtract one from the other?
What about the denominator? How many scores are there? To use the power of Excel, count(B13:E13) will tell us how many there are. Remember we will remove one ...

Solution Summary

Completion of an excel spreadsheet for calculating grade averages including how to input a formula into Excel and how to calculate averages.

$2.19