Exercise 1 - Ms Excel functions

Download this Spreadsheet file
The spreadsheet contains sample data that must be analyzed.
1. Open spreadsheet.
2. Create new sheet called "My employees".
3. Format cells as shown on the example screen (background, merge, order, etc.)
4. In E column, insert data validation, where only "a" or "p" letters are allowed. Define ERROR window (second image)that is displayed when outher value is entered.

Use Data Validation option, located at Data tab

5. In F column insert function that displays: "absent" if "a", "present" if "p", empty if no value is set in E column.

Use embeded IF function, use Conditional Formatting

6. Copy E+F columns into: G+H, I+J, K+L, M+N, O+P
7. Create summary at the bottom of the names table (rows: 33,34,35)

Row: 33 and 34 - use COUNTIF function.

8. Display number of absences in Q column. Define conditional formatting (data bars)
9. Count percentage value of absences in column R.

Use SUM of COUNTIF functions to get total number of meetings.

10. In S column display information if student is suspended. Suspension when student has more than 50% of absence.

Use IFfunction

11. Change Surname color in column C to red, when student is suspended.

Use Conditional Formatting

12. Go to sheet named "Marks" and create two columns headers "Student" and "Mark".
13. In "Student" column add data validation to display all students surnames as a list (picture 3)

Use List in Data Validation

14. Add data validation for "Mark" column, where only Integer values from 2 to 5 are allowed.
15. Input some marks for random students.
16. On "Students" sheet define formula that displays best mark of the student in T column. Search within marks saved on "Marks" sheet.

Use MAX function with IF function and array as a arguments (Shift + Ctrl + Enter required to accept formula)

17. Display average mark of each student in U column. "No marks" for students without any mark should be displayed.

Use SUMIF function to get sum of current student marks and COUNTIF function to get total quantity of student marks.
IF function used with COUNTIF function in a condition statement can help to display "no marks" sign.