Excel homewrok

B - 20 Appendix B Basic Workbooks (Excel for Windows 2013)

Thinking Like an Engineer 3e Stephan, Bowman, Park, Sill, Ohland An Active Learning Approach Copyright © 2015 Pearson Prentice-Hall, Inc

(4) You wish to calculate your grade point ratio (GPR). Below is a scheme for generating a set of fictitious

grades for the fall semester based upon the first five digits of your college ID number. Assume that each

digit corresponds to the course below as designated.

First digit: ENGR 1000 2 semester hours

Second digit: CHEM 1001 4 semester hours

Third digit: ENGL 1050 3 semester hours

Fourth digit: SOCL 1010 3 semester hours

Fifth digit: MATH 1100 4 semester hours

According to the value of the digit, fictitious final grades are assigned as follows:

Digit value Letter Grade Point Grade

0 or 1 A 4

2 or 3 B 3

4 or 5 C 2

6 or 7 D 1

8 or 9 F 0

Create a worksheet to display the courses listed for the fall semester (Column A), the semester hours

(Column B), letter grade earned (Column C), and number of points earned for each course (Column D). On

top of the worksheet, directly under your name, list the first five digits of your college ID number.

In the first row below the course list, calculate the average GPR for the fall semester using the following

formula: summation of the product of the fall semester hours and number of points earned divided by the

summation of fall semester hours. In this formula, you should use cell references and not use actual

numerical values. Format the number to show two decimal places.

We want to add the spring semester to our calculation. Assume that each digit corresponds to the course

below as designated. Here are the rules for the spring:

First digit: ENGR 1500 2 semester hours

Second digit: CHEM 1002 4 semester hours

Third digit: ECON 2004 3 semester hours

Fourth digit: PHYS 1020 3 semester hours

Fifth digit: MATH 1101 4 semester hours

Add the spring semester to the worksheet below the fall semester created above. In the first row below

the spring course list, calculate the average GPR for the spring semester, formatted in the same way as

the fall GPR.

Two rows below the spring GPR calculation, calculate the overall GPR for the entire year and format it in

the same way as the fall and spring GPR.