Excel Skills for Business: Intermediate II Quiz Answers

All Weeks Excel Skills for Business: Intermediate II Quiz Answers

Excel Skills for Business: Intermediate II Week 1 Quiz Answers

Quiz 1: Data Validation

Q1. Where is the Data Validation button located?

• On the Formula tab.
• In the right-click context menu.
• On the Home tab.
• On the Data tab.

Q2. ‘Any Value’ in the Data Validation dialog means ‘No Validation’.

• True
• False

Q3. Using Data Validation, which option can assist the user to enter valid data by showing a message beside the cell?

• ‘Input Message’ under the Settings tab.
• ‘Error Message’ under the Input Message tab.
• ‘Input Message’ under the Input Message tab.
• ‘Error Message’ under the Settings tab.

Q4. The error alert style ‘Stop’ will deliver a warning message to users when they attempt to enter invalid data. User can decide whether or not to enter the intended data anyway.

• True
• False

Q5. After applying Data Validation to existing data, any invalid data will be fixed automatically.

• True
• False

Q6. Which should be selected under the Settings tab of the Data Validation dialog if the numbers 100, 200, 300, 400, 500 are allowed to be entered in a cell?

• ‘Any Value’ under the Allow options.
• ‘Text Length’ under the Allow options.
• ‘Whole Number’ under the Allow options.
• ‘Decimal’ under the Allow options.

Q7. “Enter a value that is between 0 and 0.03 (inclusive).”

Based on the input message above, which validation criteria are correct?

• Allow: Any value
• Data: Between
• Minimum: 0
• Maximum: 0.03
• Allow: Whole Number
• Data: Between
• Minimum: 0
• Maximum: 0.03
• Allow: Decimal
• Data: Less than
• Maximum: 0.03
• Allow: Decimal
• Data: Between
• Minimum: 0
• Maximum: 0.03

Q1. Based on the Validation Criteria in the screenshot, what can you expect to see in your Drop-down list?

• accepted
• on Hold
• Rejected
• Under review
• Accepted
• Under Review
• Rejected
• On Hold
• accepted
• Under review
• Rejected
• on Hold

Q2. The values that appear in a drop-down list will always appear in alphabetical order.

• True
• False

Q3. What is the advantage of converting your lookup list into a named range and table?

• The advantage is that there is no need to update the validation criteria as the look-up list changes.
• The advantage is that the drop-down list will automatically appear in alphabetical order.
• There is no particular advantage in doing this.

Q4. To create a Drop-down List, each distinct option is entered in the Source box in the Data Validation dialog. The items should be separated by a:

(One or more correct answers possible. Partial credit will be awarded)

• Space
• Comma and Space
• Comma
• Dash

Q1. It is possible to use formulas in data validation but not in conditional formatting.

• True
• False

Q2. Which option below is correct about using a formula in conditional formatting?

• Go to the Home tab, click on the Conditional Formatting button, then select New Rule, Use a Formula.
• Go to Formulas tab, click on the Conditional Formatting button, then select New Rule, Use a Formula.
• Right click on the selected cell/s, choose Format Cells, Conditional Formatting, then Formulas.

Q3. Which option below describes correct cell referencing to apply conditional formatting to the whole row of a data set rather than a single cell?

• Relative cell references for both rows and columns.
• Absolute cell references for both rows and columns.
• Relative references for rows and absolute references for columns.
• Absolute references for rows and relative references for columns.

Q1. Practice makes permanent! Practice challenges are not graded and it is up to you whether or not you want to do them. They provide you with some great opportunities to commit what you have just learned to long-term memory through repetition and application. Why don’t you give it go?

Download the instructions and get started.

They provide you with some great opportunities to commit what you have just learned to long-term memory through repetition and application. Why don’t you give it.

Quiz 2: Week 1 Final Assignment

Q1. The attached spreadsheet provides information regarding the companies 60 jobs in three states (NSW, QLD, and VIC). Have a look at the Jobs-Data worksheet. Find the cells that contain Data Validation to answer the first .

Which column contains Data Validation? (Write the column letter)

They provide you with some great opportunities to commit what you have just learned to long-term memory through repetition and application. Why don’t you give it.

Q2. One of the cells in that column contains invalid data. Use the Data Validation tools to search for any invalid data and correct it.

Which cell contains invalid data? (Write the cell address)

They provide you with some great opportunities to commit what you have just learned to long-term memory through repetition and application. Why don’t you give it.

Q3. Find the cell where Data Validation has been applied unnecessarily and remove the data validation from the cell.

Which cell contains unwanted data validation? (Write the cell address)

They provide you with some great opportunities to commit what you have just learned to long-term memory through repetition and application. Why don’t you give it.

Q4. Apply a drop-down list to cell M6 (3 states: NSW, QLD, VIC) and create a formula in cell O6 to calculate the number of jobs in the selected state.

Find the number of jobs in the state of VIC.

They provide you with some great opportunities to commit what you have just learned to long-term memory through repetition and application. Why don’t you give it.

Q5. Apply a drop-down list to cell M10 (3 states: NSW, QLD, VIC) either from scratch or by copying the validation from M6. Create a formula in cell O10 to calculate the total profit from the jobs in the selected state.

Find the total profit from the jobs in the state of NSW. (put in the number only, no separators or currency sign)

They provide you with some great opportunities to commit what you have just learned to long-term memory through repetition and application. Why don’t you give it.

Q6. Apply a drop-down list to cell N14 (Numbers: 1, 2, 3, …, 9) and apply Conditional Formatting to the Number of people column to find out the number of jobs with more than the number of people in the cell N14.

How many jobs have more than 8 people?

They provide you with some great opportunities to commit what you have just learned to long-term memory through repetition and application. Why don’t you give it.

Q7. Apply Data Validation to the Job number column to avoid duplicate job numbers. Is there any duplicated data in this column after applying Data Validation? Specify the cell addresses, separated by a comma.

They provide you with some great opportunities to commit what you have just learned to long-term memory through repetition and application. Why don’t you give it.

Q8. Add Data Validation to the Markup percentage column to have any number between 0.1 to 0.5. Which is the best option for the type of validation criteria?

• Whole Number
• Decimal
• Any Value

Q9. The Job start date column should be validated to have the Job date after 2015-07-01. Which is the best option for the type of validation criteria? Apply the validation to this column.

• Date/less than
• Date/greater than
• Date/between

Q10. Copy the Job start date column Data Validation to Estimated finish date column. Which cell/cells contain invalid data if any?

They provide you with some great opportunities to commit what you have just learned to long-term memory through repetition and application. Why don’t you give it.

Excel Skills for Business: Intermediate II Week 2 Quiz Answers

Quiz 1: Logical Functions I: IF

Q1. How many arguments are expected by the IF function?

• One
• Two
• Three
• It depends on the variables.

Q2. Using the IF function to determine the maximum of 2 cells A1 and B1 the coding could look like:

• =IF(A1>B1,A1 is bigger)
• =IF(A1>B1,A1,””)
• =IF(A1<B1,B1 is bigger)
• =IF(A1>B1,A1,B1)

Q3. If we want to report whether A1 and B1 are the same in value using the IF function, the coding could look like:

• =IF(A1>B1,””,”same”)
• =IF(A1<=B1,”same”,”same”)
• =IF(A1/B1=1,”same”,””)
• =IF(A1=B1,”same”,””)

