Reference no: EM132283673
eLearning Assignment -
Assignment description: A group of participants have just completed a leadership training course. During the course they were assessed on 5 activities consisting of individual and group activities. Each activity was assessed by a different instructor who would then enter the scores into their respective worksheets. As the chief instructor you are now ready to rank the participants.
Instructions:
You are required to perform the following tasks, and if functions are required, use ONLY the functions given in Appendix A, taking special care of the following:
1. DO NOT rename the Excel file that you have downloaded. Note that if you rename the Excel file, you WILL NOT be able to upload and submit the file.
2. Do not rearrange the order of the worksheets.
3. Do not introduce any additional formatting to the contents of the workbook.
4. Do not shift the positions of the contents in the worksheets.
5. Do not rename the workbook or worksheets.
Instructions -
1. Download the eLearning Assignment workbook, also referred to as "Starting Materials". Proceed to work on the downloaded workbook by following the instructions below.
2. In cell C11 in the Participants worksheet, insert a formula that extracts the group number for the first participant based on the Groups worksheet. Copy the formula from cell C11 to the range C12:C61.
3. In cell D11 in the Participants worksheet, insert a formula that extracts the score for Activity 1 for the first participant based on the Activity 1 worksheet. Your formula should also convert the score to a value out of 100. Copy the formula from cell D11 to the range D12:D61. Note that the name list in the Participants worksheet and the Activity 1 worksheet may not be in the same order.
4. In cell E11 in the Participants worksheet, insert a formula that extracts the score for Activity 2 for the first participant based on the Activity 2 worksheet. Your formula should also convert the score to a value out of 100. Copy the formula from cell E11 to the range E12:E61. Note that the name list in the Participants worksheet and the Activity 2 worksheet may not be in the same order.
5. In cell F11 in the Participants worksheet, insert a formula that extracts the score for Activity 3 for the first participant based on the Activity 3 worksheet. If the name cannot be found in the Activity 3 worksheet, display "0" (zero) instead. Your formula should also convert the score to a value out of 100. Copy the formula from cell F11 to the range F12:F61. Note that the name list in the Participants worksheet and the Activity 3 worksheet may not be in the same order.
6. In cell G11 in the Participants worksheet, insert a formula that extracts the score for Activity 4 for the first participant based on the Activity 4 worksheet. Your formula should also convert the score to a value out of 100. Copy the formula from cell G11 to the range G12:G61.
7. In cell H11 in the Participants worksheet, insert a formula that extracts the score for Activity 5 for the first participant based on the Activity 5 worksheet. Your formula should also convert the score to a value out of 100. Copy the formula from cell H11 to the range H12:H61.
8. In cell I11 in the Participants worksheet, insert a formula that totals the scores of all 5 activities (rounded to 0 decimal places) based on the weightage of activities given in the Leadership components table. Copy the formula from cell I11 to the range I12:I61. (Note: the weightages in the formula must be specified by using the appropriate cell references of the values in the Leadership components table).
9. In cell J11 in the Participants worksheet, using VLOOKUP, insert a formula that ranks the first participant based on his/her total score as follows:
A (>=90)
B (>=80, <90)
C (>=70, <80)
D (>=60, <70)
E (>=50, <60)
F (<50)
A lookup table for the ranking is set up in H1:I7. Use this table to assign a rank to each participant.
Copy the formula from cell J11 to the range J12:J61.
10. In the range K11:K61 perform the same task as given in step 9 above using nested IF i.e. find the rank of the participant based on his/her total score using the logic given in step 9 and the score-ranking table given in H1:I7.
(Note: Your formula must only contain cell references of the values in the score-ranking table where appropriate)
11. In cell J2 in the Participant worksheet, insert a formula to calculate the number of participants who are ranked "F" based on the results of Step 9 as given in J11:J61. Copy the formula from cell J2 to the range J3:J7.
12. In cell K2 in the Participant worksheet, insert a formula to calculate the percentage of participants who are ranked "F". Copy the formula from cell K2 to the range K3:K7. Note that the cells K2:K7 have already been formatted as percentage with 2 decimal places, so you are not required to do any formatting.
13. Now, you also want to recommend outstanding participants for the next level of training based on the following criteria:
a. Had obtained >=90 for both Activity 1 and Activity 4, AND
b. Had obtained >=65 for Activity 2 or >=80 for Activity 3 or >=70 for Activity 5
In cell L11 in the Participant worksheet, insert a formula to display "Recommended" if the above conditions are met and "Not Recommended" if the above conditions are not met. Copy the formula from cell L11 to the range L12:L61.
14. Upload your completed assignment.
15. Submit your completed assignment.
Attachment:- Assignment Files.rar