Question 1
To do this assessment you should download this Excel workbook, follow the instructions, and answer the questions.
In column D we need a formula to generate the full name.
Which of the following formulas would return the result Tina DE SIATO in cell D23?
Question 2
If we type the formula in cell D4: =PROPER(C4x” “xB4) to achieve the intended result of Question 1 above, what character is required to replace x?
Question 3
If we wanted to change the formula in D4 to return Stevie BACATA, which of the following formulas would achieve this?
Question 4
If we entered the calculation =PROPER(CONCAT(C4,” “,UPPER(B4))) in D4, what result would it return?
Question 5
Fill in the Email column. The format for the email addresses is First Name, a “.“, Last Name, followed by @pushpin.com. It should all be in lower case. For example, Stevie Bacata’s email address is [email protected].
Which of these formulas would create the email addresses correctly? Multiple options may be correct.
Question 6
The Location field contains the 2-digit floor number followed by a hyphen, then the wing followed by a space and then the four-digit extension. We need to split them up so that they are easier to access. For example, 02-West 2635 means Floor 02, West Wing, Extension 2635. Which function should we use to extract the floor number?
Question 7
Which formula could we use to return the extension number (the last four digits in column K) for each staff member?
Question 8
We would like just the first letter of North or West to indicate the wing in column N. Which formula will give the desired result?
Question 9
Since the wings are West and North, which have different numbers of characters, we cannot hard code the number 4. We will need to use a formula that changes with the wing.
What would be the result of the formula: =FIND(” “,K23)?
Question 10
We would like to return the full word of the wing. What formula will give the desired result?
Question 11
There are a few names that need to be cleaned up. Bob Decker (row 22) has an unusual character at the end of his name. What is the code for this character?
Question 12
Which function or functions could you use to remove this unwanted character?
10. Question 10 You would also like to see a breakdown of total outstanding fees and arrears. Start by naming the other columns in the SYS DATA sheet. Back in…
Week 2 >>> Working with numbers and dates Test your skills >>> Excel Fundamentals for Data Analysis 1. Question 1 To do this assessment you should download this Excel…
9. Question 9 The calculation in Column G has been done, it calculates the outstanding balance for each payee by adding up the fee, arrears, and other charges from the…
16. Question 16 Customers who pay 5 working days before the due date are eligible for a discount. In column M calculate the date 5 working days before the due…
Week 4 Tables for automating data manipulation Test your skills >>> Excel Fundamentals for Data Analysis 1. Question 1 To do this assessment you should download this Excel workbook,…
2. Question 2 Using Excel 2019, the problem in question 1 could have been solved using which of the following: (Multiple answers may apply.) 1 / 1 point  CONCAT  Yes, that…