Q4. If we want to report whether A1 and B1 are different in value using the IF function, the coding could look like:

• =IF(A1>B1,””,”different”)
• =IF(A1<=B1,”same”,”different”)
• =IF(A1/B1<>1,”different”,””)
• =IF(A1=B1,””,”different”)

Q1. How many input arguments are expected by the AND function?

• 1 only.
• 2 only.
• 3 only.
• Variable – up to 255.

Q2. How many input arguments are expected by the OR function?

• 1 only.
• 2 only.
• 3 only.
• Variable – up to 255.

Q3. A formula that would give the answer TRUE if the cells X1, Y1, and Z1 are in ascending order (no ties allowed) and FALSE if not might look like:

• =AND(X1<Y1,X1<Z1)
• =AND(X1<Y1,Y1<Z1)
• =AND(X1<Z1,Y1<Z1)
• =OR(X1<Y1,Y1<Z1,X1<Z1)

Q4. To check for duplicate values (at least 2 cells with the same value) amongst X1, Y1 and Z1, which of the following will give TRUE if there are duplicate values?

• =OR(X1=Y1,Y1=Z1)
• =AND(X1=Y1,Y1=Z1)
• =OR(X1=Y1,X1=Z1)
• =OR(X1=Y1,X1=Z1,Y1=Z1)

Q1. To detect whether the values in cells X1, Y1 and Z1 are in ascending order (no ties allowed), the coding for giving an output “Ascending Order” could look like:

• =IF(AND(X1<Y1,Y1<Z1),”Ascending Order”,””)
• =AND(IF(X1<Y1,Y1<Z1),”Ascending Order”,””)
• =IF(OR(X1<Y1,Y1<Z1),”Ascending Order”,””)
• =IF(AND(X1<Y1,X1<Z1),”Ascending Order”,””)

Q2. To detect whether the cells X1, Y1 and Z1 contain a duplicate (2 or more cells with the same value), the coding for giving an output “Duplicate” could look like:

• =IF(OR(X1=Y1,Y1=Z1,X1=Z1),”Duplicate”,””)
• =IF(AND(X1=Y1,Y1=Z1),”Duplicate”,””)

Q3. To detect whether the cells X1, Y1 and Z1 contain exactly 2 values the same (e.g. X1 and Z1 are the same and Y1 is different), the coding for giving an output “Duplicate” could look like:

• =IF(OR(AND(X1=Y1,Y1<>Z1),AND(Y1=Z1,X1<>Y1),AND(X1=Z1,X1<>Y1)),”Duplicate”,””)
• =IF(OR(AND(X1=Y1,Y1<>Z1),AND(Y1=Z1,X1<>Y1)),”Duplicate”,””)
• =IF(OR(X1=Y1,Y1=Z1),”Duplicate”,””)
• =IF(AND(X1=Y1,Y1=Z1),”Duplicate”,””)

Q1. For the arguments to the IF function, other IF functions may be embedded within them.

Of the 3 arguments to the IF function, which can have another IF nested in them?

• First argument: logical_test
• Second argument: value_if_true
• Third argument: value_if_false

Q2. To detect whether the cells X1, Y1, and Z1 contain exactly 2 values which are the same (e.g. X1 and Z1 are the same and Y1 is different), the coding for giving an output “Duplicate” could look like:

• =IF(X1<>Y1,IF(X1=Z1,”Duplicate”,””),””)
• =IF(X1=Y1,IF(X1=Z1,””,”Duplicate”),IF(OR(X1=Z1,Y1=Z1),”Duplicate”,””))
• =IF(X1=Y1,IF(X1=Z1,””,”Duplicate”),IF(AND(X1=Z1,Y1=Z1),”Duplicate”,””))

Q3. To detect whether the values in cells X1, Y1, and Z1 are in descending order (with no ties), the coding for giving an output “Descending Order” could look like:

• =IF(X1>Y1,IF(X1<=Z1,””,”Descending Order”),””)
• =IF(Y1>Z1,IF(X1<=Z1,””,”Descending Order”),””)
• =IF(X1>Y1,IF(Y1>Z1,”Descending Order”,””),””)
• =IF(X1>Z1,IF(Y1<=Z1,””,”Descending Order”),””)

Q4. Consider the code =IF(X1>=Y1,””, IF(Y1>=Z1,””,”tick”)).

Supposing the output “tick” is produced, which of the following could be true about X1, Y1, Z1?

Please note that ties are allowed for this .

• X1, Y1, Z1 are in ascending order.
• X1 and Y1 are equal and Z1 is less than X1.
• X1, Y1, Z1 are in descending order.
• X1 is greater than Y1 and Y1 equals Z1.

Quiz 2: Week 2 Final Assignment

Q1. We would like to make a courtesy phone call to customers who may not be aware of our Express delivery option. Some customers who consider their orders to be urgent, do choose an express mode of delivery, while others do not. We would also like more senior members of staff to make these calls if these orders are generating a lot of revenue for our company. In this regard, there are two proposals to evaluate which orders are the most valuable for our company.

To answer the s in this quiz, download the attached worksheet and follow the instructions in each .

We want to figure out which orders are considered urgent by our customers. This is to be judged by whether they specify that their orders are High priority or Critical priority, or if they choose Express Air for delivery.

Populate column X so that it shows TRUE if they classify their order as High or Critical or if they have chosen Express Air as the mode of shipment. At the top of the column, count the number of cases for column X. Report this number here.

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Q2. We want to figure out which customers who consider their orders to be urgent, actually choose an urgent method of delivery.

Populate column Y so that it shows TRUE if they classify their order as High or Critical AND they have chosen Express Air as the mode of shipment. At the top of the column, count the number of cases for column Y. Report this number here.

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Q3. We want to figure out which orders are considered Medium priority or higher.

Populate column Z so that it shows TRUE if they classify their order as Medium or High or Critical. At the top of the column, count the number of cases for column Z. Report this number here.

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Q4. Which customers are not choosing Express Air for their delivery?

Populate column AA so that it shows TRUE if they do not choose Express Air as the mode of shipment. Hint: The function NOT(condition) gives the opposite result, which may be useful here. At the top of the column, count the number of cases for column AA. Report this number here.

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Q5. Given your answers to s 3 and 4, we want to call the customers who have priorities that are Medium or higher (Medium, High or Critical) but do not choose Express Air for delivery.

Populate column AB so that it shows “Call” if BOTH column Z AND AA are TRUE or “Do Not Call” otherwise. At the top of the column, count the number of customers we are going to call. Report this number here.

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Q6. We will be calling those customers whose entry in column AB is “Call” to talk to them about our express delivery option. We would like to monitor the number of calls and the duration of the calls for research.

We do not want to call customers who have told us they are not interested, so this column will have the default value of “interest potential“, but this can be switched to “no interest” if the customer indicates that they are not interested.

Recalling previous lessons on data validation and dropdown lists, allow the entries in column AC to come from a dropdown list containing either “interest potential” or “no interest“. Using an IF statement, populate the column with “interest potential” if the entry in column AB is “Call”. The cell should be blank otherwise.

Change the sixth entry that says “interest potential” to “no interest” (count down six ignoring blanks and replace the formula in that cell). How many calls were made to that customer for this order?

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Q7. We also want to calculate the average time spent on calls per order number, but we don’t want an error message to appear if the call has not been made, but rather say “not called yet”.

