Добавлено в закладки: 0
QMB-4700
Assignment-1
Create s blank workbook and call it QMB4700,Assign1 followed by a comma, followed by last name followed by a comma, followed by your first name. For example if I were to name the file, it would be QMB4700,Assign1,shebani,ehsan.xlsx.
A sheet for the raw data for this assignment is provided to you. You will create some new columns as displayed below and as explained below. You may start with the raw data file and type your name somewhere on top of the worksheet. Rename the “Sheet1” tab as Gradebook. On this sheet, you create a gradebook as follows:
Name the ranges for the scores for Quiz-1, Quiz-2 etc. for each column of data that have numbers in them.
In the column for “Total Quiz Score”, you will write a formula for the first student and copy and paste that formula for the remaining students.
In the column for “Lowest Quiz Score”, again, you will write a formula for the first student and paste that formula for the remaining students.
In the column for “Best Two Quiz Score”, again, you write a formula for the first student and paste that formula for the remaining students.
In the column for “Best Two Quiz Score Percent”, again, you write a formula for the first student and paste that formula for the remaining students. You will format the column for two decimal places with percentage formatting.
In the column for “Total” you will write a formula that sums the best two quiz scores, the exam scores and class participation scores. The total score is out of 500 points.
In the column for “Total Percent” you will write a formula that shows the total score as a percentage format and two decimals points.
Add two more columns to this Gradebook (They are not shown in the data above). These two columns will display the letter grade received by each student. In the first grade column, the grade will be computed using the VLOOKUP function, while in the second column it will be computed using the IF function. Both the columns will have identical output, using two completely different approaches.
The grade cutoffs that you will use are: A at 90% or above, B at 80% or above and below 90%, C at 70%, D at 60% and F below 60%. So the grade for the first students will be a C and for the second student a B and so on.
You will format the two Grade columns using conditional formatting so that each grade appears in a different color text and background.
The row for Average should have a formula using a statistical function. You should use the appropriate range names instead of cell addresses. Similarly the rows for Highest, Lowest and Range should have formulas which you should write using statistical functions and using appropriate range names. The Last four rows should be formatted for two decimal places. The two percent columns should be formatted for percent.
Solution of the example in excel.
