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?
5. Question 5 Which of the following functions (on their own) could you have used to achieve the outcome in Question 4? 1 / 1 point  SUBSTITUTE, TRIM, or LEFT  SUBSTITUTE,…
12. Question 12 Clear the filter you added in Question 11. In column H use the Customer Reference to look up the invoice date for that customer from the data…
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…
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…
11. Question 11 You realise you could make your life a little easier and your solution more robust by working in a table. Convert the data in A1:Q69 to a…
19. Question 19 Where there is more than one student enrolled, customers are given a 5% sibling discount. This is applied to the fees up front but as part of…