Populate column AF so that it shows, if they were a client to be called, the average duration of the calls made to them. If an error results from this calculation because there have been no calls yet made, report “not called yet”. If they were not a customer to call, leave the cell blank.

At the top of the column, calculate the average of this column and report the result here, to 2 decimal places. Use #.## number format.

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Q8. We would like to see which of our orders are generating the most revenue. If an order has an Order Total of more than $1,000, we will call this a “Star” order.

Populate column AG: Star Order Proposal A with the value “Star” if the Order Total is more than $1,000. If it is not, the cell should simply remain blank. How many “Star” orders are there?

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Q9. A consultant asks us to consider Star Order Proposal B which classifies orders according to “5 Star” (more than $1,000), “4 Star” (more than $500, up to $1000) and simply blank ($500 or below).

Populate column AH with the values specified above. How many “4 Star” orders are there?

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Q10. We would like the Deputy Sales Manager to make the courtesy call if the order is a “Star” order, and we have flagged the order as a call that needs to be made.

If they are a “customer to call” and they are a “Star” order (according to Proposal A), then we want the Deputy Sales Manager to call them.

Populate column AI: Deputy Manager to Call with an appropriate IF statement. How many calls will the Deputy Sales Manager have to make?

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Q11. We would like the Sales Manager to make a different courtesy call if the average call time is greater than 7 minutes, and the order is a “4 Star” or “5 Star” order, but only if we have flagged the order as a call that needs to be made.

If the Average Call Time is more than 7 minutes, and the order is a “4 Star” or “5 Star” order (according to Proposal B), then we want the Sales Manager to call them. If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement. (You may need to incorporate a ‘does not equal’ for “not called yet”.)

How many calls does the Sales Manager have to make?

If the customer has not been called yet, the manager should not call them. Populate column AJ: Manager to Call with an appropriate IF statement.

Excel Skills for Business: Intermediate II Week 3 Quiz Answers

Quiz 1: Introduction to Lookups: CHOOSE

Q1. Download and open the workbook:

Your friend has entered the following formula in cell C6 and wishes to copy it down. =CHOOSE([@[Loc Code]],K6,K7,K8,K9,K10). Can you predict what will happen?

• This won’t work. The friend made a mistake. The function should look like this: =CHOOSE([@[Loc Code]],K6:K10)
• This will work just fine.
• This will work fine for the first one but won’t produce the desired outcome for the lower values in the table.

Q2. In the video we mention that the CHOOSE function is not necessarily the best choice when we have a large lookup table. Can you think of why this is the case?

• The CHOOSE function is limited to only 10 items for the list specified.
• It will take a long time to type out a long list since we can only separate the values by commas.
• The CHOOSE function can handle 254 values for the list specified.

Q3. Which of the options below is an accurate definition of the CHOOSE function?

• The CHOOSE function is pointed to an index and a list of values. CHOOSE then returns the value that corresponds to the position in that list. The first item in the list is counted as 0.
• The CHOOSE function is pointed to an index and a list of values. CHOOSE then returns the value that corresponds to the position in that list. The first item in the list is counted as 1.

Q4. It is important to understand the syntax of the CHOOSE function. What is the first argument that the CHOOSE function is asking for?

• An index number – this is the numeric value that provides the position within the list.
• A logical test – this is to check whether the numeric value is where CHOOSE is meant to look for it.
• The location code – this is the number that CHOOSE is looking for first.

Quiz 2: Approximate Matches: Range VLOOKUP

Q1. When you are doing a VLOOKUP, what is the lookup value?

• This is the value that we don’t know. We want Excel to return this value from the lookup table.
• This is the value we do know. We want Excel to lookup this value from the table.

Q2. When you are doing a VLOOKUP, what is the col_index_num?

• This has to be sometimes specified by the column (e.g. F) for very large datasets.
• This is the number you specify when you want Excel to lookup a value that is not in the first column.
• This is the number of the column that contains the value you want Excel to return from the lookup table.

Q3. With a range VLOOKUP you can look up a value that falls within a given range and return a corresponding value.

• True
• False

Q4. Which characteristics about the data set that the table_array argument points to are important to keep in mind when creating a range VLOOKUP?

• The lookup data set that the table_array points to should be listed in alphabetical order, the range should be referred to with relative references, and headings should be included.
• The lookup data set that the table_array points to should be listed from smallest to largest, the range should be referred to with absolute references, and headings should not be included.
• The lookup data set that the table_array points to should be listed in alphabetical order, the range should be referred to with absolute references, headings should be included.
• The lookup data set that the table_array points to should be listed from smallest to largest, the range should be referred to with relative references, and headings should not be included.

Quiz 3: Exact Matches: Exact Match VLOOKUP

Q1. Which of the statements below about the fourth argument, [range lookup] in the VLOOKUP syntax is correct?

• This argument is optional, however, it has to be set to FALSE if you want to do an exact match lookup.
• This argument is optional, however, it has to be set to FALSE if you want to do a range lookup.
• This argument is not optional and has to be set to FALSE to do a range lookup.

Q2. Consider the following parameters:

This is the value we know: Table column with the heading Materials

This is our data set: Table with the name Classification

This is the column with the target value in it: Fourth from the left in the data set.

Which of the functions below was written correctly to successfully complete an exact match VLOOKUP?

• =VLOOKUP([@Materials],Classification,4,FALSE)
• =VLOOKUP([@Materials],”Classification”,4,FALSE)
• =VLOOKUP([@Materials],Classification,4,0)
• =VLOOKUP([@Materials],”Classification”,4,0)

Q3. In order to set a VLOOKUP to do an exact match, you have to complete the fourth argument by adding:

• The number 1
• The number 0
• The word FALSE
• The word TRUE

Q4. When is it appropriate to use an exact match VLOOKUP?

• When you are using a horizontal table and you want the lookup value to exactly match the first row.
• When the data is arranged in descending order and a range VLOOKUP is no longer appropriate.
• When you are dealing with a lookup value with a unique identifier, such as bank account numbers, and you want to match to a vertical table.

Q1. Which of the following properties are true for the MATCH function?

• The data has to be arranged in a specific order to use the MATCH types of less than or greater than.
• The default match type (the MATCH function’s third argument) is exact match.
• The MATCH function does not care if the lookup array is horizontal or vertical.
• The MATCH function returns the value of a lookup value in the lookup array.
• The MATCH function returns the position of a lookup value in the lookup array.

Q2. What are the required arguments of a MATCH function?

• match_type
• lookup_value
• table_array
• lookup_array

Q3. Is it necessary to highlight all of the column headings (this must correspond to your table array) when using the VLOOKUP and MATCH functions together?

• Yes, it is necessary. You have to make sure that the VLOOKUP table array matches the MATCH lookup array.
• No, it is unnecessary. Make sure to highlight the headings of the table in the table array of the VLOOKUP function and Excel will understand what you mean.
• No, it is not necessary. You just need to make sure the lookup value is included in the VLOOKUP table array.

Q4. What is the purpose for the format of the reference (in bold)?

=VLOOKUP([@Item],parts,E$4,FALSE)

