1 5. Each employee at WCS has an email address which is always their Lastname_Firstname@wcs.com. Using text functions, put each employee's email address in column "Email Address". For example, for employee # 1011, it should be Gorton_Hazelwcs.com 2 Calculate "Age" and "Years in Service" for each employee on Database worksheet using correct Excel formula. Format with one decimal place. 3. In column "Birthday Today?", your message should be Happy Birthday if today is an employee's birthday. Otherwise, message should be Wait" To be answered on the worksheet entitled "QUERIES" using appropriate Excel functions and/or formulas. There is no partial credit for incorrectant 4. Calculate the minimum salary of all employees How many employees are working at was thint: how many records are there in this database)? What is the average salary of 35 years or older employees and who are from either copier or printer division? What is the maximum salary for employee over the age of 30 hired after 1/1/2000? 8 What percentage of the people are earning above $40,000? (Hint: Use a database function to find the count of personnel earning above $40000. Then calculate the percentage) 9. What is average salary of Copier division personnel who have been with company for at least 10 years but less than 15 years? 10. What is standard deviation of the salary of Copier division personnel who have been with company for at least 10 years but less than 15 years? 11. How many employees are in departments with department name starting with the letter A earning between $30,000 & $40,000? 12. How many employees are there with last name starting with letters Go? 13. Find the percent rank of the salary (use column F) for employee Ellen Smith? Describe what this percentile rank means for this company? HINT: Make use of DGET function 6 7 BUS 2 BUS 2 Samples 2 Dorplan 2 2 Learning Point: This is an example of nested function. Individually there are two simple Excel function that need to be nested based on the argument requirements. First, study what percent rank means. It is a statistical function. Then, figure out what the different arguments you will need in this function, and then how to calculate this from the given Information To be answered on the worksheet entitled "DATABASE 14. We want to find out the personnel who are in top 15% indicated by Tops!) and lowest 15% salary range (to be indicated by "lows, otherwise "Middle in the column under Top Low). (Use IF function with Percentile function) Learning Point: Before you try to solve this study how you use the percentile function to determine what percentile an employee's salary falls in. You can use the Excel help package to explore the percentile function. It is a statistical function 15. In column labeled as "Gor ****. If a person's last name begins with letter then write their last name, otherwise put asterisks. Learning Point: Use the LEFT function to pull out the lett most character of the last name and Database Queries Lookup Text functions SOLUTIONS West Coast West 00 70 01 11 02 Hul DEL ADS WS w VIP * CH 18 DIE YE w F w SED ER STE M DID w DUA M 1 BER 11 SNEL LIN www SUNO ER DILI Leil ALS 1 5. Each employee at WCS has an email address which is always their Lastname_Firstname@wcs.com. Using text functions, put each employee's email address in column "Email Address". For example, for employee # 1011, it should be Gorton_Hazelwcs.com 2 Calculate "Age" and "Years in Service" for each employee on Database worksheet using correct Excel formula. Format with one decimal place. 3. In column "Birthday Today?", your message should be Happy Birthday if today is an employee's birthday. Otherwise, message should be Wait" To be answered on the worksheet entitled "QUERIES" using appropriate Excel functions and/or formulas. There is no partial credit for incorrectant 4. Calculate the minimum salary of all employees How many employees are working at was thint: how many records are there in this database)? What is the average salary of 35 years or older employees and who are from either copier or printer division? What is the maximum salary for employee over the age of 30 hired after 1/1/2000? 8 What percentage of the people are earning above $40,000? (Hint: Use a database function to find the count of personnel earning above $40000. Then calculate the percentage) 9. What is average salary of Copier division personnel who have been with company for at least 10 years but less than 15 years? 10. What is standard deviation of the salary of Copier division personnel who have been with company for at least 10 years but less than 15 years? 11. How many employees are in departments with department name starting with the letter A earning between $30,000 & $40,000? 12. How many employees are there with last name starting with letters Go? 13. Find the percent rank of the salary (use column F) for employee Ellen Smith? Describe what this percentile rank means for this company? HINT: Make use of DGET function 6 7 BUS 2 BUS 2 Samples 2 Dorplan 2 2 Learning Point: This is an example of nested function. Individually there are two simple Excel function that need to be nested based on the argument requirements. First, study what percent rank means. It is a statistical function. Then, figure out what the different arguments you will need in this function, and then how to calculate this from the given Information To be answered on the worksheet entitled "DATABASE 14. We want to find out the personnel who are in top 15% indicated by Tops!) and lowest 15% salary range (to be indicated by "lows, otherwise "Middle in the column under Top Low). (Use IF function with Percentile function) Learning Point: Before you try to solve this study how you use the percentile function to determine what percentile an employee's salary falls in. You can use the Excel help package to explore the percentile function. It is a statistical function 15. In column labeled as "Gor ****. If a person's last name begins with letter then write their last name, otherwise put asterisks. Learning Point: Use the LEFT function to pull out the lett most character of the last name and Database Queries Lookup Text functions SOLUTIONS West Coast West 00 70 01 11 02 Hul DEL ADS WS w VIP * CH 18 DIE YE w F w SED ER STE M DID w DUA M 1 BER 11 SNEL LIN www SUNO ER DILI Leil ALS