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 5 Logical and lookup functions Test your skills >>> Excel Fundamentals for Data Analysis 1. Question 1 To do this assessment you should download this Excel workbook, follow…
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…
17. Question 17 Discounts are calculated as a percentage of the balance owed, rates vary depending on how large the balance is. The lookup table in S2:T5 details what percentage…
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…
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…
13. Question 13 In column I, use the data in columns E and H to calculate how many days there were between when the invoice was issued and when it…
