In-lab Exercise (March 6th, 2020)

In this exercise, you will practice filling out data in the spreadsheet/worksheet, as well as formatting them. The result of this exercise will be used in later excel assignments.

1. First, download the file lab06_scoresheet_en.xlsx from the course web page. If you access the file from the URL, make sure to download the file from Google Drive. You will see an incomplete spreadsheet, as shown above.
2. Fill out 10 entries of students in row 3 to 12. Each entry must have:
   1. ID
   2. Name.
   3. Midterm Score. (Usually 0-50, but have a few with values over 50 or under 0)
   4. Final Score. (Usually 0-50, but have a few with values over 50 or under 0)
   • Make sure that the first entry is you, filling in your name and student ID.
3. Format the Midterm and Final Score so that all of them show 2 decimal points.
4. Add today's date to Cell B1.
5. Under cell E3-E12 (under Total), use formula to add up the midterm score in column C and the final score in column D for each row.
6. Decorate the spreadsheet as you see fit.

The completed spreadsheet (for this exercise) should look similar to this:

Submission

Save your report to file with studentid_excel1-inlab.xlsx as the file’s name. For example, 500510111_excel1-inlab.xlsx. Then, upload the file to homework submission website (http://hw.cs.science.cmu.ac.th), under your section.

Take-home Assignment (due April 10th, 2020)

From your work in lab07_inlab, you will use formulas and functions to finish the report.

NOTE: Everything in this assignment must be done using formulas and functions, just typing in number will not be accepted.

1. Each midterm and final score should be in the range of 0 to 50. Under column H and I, check for the correctness of Midterm and Final score in column C and D. If the score is in range 0-50, display “Correct”. Otherwise, display “Incorrect”. Note that you must check if the score is no less than zero, as well as no more than 50 for it to be correct.
2. Display summary of total score (Column D) on cells in column I, under Summary. (H14)
   • First cell (next to MIN) should contain the minimum score.
   • Second cell (next to MAX) should contain the maximum score.
   • Third cell (next to Average) should contain the average of all total scores
   • Fourth cell (next to S.D.) should contain the standard deviation of all total scores
3. Calculate grade for each student (using IF() function). The range of each grade is provided under student entries on column B and C, as well as grade letter under column A. The grade should be displayed on column F for each student. This will be similar to page 36 of MS Excel Slide pt. 2.
4. Count student with each grade (using CountIF() function) and display them on column D, next to each grade.
5. Format the spreadsheet as to make it easier to read. Use borders, fills, and fonts as you see fit.

The final result should look like this, cells with green text, are what need to be done using functions and formulas:

Submission

Save your report to file with studentid_excel1-takehome.xlsx as the file’s name. For example, 500510111_excel1-takehome.xlsx. Then, upload the file to homework submission website (http://hw.cs.science.cmu.ac.th), under your section.