Use Data Validation option, located at Data tab5. 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 Formatting6. Copy E+F columns into: G+H, I+J, K+L, M+N, O+P
Row: 33 and 34 - use COUNTIF function.8. Display number of absences in Q column. Define conditional formatting (data bars)
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 IFfunction11. Change Surname color in column C to red, when student is suspended.
Use Conditional Formatting12. Go to sheet named "Marks" and create two columns headers "Student" and "Mark".
Use List in Data Validation14. Add data validation for "Mark" column, where only Integer values from 2 to 5 are allowed.
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.