CSS 341 - Machine Problem #2

 

Problem Solving with Decision and Loop Constructs

Working with Spreadsheets

 

Due time: Sat.Oct 24, 2009 at midnight (50% credit up to 24 hr late)

               

 

In this machine problem we will practice using simple loop and if-else constructs, in working with commercial spreadsheet applications.

We have to help the Keystone Daily Newspaper¡¦s Employee Promotion Board (KDNEPB) compute the grades for their employees.

 

Employees of the Keystone Daily must take a programming course before they can get promoted (no kidding!). The KDNEPB uses a spreadsheet to record students¡¦ performance in this class. Here is an example in of the format of their record. Notice the empty Final Grade Point column. Our job is to write a VBScript to compute the decimal grade points for this column.

 

Since the KDNEPB does not like to be pinned down on anything, it likes to keep everything flexible:  Students are scored numerically, and their averages for all of the assignments are computed on a 100-pt (percentage) scale.  For each letter grade, there are thresholds based on the score percentages.  There is a mapping from these percentages for each grade level into a numerical grade.  The score thresholds for each grade level and the numerical mapping change from year to year.  For example, and ¡§A¡¨ can require 93% one year and 95% the next.  Similarly a ¡¥B¡¨ could require an 80% one year and a 75% in another.  The letter-grade ranges are then mapped into a numerical range.  For example, an ¡§A¡¨ could be 10.0 pts one year and 12.0 another, while a ¡§B¡¨ could be 7.0 one year and 8.2 another.  Our job will be to take the raw student scores, calculate a final score (average) for each student, and then determine  a final numerical grade via the score to letter-grade and letter-grade to numerical grade mappings.  Here is where you find this information:

 

1.      Student Score to Letter-Grade  mapping:.  the KDNEPB likes to keep student score to grade point mapping a variable. We are told the student-score to letter-grade point mapping is always in the ¡§B¡¨ column, with B1 representing the threshold score for A-grade-point, and B2 B-grade-point, B3 for C-grade-point, and B4 for F-grade-point.  In the sample score sheet above, the threshold for a grade of ¡§A¡¨ is 93 and for a grade of ¡§B¡¨ is 75.  A score of ¡§80¡¨ would be within the B range.

 

2.      Letter-Grade to Numerical Grade mapping: this can also change (e.g. in some years, A is a 10.0, while in others A can be a 4.0.).  We are told that the letter-grade to numerical grade mapping can always be found in the C column, where C1 is the numerical grade for A, C2 for B, C3 for C, and C4 for F.  In the sample score sheet above, an ¡§A¡¨ maps into 10.0 pts, a ¡§B¡¨ into 8.0, a ¡§C¡¨ into 6.0, etc.  A score of 80 would receive a grade between 8 and 10.

 

3.      In addition, we know the students information always begin on row 12, and a ¡§-1¡¨ in column D indicates there are no more students.

 

Given the above specification, here is another example excel format that satisfies the formatting requirement but has a much larger scale for final numerical grades..   Now the final scores are not on a 10-pt, although the letter mapping is still on a percentage basis.

 

If a student¡¦s score is in between two letter grade thresholds, the numerical grade point will be computed based on the student average score and the linear proportion between the two bracketing letter grade scores.   If the score is above the top letter-grade threshold or beneath the bottom one, the numerical grade point assigned is that of the threshold.  In the first score sheet above, the top grade earned would be 10.0 for an ¡§A¡¨ which would be any grade at 93 or above.  An average of 80 would be in the B range and would map into final grade of 8.56.  Make sure you understand this before coding it.

 

Now, implement a VB script to:

¡P         select an Excel spreadsheet from the working directory,

¡P         process the above information,  checking for valid data in the spreadsheet cells

¡P         compute the final numerical grade points of each student,

¡P         fill this information into the corresponding rows in column C of the user selected spreadsheet.

¡P         Save the modified spreadsheet

 

 

You should employ modularization in developing this program, using functions and subprograms where useful..

 

This programming assignment contributes approximately 4.5% towards your final grade for this course.

 

My ¡§black box¡¨ test will execute your program on a score sheet similar to these, but possibly with invalid data in some cells..

 

On a second file in your submission,  briefly answer three questions:  1) Give a flow chart for each module of the vbscript program used here ; 2) provide a short paragraph describing the strategy you used to test or debug this program; and 3) provide a short paragraph describing the programming procedure(s) you used as a team.