• The reference is a relative reference, and we are using it so that we can drag the formula across to copy it.
• The reference is an absolute reference, and we are using it so that the formula will always refer to this specific cell even when we copy it.
• The reference is a mixed reference, and we are using it so that we can drag the formula down to copy it.

Quiz 4: Week 3 Final Assignment

Q1. Download the following file in order to answer all the s in this final assessment.

How many sheets does this file have?

It is necessary to highlight all of the column headings (this must correspond to your table array) when using the VLOOKUP and MATCH functions together

Q2. The CHOOSE function

Using the data in Data Task 1, use the CHOOSE function to describe the items coded.

The data is available in the ‘Data’ sheet. Here you will need to click the “plus” icon near ‘Task 1’ to reveal the relevant data.

Now, using the data in Task 1, use the CHOOSE function to fill the green cells D31:E33.

Every time you enter something in the green cells, the Assessment Code for Task 1 will change.

This code has been programmed by us for the purposes of checking your answers.

When you have the correct answers, and if you have correctly used the CHOOSE function, the Assessment Code that is generated will let us know this.

Please do not try to type anything into the yellow cell as that would disrupt the assessment code. If you do type into the yellow cell, re-download the Excel file from Coursera.

You will be required to enter the data in the green cells, as well as the data in the yellow cell onto the Coursera platform so that we can mark you.

What is the ‘Assessment Code for Task 1’? Note that this will only work in the English version of Excel 2013 or newer. If you are using Excel in a different language, please set it to English while doing this assessment task.

Q3. In the Instructions sheet, what is in cell D32?

It is necessary to highlight all of the column headings (this must correspond to your table array) when using the VLOOKUP and MATCH functions together

Q4. In the Instructions sheet, what is in cell E32?

It is necessary to highlight all of the column headings (this must correspond to your table array) when using the VLOOKUP and MATCH functions together

Q5. Range VLOOKUP

Using the data in Data Task 2, use Range VLOOKUP to complete this task.

Long Distance phone calls are based on a sliding scale depending on the length of time take on a call. The charges rates are found in the ‘Data’ sheet in Task 2. For example, a call that lasts between 10 and 20 minutes will be charged $2.00. As soon as a call duration passes the threshold it is charged at the higher rate. For example, a 7 minute call will be charged $1.75.

Complete the Phone bill charges and calculate the total at the bottom.

The data is available in the Data sheet. Here you will need to click the “plus” icon near Task 2 to reveal the relevant data.

Now, using the data in Task 2, use Range VLOOKUP to fill the green cells D50:D54.

Every time you enter something in the green cells, the Assessment Code for Task 2 will change.

This code has been programmed by us for the purposes of checking your answers.

When you have the correct answers, and if you have correctly used Range VLOOKUP, the Assessment Code that is generated will let us know this.

Please do not try to type anything into the yellow cell as that would disrupt the assessment code. If you do type into the yellow cell, re-download the Excel file from Coursera.

You will be required to enter the data in the green cells, as well as the data in the yellow cell onto the Coursera platform so that we can mark you.

What is the ‘Task 2 Total’ in cell D55? (Enter this as a number with two decimal places and without a $ sign)

It is necessary to highlight all of the column headings (this must correspond to your table array) when using the VLOOKUP and MATCH functions together

Q6. What is the Assessment Code for Task 2?

Note that this will only work in the English version of Excel 2013 or newer. If you are using Excel in a different language, please set it to English while doing this assessment task.

It is necessary to highlight all of the column headings (this must correspond to your table array) when using the VLOOKUP and MATCH functions together

Q7. Exact Match VLOOKUP

At a fast food Café, the waitress went round and took down the orders for a large group of 10

Use VLOOKUP with exact matching to complete the table.

Make any adjustment to the items wherever necessary

Calculate the Grand Total for the party of 10

The data is available in the Data sheet. Here you will need to click the “plus” icon near ‘Task 3’ to reveal the relevant data.

Now, using the data in Task 3, use Exact Match VLOOKUP to fill the green cells C72:C82.

Every time you enter something in the green cells, the Assessment Code for Task 3 will change.

This code has been programmed by us for the purposes of checking your answers.

When you have the correct answers, and if you have correctly used Exact Match VLOOKUP, the Assessment Code that is generated will let us know this. For the final argument to VLOOKUP, range_lookup, you should use FALSE instead of 0, otherwise the Assessment Code will not be correctly generated.

Please do not try to type anything into the yellow cell as that would disrupt the assessment code. If you do type into the yellow cell, re-download the Excel file from Coursera.

You will be required to enter the data in the green cells, as well as the data in the yellow cell onto the Coursera platform so that we can mark you.

What is the ‘Task 3 Total’ in cell E83? (Enter this as a number with one decimal place and without a $ sign)

It is necessary to highlight all of the column headings (this must correspond to your table array) when using the VLOOKUP and MATCH functions together

Q8. What is the Assessment Code for Task 3?

Note that this will only work in the English version of Excel 2013 or newer. If you are using Excel in a different language, please set it to English while doing this assessment task.

It is necessary to highlight all of the column headings (this must correspond to your table array) when using the VLOOKUP and MATCH functions together

Q9. INDEX and MATCH

An outdoor clothing and accessories store placed prices on items according to the colours in demand for the item as per table in Data Task 4

Using the INDEX and MATCH functions, find the prices on these three items

The data is available in the Data sheet. Here you will need to click the “plus” icon near ‘Task 4’ to reveal the relevant data.

Now, using the data in Task 4, use INDEX and MATCH to fill the green cells D99:D101.

Every time you enter something in the green cells, the Assessment Code for Task 4 will change.

This code has been programmed by us for the purposes of checking your answers.

When you have the correct answers, and if you have correctly used INDEX and MATCH, the Assessment Code that is generated will let us know this.

Please do not try to type anything into the yellow cell as that would disrupt the assessment code. If you do type into the yellow cell, re-download the Excel file from Coursera.

You will be required to enter the data in the green cells, as well as the data in the yellow cell onto the Coursera platform so that we can mark you.

What is the ‘Task 4 Total’ in cell D102? (Enter this as a number without a $ sign)

It is necessary to highlight all of the column headings (this must correspond to your table array) when using the VLOOKUP and MATCH functions together

Q10. What is the Assessment Code for Task 4 in cell G99?

Note that this will only work in the English version of Excel 2013 or newer. If you are using Excel in a different language, please set it to English while doing this assessment task.

It is necessary to highlight all of the column headings (this must correspond to your table array) when using the VLOOKUP and MATCH functions together

Excel Skills for Business: Intermediate II Week 4 Quiz Answers

Quiz 1: Error Checking

Q1. What does it mean if a cell displays #########?

• The contents of the cell are too wide to display.
• You have used an invalid function name.
• The formula is trying to divide by 0.
• The formula in the cell is incorrect.

Q2. What are some of the benefits of working with the Error Checking tool?

(One ore more answers are possible. Partial credit will be awarded.)

• The tool provides access to supporting documentation on common errors.
• The tool can automatically correct any errors in formulas that occur in the spreadsheet
• The tool enables us to evaluate each flagged formula step-by-step.

Q3. Which of the options below will help us to locate formulas with errors in a spreadsheet?

• Go to the Formulas tab and click on Show Formulas.
• Display formulas with errors through the Go to Special dialogue.
• Go to the Review tab and click on Check Spelling.

Q4. One of the cells in your spreadsheet is showing an #N/A error. What does that mean?

