SOLVED EXCEL EXERCISE

SOLVED EXERCISE

Open Excel and follow step-by-step instructions provided below:

1) Enter data as it is displayed on the following worksheet:

2) Place the cursor in F2 and write the =SUM(C2:E2) formula to get the total credit units for 3 modules.

3) Place the cursor in F3 and write the =SUMIF(C3:E3,">=50",$C$2:$E$2) function to get the total credit units of modules that Akon passed.

4) Place the cursor in F4 and write the =SUMIF(C4:E4,">=50",$C$2:$E$2) function to get the total credit units of modules that Bkon passed.

5) Place the cursor in F5 and write the =SUMIF(C5:E5,">=50",$C$2:$E$2) function to get the total credit units of modules that Ckon passed.

6) Place the cursor in F6 and write the =SUMIF(C6:E6,">=50",$C$2:$E$2)  function to get the total credit units of modules that Dkon passed.

7) Place the cursor in F7 and write the =SUMIF(C7:E7,">=50",$C$2:$E$2)  function to get the total credit units of modules that Ekon passed.

8) Place the cursor in F8 and write the =SUMIF(C8:E8,">=50",$C$2:$E$2) function to get the total credit units of modules that Fkon passed.

9) Place the cursor in F9 and write the =SUMIF(C9:E9,">=50",$C$2:$E$2) function to get the total credit units of modules that Gkon passed.

10) Place the cursor in G3 and write the =IF(C3<50,$C$1,"")& " " &IF(D3<50,$D$1,"")& " " &IF(E3<50,$E$1,"") function to get the names of modules that Akon has failed.

11) Place the cursor in G4 and write the =IF(C4<50,$C$1,"")& " " &IF(D4<50,$D$1,"")& " " &IF(E4<50,$E$1,"") function to get the names of modules that Bkon has failed.

12) Place the cursor in G5 and write the =IF(C5<50,$C$1,"")& " " &IF(D5<50,$D$1,"")& " " &IF(E5<50,$E$1,"") function to get the names of modules that Ckon has failed.

13) Place the cursor in G6 and write the =IF(C6<50,$C$1,"")& " " &IF(D6<50,$D$1,"")& " " &IF(E6<50,$E$1,"") function to get the names of modules that Dkon has failed.

14) Place the cursor in G7 and write the =IF(C7<50,$C$1,"")& " " &IF(D7<50,$D$1,"")& " " &IF(E7<50,$E$1,"") function to get the names of modules that Ekon has failed.

15) Place the cursor in G8 and write the =IF(C8<50,$C$1,"")& " " &IF(D8<50,$D$1,"")& " " &IF(E8<50,$E$1,"") function to get the names of modules that Fkon has failed.

16) Place the cursor in G9 and write the =IF(C9<50,$C$1,"")& " " &IF(D9<50,$D$1,"")& " " &IF(E9<50,$E$1,"") function to get the names of modules that Gkon has failed.

17) Place the cursor in H3 and write the =IF(F3=$F$2,"PASS",IF(F3>=20,"PASS WITH RETAKE","REPEAT")) function to know the verdict of Akon.

18) Place the cursor in H4 and write the =IF(F4=$F$2,"PASS",IF(F4>=20,"PASS WITH RETAKE","REPEAT")) function to know the verdict of Bkon.

19) Place the cursor in H5 and write the =IF(F5=$F$2,"PASS",IF(F5>=20,"PASS WITH RETAKE","REPEAT")) function to know the verdict of Ckon.

20) Place the cursor in H6 and write the =IF(F6=$F$2,"PASS",IF(F6>=20,"PASS WITH RETAKE","REPEAT")) function to know the verdict of Dkon.

21) Place the cursor in H7 and write the =IF(F7=$F$2,"PASS",IF(F7>=20,"PASS WITH RETAKE","REPEAT")) function to know the verdict of Ekon.

22) Place the cursor in H8 and write the =IF(F8=$F$2,"PASS",IF(F8>=20,"PASS WITH RETAKE","REPEAT"))  function to know the verdict of Fkon.

23) Place the cursor in H9 and write the =IF(F9=$F$2,"PASS",IF(F9>=20,"PASS WITH RETAKE","REPEAT")) function to know the verdict of Gkon.

24) To highlight marks of students which are less than 50, go to Home Tab, Styles Group, Click on Conditional Formatting icon, choose Highlight Cells Rules=>Less Than, enter 50 in Less Than dialog box, then click Ok. (This should highlight cells with marks less than 50 as “Light Red Fill with Dark Red Text”.

The $ sign used in functions above is called dollar sign. It is found on the keyboard by pressing (Shift+4).

If all functions are well written, you should get the resulting worksheet looking like the following: