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?
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,…
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…
6. Question 6 In the BPAY file dates come through in the format YYYYMMDD, which makes them difficult to perform calculations with. In column D create a calculation to extract…
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,…
20. Question 20 A valid solution for Question 19 could be achieved using which of the following combinations of functions? (Multiple answers may be correct.) 1 / 1 point XLOOKUP and MATCH…
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…