• It means that you have misspelt the name of the function you used in your formula.
• It means that one of the values you used in your formula cannot be found.
• It means that you have chosen the incorrect function for the purpose of your formula.

Q1. Which of the statements below is true about circular references?

(One or more answers are possible. Partial credit will be awarded)

• If your spreadsheet contains one or more circular references, Excel will display this in the Status Bar.
• Circular references can be found using the Error Checking tool
• Circular references occur when the calculation cell is included in the range used for the calculation.
• Circular references only can occur with the SUM function.

Q2. What does it mean when a cell has a small green triangle in the top-left corner?

• The green triangle indicates that Excel considers that there is an error in the formula in the cell.
• The green triangle indicates that there is a comment in this cell.
• The green triangle indicates that there is a circular reference error in that cell.

Q3. In the Error Checking options, you can set the automatic calculation of formulas in your spreadsheet to manual.

• True
• False

Q4. Once set to manual, workbook calcuations can only be done by re-setting the calculation options to automatic.

• True
• False

Q1. To locate all the cells that are used in a formula in a cell, I can

• Click on Find & Select > Go To Special > Formulas.
• Click on Evaluate Formulas.
• Click on Trace Precedents.
• Click on Trace Dependents.

Q2. It is possible to select a range of cells and find all their precedents in one step by clicking on Trace Precedents.

• True
• False

Q3. It is possible to show the Precedents of the Precedents of a cell by clicking the Trace Precedents button as often as needed.

• True
• False

Q4. Which of the options below will remove the arrows created by the Trace Precendents/Dependents tool?

• Press the Delete button.
• Click on Clear Formats on the Home tab.
• Press the Esc button.
• Click on Remove Arrows on the Formulas tab.

Q5. Take a look at the screenshot below. What does the dashed black arrow indicate?

• This refers to a value in a different table.
• This refers to a value in a different worksheet.
• This refers to a value that is further removed from the original calculation.

Q1. In order to protect a worksheet, you need to assign a password.

• True
• False

Q2. What is the purpose of protecting a worksheet?

(One or more answers are possible. Partial credit will be awarded.)

• To allow a team of users to work on a worksheet without accidentally making changes key elements of the worksheet.
• To protect the structure of one of your worksheets.
• To prevent unauthorised users from opening an Excel file.
• To prevent unauthorised users from sharing an Excel file with others.

Q3. These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook, Excel can retrieve it.

• True
• False

Q1. We have been given a vehicle spare parts ordering spreadsheet that has many errors. This is in the file attached here.

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

By first activating the top left cell and selecting Error Checking, and Next, count the number of cells with suspected errors in them. Remember to count the first error found. Report the number here:

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q2. Does the number reported above represent the number of cells we need to repair? Explain.

• Yes, there is a one-to-one correspondence between errors counted to cells in need of repair
• No, sometimes errors produce other errors in a chain for a particular calculation.

Q3. Activate the cell M2, one of the cells containing an error, then select Trace Dependents. What happens and why?

• Nothing except for a ping. There are no cells that depend on this cell as an input.
• Arrows appear from the cell, with at least one being red indicating the error.
• Nothing except for a ping because that cell is not in error.

Q4. There are 3 (other) cells that should agree with the value in cell B2. Which cells are they?

• C2
• L17
• D2
• A1
• G2

L1

Q5. What is the name given to the type of checking being done between the cells in  4?

• Error matching
• Tracing precedents
• Tracing precedents and error checking options
• Cross-checking (totals and subtotals)

Q6. Is there another group of cells that should agree in their value? If so, list their addresses:

No.

• Yes: H2, M2, Q2 and U2 only.
• Yes: H2 and M17 only.
• Yes: H2, M2, Q2 and U2 as well as the group F2, L2, P2 and T2.

Q7. Activate H2 and use the Trace Error to find the root cause of the error report in that cell. Was there anything to edit in cell H2 itself?

• H2 did not need to be changed.
• H2 had a spelling error (syntax error) that had to be changed.
• H111 had a spelling error (syntax error), H2 itself did not need to be changed.
• H2 and H111 both contained errors that had to be corrected.

Q8. Activate M2, then, by repeated use of the Trace Precedents, determine the number of generations behind the result. Report the answer here.

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q9. Go ahead and fix all errors. And provide the values of the cells requested.

The cells with an obvious error message are not the only cells with errors. Look back to your answers to s 4-6. Do all of the cells that should have the same value actually do so?

What is in cell B2 after the errors have been fixed?

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q10. What is in cell H2 after the errors have been fixed?

Use the format ###.##. Do not include a currency symbol or thousands separator.

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q11. What is in cell M16 after the errors have been fixed?

Use the format ###.##. Do not include a currency symbol or thousands separator.

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q12. We wish to protect this spreadsheet from further errors after we pass it on, as our duty of care. The data resides in columns B to G.

What facility would allow us to protect the formulas in column H as well as the summary tables to the right of this?

• Hide Sheet
• Protect Sheet
• Add a comment

Q13. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.

State the type of Data Validation that might have been used for each of the columns requested:

Column B:

• Dropdown list
• Date only
• Whole number
• Positive value

Q14. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.

State the type of Data Validation that might have been used for each of the columns requested:

Column C:

• Dropdown list
• Date only
• Whole number
• Positive value

Q15. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.

State the type of Data Validation that might have been used for each of the columns requested:

Column D:

• Dropdown list
• Date only
• Whole number
• Positive value

Q16. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.

State the type of Data Validation that might have been used for each of the columns requested:

Column E:

• Dropdown list
• Date only
• Whole number
• Positive value

Q17. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.

State the type of Data Validation that might have been used for each of the columns requested:

Column F:

• Dropdown list
• Date only
• Whole number
• Decimal

Q18. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.

State the type of Data Validation that might have been used for each of the columns requested:

Column G:

• Dropdown list
• Date only
• Whole number
• Positive value

Excel Skills for Business: Intermediate II Week 5 Quiz Answers

Quiz 1: Modelling Functions (SUMPRODUCT)

Q1. Which of the options below best describes how SUMPRODUCT works ?

• SUMPRODUCT sums up the elements of multiple arrays and then calculates the product of the sums.
• SUMPRODUCT finds the product of multipe arrays and then sums up the products.
• SUMPRODUCT calculates the product of elements within an array and then sums up those products.

Q2. A colleague is unsure what order the arrays need to be entered in the SUMPRODUCT function. What is the best response to this?

• It is important to consider the order of the arrays. If the arrays are not in the right order, an erroneous answer can be produced.
• It is actually not necessary to use a particular order when placing the arguments in SUMPRODUCT.

Q3. How can SUMPRODUCT be used to add up values that meet certain criteria (perform a COUNTIF)?

• It finds the product element wise of one times whether each element in the array met the condition (1 if it did and 0 if it didn’t) and then sums up the resulting products.
• It can’t fully be understood how Excel does this as this is odd for the SUMPRODUCT function to do.
• This only works sometimes, it could produce an erroneous answer on a large dataset.

Q4. The arrays in a SUMPRODUCT can have differing amounts of rows and columns.1 point

• True
• False

Q5. When using SUMPRODUCT to calculate the total marks of students in Column G, for efficiency, which array will need to be in absolute cell referencing terms?

