IT Tools in Management

ict-trainer.pl - © 2021 - Grzegorz Szyjewski

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.

7. Create summary at the bottom of the names table (rows: 33,34,35)

9. Count percentage value of absences in column R.

13. In "Student" column add data validation to display all students surnames as a list (picture 3)

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.

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

Use **embeded IF** function, use **Conditional Formatting**

7. Create summary at the bottom of the names table (rows: 33,34,35)

Row: 33 and 34 - use **COUNTIF** function.

9. Count percentage value of absences in column R.

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

Use **IF**function

Use **Conditional Formatting**

13. In "Student" column add data validation to display all students surnames as a list (picture 3)

Use **List** in **Data Validation**

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)

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.