EPPD1063: Computer Application LAB TEST 1: Excel
EPPD1063: Computer Application
LAB TEST 1
May 2024
2 hours
Instruction: Please save all answers (Excel and Words files) in a folder and named it with your Matric Number. Please submit both files via UKMFolio. No excuse is accepted if you forgot to submit any of the files.
Part 1: Microsoft Excel (50 marks)
1. Please create a new Microsoft Excel file and save the file. In this file, please rename the sheet as Data.
2. By using the data below, please proceed with the following instructions:
Item | Matric Number | Name | Q | A | M | F |
1 | Your Own Matric No | Your Own Name | 65 | 71 | 79 | 84 |
2 | A191342 | NG ZHI QIAO | 73 | 74 | 53 | 67 |
3 | A191455 | NUR HALIZA BINTI HALIM | 90 | 90 | 70 | 80 |
4 | A191549 | AIN BINTI HISHAM | 97 | 74 | 60 | 78 |
5 | A191637 | NAZARUL BINTI HANIFFA | 69 | 63 | 51 | 61 |
6 | A191648 | NUR IZZAH BINTI AZMI | 80 | 83 | 53 | 70 |
7 | A191721 | RAMA A/L VIJAY | 81 | 67 | 68 | 83 |
8 | A191900 | NABILAH BINTI MAJID | 82 | 83 | 52 | 55 |
9 | A191911 | AMIRAH ISMAIL | 67 | 69 | 60 | 88 |
10 | A191957 | SURAYA RAHMAN | 78 | 69 | 61 | 79 |
11 | A192029 | KUMARI A/P SAMY | 87 | 69 | 53 | 76 |
12 | A192055 | SHAMIL BIN GHAFAR | 73 | 74 | 60 | 59 |
i) The details for the columns are as below:
Q is for Quiz, A is for Assignments, M is for Midterm Examination and F is for Final Examination. The total marks for each student is 100%.
ii) By using the formula available in Microsoft Excel, please find the largest and smallest marks for each of the Q, A, M and F columns. Please add new rows in the table with appropriate title for the answer.
iii) For each of the Q, A, M and F columns please COUNT how many students with
a) 80 marks and above
b) 60 marks and below
Please add new rows in the table with appropriate title for the answer.
iv) Calculate the following:
- For Quiz = 20%
- For Assignments = 30%
- Midterm examination = 20%
- Final examination = 30%
You should add new columns for Total Marks and Grade. Please calculate the Total Marks based on the percentages above.
v) Please use the following table for Grades:
0-39 | E |
40-43 | D |
44-46 | D+ |
47-49 | C- |
50-54 | C |
55-59 | C+ |
60-64 | B- |
65-69 | B |
70-74 | B+ |
75-79 | A- |
80-100 | A |
vi) Please add new columns in the table for the status of the students. This status is based on the following grades:
Grade | Status | Status2 |
D+ - E | Fail | improve |
C+ - C- | Pass | improve |
A- - B- | Good | improve |
A | Excellent | maintain
|
vii) Please create an appropriate chart or graph which shows the achievement of the students based on their grade (number of students by grade).
viii) The following table is a new Address table. The first row is your own details. Please fill up the Name2 column with the parent / guardian name. Please create any names for the parents/guardians:
Name2 | Address1 | Address2 | Postcode | City | State |
|
|
|
|
|
|
| No.3 | Seksyen 5 | 43650 | Bangi | Selangor |
| 100 | Jalan 3/2A | 77000 | Jasin | Melaka |
| PL1/2 | Taman Mawar | 80000 | Johor Bahru | Johor |
| Lot 1A | PengkalanHulu | 26200 | Gong Badak | Terengganu |
| No.7 | JalanPelandok | 86200 | SimpangRenggam | Johor |
| Lot 365 | JalanKuda | 43000 | Kajang | Selangor |
| No.777 | JalanEmas | 77451 | Ayer Keroh | Melaka |
| Lot 123 | JalanBahagia | 32460 | Slim River | Perak |
| No.34 | Taman Permata | 43600 | Bandar BaruBangi | Selangor |
| Lot 6295 | JalanPenjara | 34000 | Taiping | Perak |
| No.362 | Taman Chen Siew | 20000 | SeberangPerai | Pulau Pinang |
Part 2: Microsoft Word (50 marks)
1. By using data from Microsoft Excel file that you created earlier, you need to create a formal letter. This letter is to inform about the performance of student to their parent / guardian. Please use the Mail Merge technique for this particular section. Please use the following template and add your own logo (you can Google the logo) with your creativity:
(LOGO)
<Name2>
<Address1> <Address2>
<Postcode>
<City><State>
Dear Sir/Madam,
Exam Result for Computer Applications
With reference to the above matter, this is the formal result for <Name>, <MatricNumber>.
The details are as below:
ID : <MatricNumber>
Total Marks : <TotalMarks>
Grade : <Grade>
Status : <Status>
We hope that <Name> could <Status2> his/her performance in the following semester. If you have any question regarding to this matter, please contact me, Dr Surya Binti Ahmad at this number, 03-32569841 or via email, surya@silk.edu.my.
Thank you for your attention.
Best regards,
……………….
Dr Surya Binti Ahmad
Faculty of Management
SILK University College