• $B$4:$F$4
• $B$5:$F$5
• $B$3:$F$3

Q6. For the Excel worksheet below, typing either of the following two formulas in cell G5 will return the same answer:

=SUMPRODUCT(B5:F5,$B$3:$F$3)

=SUMPRODUCT($B$3:$F$3,B5:F5)

• True
• False

Q1. Where is the Data Tables tool located?

• You can access Data Tables through the right-click context menu.
• Go to the Data tab, locate the Forecast group and go to the What-If Analysis.
• Go to the Home tab, locate the Data Tools group and go to the What-If Analysis.

Q2. When you create a Data Table, you need to specify your input row or column. What is that?

• The input for the Data Table is the variable that will change and for which you are building this Table.
• The input for the Data Table is the variable that stays the same and is your base scenario.
• The input for the Data Table is the variable that Excel needs to look up from your array.

Q3. The function of the Data Table tool can be summarized as follows:

Data Tables are a quick way of displaying how the outcomes of a calculation change by varying the inputs.

• True
• False

Q4. For the Data Table in A15:B22, a Data Table is useful because

• It calculates the estimated wage cost for a variety of values for hours worked.
• It calculates the estimated wage cost for a variety of values for the hourly wage.
• It calculates the estimated wage cost for a variety of values for the hourly wage, and a variety of values for hours worked.

Q5. For the Data Table in A15:B22, when selecting the Data Table option, what is cell E16 referred to?

• The row input cell.
• The column input cell.

Q6. To create the Data Table displayed above, after typing the formula in A26, you need to select:

• A26:G34
• A27:G34
• B26:G34

Q7. For the Data Table in A26:G34, after clicking on Data Table, what do cells E16 and E18 represent?

• E16 is the Row input cell; E18 is the Column input cell.
• E16 is the Row input cell; E18 is the Row input cell
• E16 is the Column input cell; E18 is the Row input cell.

Q1. Where is the Goal Seek tool located?

• Go to the Formulas tab and in the Data Tools group locate What-If Analysis and it can be found there.
• Go to the Data tab and in the Forecast group locate What-If Analysis and it can be found there.
• Right click on the cell that contains the calculation and go to Goal Seek on the menu that appears.

Q2. Which of the options below correctly explains the Goal Seek tool?

• The Goal Seek tool through multiple iterations finds the corresponding outcome of a calculation to a specified input.
• The Goal Seek tool through multiple iterations finds what input value is necessary to obtain a certain set value for the calculation.
• The Goal Seek tool can only be used when the calculation cell contains a simple arithmetic formula.

Q3. Refer to the worksheet above (Revenue, Cost and Profit are in $millions). When trying to find the Quantity that will result in a Profit of $12 million, using Goal Seek, the Set Cell is:

• 12
• B3
• B4
• B2
• B1

Q4. Refer to the worksheet above (Revenue, Cost and Profit are in $millions). When trying to find the Quantity that will result in a Profit of $12 million, using Goal Seek, the To value is:

• 12
• B4
• B3
• B2
• B1

Q5. Refer to the worksheet above (Revenue, Cost and Profit are in $millions). When trying to find the Quantity that will result in a Profit of $12 million, using Goal Seek, By changing cell is:

• B1
• B4
• B3
• 12
• B2

Q1. The following file describes the situation faced by a landscaping company, ‘Landscaping Solutions’.

If there is great weather, the company takes the following hours to complete a driveway, backyard, front yard, back verandah, and front porch: 20, 30, 25, 25, and 20 hours respectively.

Good weather increases all times by a factor of 1.1.

Bad weather increases all times by a factor of 1.25.

Stormy weather increases all times by a factor of 1.5.

A natural disaster increases all times by a factor of 2.0

Great weather, good weather, bad weather, stormy weather, and a natural disaster have the following probabilities of occurrence: 0.6, 0.25, 0.1, 0.04, and 0.01 respectively.

All these probabilities (weightings) sum to 1.

Use SUMPRODUCT to calculate the estimated hours of work to complete the landscaping.

Please answer using the format ###.##.

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q2. The labourers hourly wage is $30 an hour. In B15, calculate the estimated wage cost, without bonuses, given the estimated hours of work. What is the estimated wage cost? Do not include a currency symbol.

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q3. In A15:B22, create a Data Table that calculates the estimated wage cost for wage rates from $24 an hour to $36 an hour in increments of $2 an hour.

What is the sum of all the estimated wages in your data table (B16:B22)? Do not include a currency symbol.

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q4. In A26, calculate the estimated wage cost with a bonus rate of 10%. In A26:G34, create a Data Table that calculates the estimated wage cost for wage rates from $24 an hour to $38 an hour in increments of $2 an hour, including bonuses for the workers ranging from 5% to 10% in increments of 1%.

What is the sum of all the estimated wages in your data table (B27:G34)? Do not include a currency symbol.

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q5. It has been estimated the company’s revenues per week can be estimated by the equation revenue = (240 – 30Q)*1000Q where Q is the number of gardens attended to per week. In B39, calculate the total revenue if the quantity is 1. What is the total revenue? Do not include a currency symbol.

-29760

Q6. It has been estimated the company’s costs per week can be estimated by the equation cost = (120 + 8Q)*1000 where Q is the number of gardens attended to per week. In B40, calculate the total cost if the quantity is 1. What is the total cost? Do not include a currency symbol.

8120

Q7. What is the total profit if the quantity is 1?

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q8. Use Solver to calculate the quantity that maximises the profit per week. Use the format #.##.

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password you used to encrypt your workbook

Q9. Use Scenario Manager to save two alternate scenarios for Q = 3 (Less Gardens) and Q = 4 (More Gardens), and display the Scenario Summary.

Which screenshot most closely resembles what you see?

• This:
• This:
• This:

Q10. Use Goal Seek to calculate: What is the quantity required to generate a profit of $300,000 per week? Use the format #.##

These days we have to remember many different passwords. Fortunately, there is a fail-safe built into Excel. In the event that you forget the password, you used to encrypt your workbook.

Excel Skills for Business: Intermediate II Week 6 Quiz Answers

Quiz 1: Record a Macro

Q1. How do you access the Developer tab?

• You can only access it by turning it on in the Customize Quick Access Toolbar dialogue.
• You need to have a special version add-on for Excel to access the Developer tab.
• You can only access it by turning it on in the Customize Ribbon dialogue which you can find in the Backstage view.

Q2. Changes made by a macro can be undone with CTRL-Z.

• True
• False

Q3. You have finished recording your macro, hit the Save button and see this dialogue. What happens if you click on Yes?

• The macro I have just finished recording will be saved in my workbook but in a different file format.
• The macro I have just finished recording will be saved in my workbook.
• The macro I have just finished recording will be lost.

Q4. Which of the file types below will allow workbooks to be saved with macros? (Multiple correct answers possible; partial credit will be awarded)

• *.xlsx
• *.xlsm
• *.xlsb
• *.xltm

Q5. What is the computer language called in which Excel records a macro?

the file types below will allow workbooks to be saved with macros

Q1. Which of the following options are components of the VBA Editor?

• Project Explorer
• Macro Editor
• The Developer tab
• Code Window

Q2. What is this line in the Code Window referred to and what is it’s purpose?

