Exercise 2 - 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. Import data from other spreadsheets (purchase_orders, employees, job_titles) using relative and absolute references + functions.
 
WARNING: Create formulas for more lines then needed to be able to analyse data even with new inputs.
 

Name: use CONCATENATE function to display surname and name in one cell.

Job title: import job title but only for existing users (no zero for empty rows should be displayed) - use IF function. Use conditional formatting to display job titles in specified color.

Seniority: use dates substraction and TODAY function.

Hour rate: depending on Seniority (empty records should not be displayed) - use embeded IF function:

Hour rate condotions:
<500 days --> 10$
500-1000 days --> 15$
1001-3000 days --> 20$
>3001 days --> 25$

No. of purchase orders: count how many orders were placed by this employee, display nothing if qty is lower than 1 - use IF and COUNTIF functions add ID column and hide it later.
Hint: sheets "employees" and "purchase_orders" have relation based on ID in employees and created_by in purchase_orders

Score rate: score depending on seniority and number of purchase orders:
1 or less purchase per year --> low
2 or more purchase per year --> high
Hint: use seniority in days to count this value and add conditional formatting. Use symbols to check and display smiley faces.

Max, Total and Average purchase values: count what was maximal, total and average value of purchases for each employee - use MAX, AVERAGE, SUM and IF functions (using array values)

Salary: find and assign salary from job_titles table using VLOOKUP function.