• This is a comment – they can appear anywhere in the code and are always ignored by the compiler.
• This is a line of code – it’s an important part of the subroutine and controls one of the macro’s action.
• This is the heading row – all macros begin with this line of code.

Q3. What is the purpose of this line of code?

• This is a line of code – it’s an important part of the subroutine and controls one of the macro’s action.
• This is the heading row – it stands for subroutine and all macros begin with this line of code.
• This is a comment – they can appear anywhere in the code and are always ignored by the compiler.

Q4. Each instruction to the compiler should appear on a new line in the Code Window.

• True
• False

Q1. You need to change the shortcut key that starts your macro. How do you do this?

• Go to the Developer tab, click on Macros, then choose Options.
• Go to the Developer tab, click on Macros, then choose Edit to access the VBA Editor.
• Go to the Developer tab, click on Macros, then choose Step Into.

Q2. You can copy and paste lines of code from one macro to another in the VBA Editor.

• True
• False

Q3. How do you delete an existing macro in a workbook?

• Delete the Run button from your workbook.
• Go to the Developer tab, click on Macros, then choose Delete.
• Go to the Developer tab, click on Macros, then choose Options.

Q1. You have decided to write a macro to quickly and easily generate an expense sheet. Follow the steps carefully and answer the s as you go.

Create a new blank workbook. Save it with the name Expenses and change the file type to one that will support macros. Which of the following would be a valid option?

Excel Template (.xltx)

Macro-Enabled Workbook (.xlsm)

CSV (.csv)

Excel Workbook (.xlsx)

Q2. Which option would we click on the Developer tab to add a trusted location?

• Macro Security
• Visual Basic
• Macros
• COM Add-Ins

Q3. Record a Macro following these steps (you may wish to run through them first).

1. Ensure Relative Referencing is off. Click in B1. Open the record macro dialog, change the macro name to Expenses, give it a shortcut key Ctrl+Shift+E, click OK to start recording
2. Click in A1 and type Expenses, click Enter twice.
3. In A3 type Date, in B3 type Description, in C3 type Distance and in D3 type Cost, press Enter.
4. In A4 type =TODAY()-WEEKDAY(TODAY(),3) to get the first date of the current week. Press Enter.
5. Copy A4 and Paste Values into A4 to replace the calculation with the result.
6. Use the Fill Handle to copy A4 down to A8. Press Enter
7. In A9 type TOTAL, press Tab three times.
8. In D9 type =SUM(D4:D8) and press Enter.
9. Change the font size of A1 to 20 and widen column B.
10. Click Stop Record and save your workbook.

If you made a mistake while recording, you could always stop recording, delete the macro and start again…

• True
• False

Q4. Open the Macro in the Visual Basic editor. The very first line should say Sub Expenses(). We are not going to change it but if someone changes it to Sub Travel Expenses() what will happen?

• The macro will not be affected
• The Macro will generate an error
• When we run the macro the heading in the worksheet will say Travel Expenses
• The Macro will now be called Travel Expenses

Q5. Look at the lines of code directly beneath the Sub with the apostrophes (‘) in front, these lines are called…

• Statements
• Reserved Words
• Comments
• Executable Code

Q6. Look at the line that says:

‘ Keyboard Shortcut: Ctrl+Shift+E

If we changed it to Ctrl+E what will happen?

• The keyboard shortcut for the macro will be changed to Ctrl+E
• We will now be able to run the Macro using Ctrl+E or Ctrl+Shift+E
• The Macro will generate an error
• The macro will not be affected

Q7. Find the line of code that does the SUM. It looks a bit unusual as it is a relative reference formula. Which of the following formulas is correct (as shown in your code):

• ActiveCell.FormulaR1C1 = “=SUM(R[5]C:R[1]C)”
• ActiveCell.FormulaR1C1 = “=SUM(R[-5]C[1]:R[-1]C[1])”
• ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
• ActiveCell.FormulaR1C1 = “=SUM(R[5]C[1]:R[-1]C[1])”

Q8. In the formula above, if we were to replace the 5 with a 4 what would happen when we ran the macro?

• The macro will not be affected
• The SUM function generated would add the values from D5:D8
• The SUM function generated would add the values from D5:D9

The macro will error

Q9. Find the line of code that says

.Size = 20

Change it to

.Size = 24

Which of these statements is true:

• The heading Expenses will be set to size 24 next time the macro is run
• The column width will immediately be changed to 24pt wide
• The heading Expenses in the workbook will immediately be changed to size 24
• The column width will be set to 24pt next time the macro is run

Q10. Return to the Excel spreadsheet. Which keyboard shortcut can we use for this (on the PC)?

• Alt+F11
• Ctrl+F11
• Shift+F11
• Ctrl+Shift+F11

Q11. Click in a blank worksheet (if necessary add one). Click into cell B2 and run the macro. Which statement is true of the heading Expenses? (Note: If the macro generates an error, try recording it again and don’t make any edits to it.)

• It has been inserted in A2 because it is a relative reference macro
• It has been inserted in A1 because the macro uses relative referencing
• It has been inserted in A1 but if relative referencing had been on it would be in A2
• It has been inserted in A1 because all macros start in A1

Q12. In C4 enter the distance 52.5 and copy down to C8. We want to record a macro that will multiply the distance travelled in the cell to the left by 0.73.

Click in D4 and record a relative reference macro called Calc that multiplies the value in the cell to the left by 0.73, press Enter, and then stop recording. Open the macro in the VBA editor. What is the second line of executable code?

• Range(“C5”).Select
• ActiveCell.Offset(1, 0).Range(“C5”).Select
• Range(“C4”).Select
• ActiveCell.Offset(1, 0).Range(“A1”).Select

Q13. Return to the spreadsheet and click in D5. Run the Calc macro. Which cell is now selected?

Calc macro from a button in the worksheet. Insert a shape in the worksheet and change the label

Q14. We would like to be able to run the Calc macro from a button in the worksheet. Insert a shape in the worksheet and change the label to Calc. To run the macro we right click on the shape and select Run.

• True
• False

Q15. Save your workbook but leave open. Create another blank workbook. Click in cell A1 and type 192 then press Enter (to go to cell A2). Run the macro Calc. What result is returned and why?

• None of the above, you can’t run the macro as it is not in this workbook
• 0
• 140.16
• #REF!

Q1. You are required to work on a spreadsheet for an Event Planning company who is hosting a Charity Fundraiser. Follow the instructions carefully and answer the s as you go. Note that some data is stored in tables and there are several named ranges already in the workbook. Feel free to apply your own named ranges where appropriate. Also, the Error checking rules should be set to the default.

Go to the Client Database Worksheet. This contains a list of all regular clients. We want to avoid introducing data entry errors so we will look at adding data validation.

Which data validation option would be best for First Name?

• Text Length
• List
• Custom
• Any Value

Q2. Which data validation option would be best for Client ID?

• List
• Decimal
• Text Length
• Whole Number

Q3. Using the lists in the Lookup Lists tab, add validation for the Organisation and Country Code so that only values that appear in the lists can be entered. Next Circle Invalid Data. Just looking at data in rows 7 to 27, how many red circles appear?

Correct the data by selecting the correct option from the list. For the Country Code, choose the closest one alphabetically.

Q4. Add data validation to prevent users entering a Start Date in the future. Which of the following is the correct formula?

• =$E$7<=DATE()
• =$E$7<=TODAY()
• =<DATE()
• =E7<=TODAY()

Q5. We want to identify all clients who started from 2017-01-01 onwards. In I7 enter a formula that will put the text “New” if the start date is the same or more recent than the date in N7 and otherwise leave the cell blank. How many new joiners are shown in O7?

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q6. We have decided to send a gift to clients who have shown long-term loyalty or attended a lot of events. Create a calculation in J7 that will put “Yes” in the column if the start year was before 2013 or if the events attended is 15 or over, otherwise leave blank. How many gifts are we going to need to send (see N16)?

Hint: use the YEAR function i.e. YEAR(E7)

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q7. We award a status based on how many events clients have attended. If they have attended fewer than 10 events they are given Bronze, 10-19 they get Silver, 20-29 is Gold and anything from 30 upwards is Platinum. Enter a formula in K7 to lookup the Client’s status using the lookup array in M10:N13. How many Gold members do we have (value in O12)?

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q8. We need to contact all the new members who live in Great Britain (GB). Add conditional formatting to make the whole row of the table yellow (or any colour of your choosing) where the Country is GB and the New Status is New (Hint: use a function that evaluates multiple criteria). In the Client ID column sort by colour to arrange the members we need to contact at the top of the list. What is the value of Check Digit 1 in N15?

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q9. You have now completed the required changes to the Client Database, click onto the Attendees worksheet, and follow the instructions to complete the calculations required.

A list of Client IDs for those clients attending has been entered in the table Attendees. To make this data easier to understand and work with, use an appropriate lookup function to lookup the First Name for each of the Clients attending. You should get one NA error. What Client ID caused the error? It turns out this client has unsubscribed and will not be attending, remove this record (be careful not to delete the seating plan data on the right).

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q10. Use an appropriate lookup function to lookup the Last Name for each of the Clients attending. Sort the data by Last Name (A-Z). Which Client ID is now first (in A7)?

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q11. Use appropriate lookup functions to lookup the Country and Status for each of the Clients attending. How many Bronze members are attending (shown in J10)?

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q12. The seating plan in I10:M64 has allocated seating areas by Country and Status. In the Seating Area column, use an appropriate lookup function to look up the correct seating area from the seating plan using the client’s Status and Country. How many people are currently being seated in area F (shown in M8)?

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q13. Well done. Now click into the Cost Overview sheet. There are a few errors that we will need to address and then we will look at different cost models.

You will notice quite a few errors. Start by clicking into D9. This is a very simple formula, so it’s probably not causing the error, it’s just trying to work with a cell that has an error in it. Use the Trace Error tool to find the cell that is causing the error. Each red arrowhead indicates a cell affected by the original error, how many are there?

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q14. Which cell did the error originate in?

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q15. Correct the error. What is the Total Cost (D9) now? Enter whole numbers only.

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q16. That has fixed most of the problems, but there is still a green triangle showing in cell D17, which suggests we may have another error. Click on D17 and identify what the problem is, if necessary correct it. What is the Total Cost (D9) now? Enter whole numbers only.

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q17. You suspect that changing the value in D6 will impact a lot of the calculations, but you want to find out how many. Click in D6 and Trace Dependents (just once). How many direct dependents does it have? (Count the blue arrowheads)

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q18. Clear the arrows, and now find out which cell is the direct precedent of D7 using Trace Precedents. What is the cell reference (do not include sheet name)? Remove arrows when done.

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q19. We are still quite far off our target of 200 guests and a bit worried that we might not achieve it. Change the value in D6 to 1 and then use Goal Seek to find out the minimum number of guests we need to not make a loss, i.e. get $0 profit. What is the minimum number of guests? Enter a whole number only (no decimal places).

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q20. Change the value in D6 to 91 to see what profit we are currently making. We would like to see how our profit will be affected by changing our catering company. Use the Scenario Manager to create a scenario called Food2U using the cell D23 with its current value. Add two additional scenarios, one called Munchies with the value Munchies in D23 and another called Janelle’s with the value Janelle’s. Show the scenario for Munchies. What is the Profit in D12? Enter the number to 2 decimal places.

Q21. Create a Summary of all 3 Scenarios. Which caterer yields the highest profit?

• Janelle’s
• Food2U
• Munchies

Q22. With 91 guests coming and Munchies as our caterer, our cost per guest is higher than the ticket price. Use the Solver tool to minimise the Cost/Guest by adjusting Guests (D6), Site Staff (B17) and Speakers (B31). Add constraints so that Site Staff cannot be less than 2, Speakers cannot be less than 1 and Guests cannot be more than 400. Save the scenario as Min Guest Cost and keep the solution. What is the new Cost/Guest? Enter the number to 2 decimal places.

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q23. We want to protect all the calculations in this worksheet, but we need users to be able to still edit cells D6 and D23. Before applying protection we should:

• Unlock these cells
• Unhide these cells
• Lock these cells
• Hide these cells

Q24. Having made provision for D6 and D23, how would we then protect the rest of the worksheet?

• Review > Protect Workbook
• Review > Protect Sheet
• File > Info > Protect Workbook > Protect Workbook Structure
• File > Info > Protect Workbook > Encrypt with Password

Q25. To make it easy for their users to switch between caterers you decide to create 3 macros to show the 3 different scenarios. These macros need to be relative references.

• True
• False

Q26. Record a macro called ShowFood2U (no shortcut key and store in workbook) that shows the scenario Food2U. (Don’t perform any other actions while recording.) Open the Macro in the VBA Editor. Which of the following is the line of code to show the Scenario?

• Sheet.Scenario(“Food2U”).Show
• Sheet.Show.Scenarios(“Food2U”)
• ActiveSheet.Scenario.Show(“Food2U”)
• ActiveSheet.Scenarios(“Food2U”).Show

Q27. Copy the whole macro (from Sub to End Sub) and Paste it underneath the End Sub. Change the name to ShowJanelles() and replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles. What is the total catering cost now (D28)? Just enter a whole number (no $ or decimal places).

Repeat this step to create a macro to show the Munchies scenario.

replace the text “Food2U” with “Janelle’s”. Go back to your worksheet and click the Macros button. You should see two macros there. Run the one called ShowJanelles.

Q28. You want to be able to run the macros using the buttons provided (to the right of the catering totals). To do so, after you unprotect the worksheet, you will need to:

• Click Developer Tab > Macros > Options > Assign Macro
• Right Click the Button > Assign Macro
• Click File > Options > Macro Settings > Run Macro
• Click Developer Tab > Insert > Button (Form Control)

Q29. To delete a macro we can: (select 2 options)

• Right Click the Button > Delete
• Developer Tab > Macros > Delete
• Develop Tab > Macro Settings > Disable All Macros
• Open the VBA Editor and delete the code

Q30 .To preserve the macros in this workbook I can save as what type of file? (select 2 options)

• Excel Workbook (.xlsx)
• Excel Template (.xltx)
• Macro-Enabled Workbook (.xlsm)
• Binary Workbook (.xlsb)

Get All Course Quiz Answers of Excel Skills for Business Specialization

Excel Skills for Business: Essentials Coursera Quiz Answers

Excel Skills for Business: Intermediate I Coursera Quiz Answers

Excel Skills for Business: Intermediate II Coursera Quiz Answers

Excel Skills for Business: Advanced Coursera Quiz Answers