Excel Skills for Business: Intermediate I Quiz Answers

All Weeks Excel Skills for Business: Intermediate I Quiz Answers

Excel Skills for Business: Intermediate I Week 1 Quiz Answers

Quiz 1: Multiple Worksheets

Q1. Which of the following options allow us to move a worksheet in a workbook?

(One or more answers are possible – partial credit will be awarded)

Left-click to drag and drop the worksheet tab to the desired position
In the Home tab, select Format > Move or Copy Sheet…
While pressing Ctrl, left-click to drag the worksheet tab to the desired position.
Right-click the worksheet tab and select Move or Copy

Q2. If you delete a sheet by mistake, you can click Undo or press Ctrl+Z to undo the operation.

True
False

Q3. When you click on the + button, what will happen next?

A new worksheet will be added to the right of Sheet1.
A new worksheet will be added to the right of Sheet3.
A new worksheet will be added to the right of Sheet2.

Q4. How do you make a copy of an existing worksheet?

Press Shift, left-click, drag and release.
Left-click, drag and release.
Press Ctrl (Alt on Mac), left-click, drag and release.

Q5. Which of the following is true of the scenario shown below:

Sheet1, Sheet3 and Sheet5 have been grouped using the Shift key.
If I change something on Sheet1, the same change will be applied to Sheet3 and Sheet5.
If I change something on Sheet2, the same change will be applied to Sheet4.

Q1. When you link to a cell in a different workbook, your reference will contain several components. What will the reference look like when you link to a cell such as R28 on the worksheet, Expenses, in a file named Facility-expenses2017.xlsx?

‘(Facility-expenses2017)Expenses’!$R$28
‘[Facility-expenses2017]Expenses’!$R$28
‘[Expenses!]Facility-expenses2017’$R$28
‘[Facility-expenses2017]Expenses’;$R$28

Q2. What tool did we use to display these four workbooks on the screen?

Select the View tab, click on Arrange All, select Tiled.
Select the View tab, click on Arrange All, select Vertical.
Select the View tab, click on Arrange All, select Horizontal.
Select the View tab, click on Arrange All, select Cascade.

Q3. Why would we link workbooks?

To push data from one workbook into one or more other workbooks.
To pull values from one or more workbooks and work with them in one of these workbooks, or work with them in a separate workbook.
To make the same change in all linked workbooks at the same time.

Q4. Once you have created links between workbooks, they will remain intact and continue to work even if you move the location of any of your files.

True
False

Q1. How do you access the Consolidate feature?

Right-click on any cell that you want to use for the consolidated value and choose from the context menu.
Navigate to the Data tab and locate the feature in the Get External Data group.
Navigate to the Page Layout tab and locate the feature in the Sheet Options group.
Navigate to the Data tab and locate the feature in the Data Tools group.

Q2. When using the Consolidate feature, you can only use the SUM function.

True
False

Q3. Which sentence is correct about Consolidate?

The consolidation setup can be run just once.
There is no way to link consolidated worksheet to the data sources.
When you are linking to a data source, the “Create link to data source” check box should be ticked in Consolidate dialog box.
It creates a link between data sources and the consolidated worksheet automatically.

Q4. You can undo Consolidation with Links.

True
False

Quiz 2: Test your Skills: Working with Multiple Worksheets & Workbooks

Q1. Ensure you have downloaded all the C2 W1 Assessment workbooks. Open the workbook C2 W1 Assessment Sales Di. Move the worksheet called Q1 Summary to the front of the other worksheets. In the Q1 Summary worksheet click in cell C5, use 3D formulas to summarise the total quantity sold of item 3243 for January to March. How much commission did Di earn on this item?

Do not enter the $ symbol – please enter the number as #.##

Copy the formula in C5 down to C14. What was Di’s total commission?

Don’t enter the $ symbol – please enter the number as ####.##

Save the workbook, but don’t close it, yet.

Sales Summary consolidate the Q1 summaries from the other three workbooks to get the total sales by item code.

Q3. Open C2 W1 Assessment Sales Lemin and C2 W1 Assessment Sales Summary. In Sales Lemin note all the commissions are coming up as 0. Go to the Data tab, you will observe the commission rate cell F4 is empty. The rate needs to be picked up from the Sales Summary workbook Rates tab. Ensure you can see both workbooks on your screen, click in F4 in Sales Lemin and use a linking formula to pull the commission rate through from Sales Summary. In Sales Lemin return to the Q1 Summary, what is the updated Total Commission?

Don’t enter the $ symbol – please enter the number as #.##

Save the workbook, but don’t close it, yet.

Sales Summary consolidate the Q1 summaries from the other three workbooks to get the total sales by item code.

Q4. Open C2 W1 Assessment Sales Aneesha. You will see a message informing you of broken links, this is because the commission rate is still linked to the old sales summary. The link will need to be updated to look at the rate in C2 W1 Assessment Sales Summary. After updating the link what is Aneesha’s Q1 Commission?

Don’t enter the $ symbol – please enter the number as #.##

Save the workbook, but don’t close it, yet.

Sales Summary consolidate the Q1 summaries from the other three workbooks to get the total sales by item code.

Q5. In C2 W1 Assessment Sales Summary consolidate the Q1 summaries from the other three workbooks to get the total sales by item code for Q1. (Keep in mind that different sales people sell different products). What was the total sales figure for Item Code 3256?

Enter your answer as a 5-digit number ##### – please don’t use decimals, commas or symbols.

Save the workbook, but don’t close it, yet.

Sales Summary consolidate the Q1 summaries from the other three workbooks to get the total sales by item code for Q1.

Excel Skills for Business: Intermediate I Week 2 Quiz Answers

Quiz 1: Changing Text Case (UPPER, LOWER, PROPER)

Q1. Cell A2 contains the text “Sean SANDERS” and Uma wants cell B2 to be “Sean Sanders”. Which function will help her to achieve this?

=PROPER(A2)
Use the Change Case button
=LOWER(A2)
=UPPER(A2)

Q2. Cell A1 contains “Macquarie ” and cell B2 contains “University”. How will Excel compute the following function:

=CONCAT(A1,” “,UPPER(B2))

Convert “University” to upper-case, i.e. “UNIVERSITY” first, and then add this text to the end of “Macquarie ” to result in: “Macquarie UNIVERSITY”.
Convert “University” to upper-case, i.e. “UNIVERSITY” first, and then add this before the text “Macquarie ” to result in: “UNIVERSITY Macquarie”.

Convert “Macquarie” to upper-case, i.e. “MACQUARIE” first, and then add this text to the end of “University”, to result in: “University MACQUARIE”.
Add the text “Macquarie ” to the text “University” first, and then convert the whole text to upper-case, i.e. “MACQUARIE UNIVERSITY”.

Q3. Cell A3 contains “Chairs” and cell B3 contains “Samantha” and Uma would like cell C3 to contain “Samantha CHAIRS”. Which of these will let her do this?

(Multiple answers possible. Partial credit awarded).

=CONCAT(A3,” “,UPPER(B3))
=CONCAT(B3,” “,UPPER(A3))
=B3&” “&UPPER(A3)
=UPPER(CONCAT(B3,” “,A3))

Q1. What formula would return the first 5 letters of the text in cell A1?

=LEFT(A1,1-5)
=LEFT(5,A1)
=LEFT(A1,1,5)
=LEFT(A1,5)

Q2. An ISBN is a number that identifies a particular edition of a book. There are 2 lengths of ISBN: ISBN-13 which is 13 characters long and ISBN-10 which is 10 characters long. You can convert from an ISBN-13 to ISBN-10 by removing the first 3 numbers. In cell A2 you have the ISBN-13 9780747532699. How would you convert this to an ISBN-10 (0747532699)?

=LEFT(A2,3)

=MID(A2,3,10)
=RIGHT(A2,10)

Q3. Cell A2 contains the US telephone number (939) 555-0113. What will be displayed using the formula =MID(A2,7,3)?

There are 2 lengths of ISBN: ISBN-13 which is 13 characters long and ISBN-10 which is 10 characters long. You can convert from an ISBN-13 to ISBN-10 by removing the first 3 numbers.

Q1. If cell A1 contains the text “John Smith”, why does the formula: =LEFT(A1,FIND(” “,A1)-1) return the word “John”?

In fact, why does this formula always return the first word, if cell A1 contains two words separated by a space, ” “?
The outermost function, LEFT, actually extracts the first word, such as “John” in this example, on its own, and the innermost function, FIND, is redundant.
The innermost function, FIND(” “,A1), actually extracts the first word, such as “John” in this example, on its own, and the outermost function, LEFT is redundant.
Here LEFT is being used as a helper function for the FIND function. The helper function, LEFT, finds the location of the space character, ” “, and this function in turn speaks to the FIND function, to extract all the text from the left until this space.
Here FIND is being used as a helper function for the LEFT function. The helper function, FIND, finds the location of the space character, ” “, and this function in turn speaks to the LEFT function, to extract all the text from the left until this space.

Q2. When using the FIND function and the second argument for ‘within text’ is: “Microsoft Excel”, which of the following are correct?

(Multiple answers possible. Partial credit awarded)

=FIND(“o”, “Microsoft Excel”, 6) returns 7
=FIND(“o”, “Microsoft Excel”) returns 7
=FIND(“T”, “Microsoft Excel”) returns 9
=FIND(“e”, “Microsoft Excel”) returns 14

Q3. Suppose that cell A1 contains the text “Microsoft Excel”, What would the following formula return: =FIND(“Excel”,A1)?

Q4. Postcodes in the UK look like SW1A 1AA. This consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters. Which of the following will extract the Outward and Inward Codes from this list?

You can try it yourself using the attached file (Quiz Premier League.xlsx).

(Multiple answers possible. Partial credit awarded).

Outward Code: =LEFT(C2,3)
Inward Code: =RIGHT(C2,FIND(” “,C2)-1)
Outward Code: =MID(C2,FIND(” “,C2)-1,1)
Inward Code: =MID(C2,3,FIND(” “,C2)+1)
Outward Code: =LEFT(C2,FIND(” “,C2)-1)
Inward Code: =RIGHT(C2,3)
Outward Code: =MID(C2,1,FIND(” “,C2)-1)
Inward Code: =MID(C2,FIND(” “,C2)+1,3)

Q1. Use the YEARFRAC function to work out how many years difference is there between the date 1980-07-31 (31 July 1980) and 1998-05-02 (2 May 1998) (to 1 decimal place)?

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q2. The keyboard shortcut Ctrl+; is the same as using the TODAY() function. (Note: This is in the Toolbox for this week)

True
False

Q3. Here is a super challenge for you if you feel up for it.

(You will need to work this out in Excel.)

In 2017 the Hindu festival of Diwali occurred on Thursday, 19 October. What fraction of the year was this? (to 1 decimal place). Hint: Think carefully which start and end dates to use.

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Quiz 2: Test your Skills: Text and Date Functions

Q1. It is necessary to provide more meaningful labels for boxes of items in stock. The first tab of the attached workbook contains the inventory items. You will need to generate the information needed on the For Printing tab using a range of text functions.

On the Inventory tab enter a formula in J1 that will return the current date but not time. What function did you use?

Please enter just the function name all in UPPERCASE letters with no equal sign, brackets or arguments.

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q2. In J4 calculate the number of days since the last order, copy the formula down to J35. What is the check digit value in G1?

Please enter just the number without decimal places, e.g. ###.

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q3. In the For Printing worksheet in A5, generate a part code using “SKU-” followed by the Material Code, another “-” and the Part Number, e.g. SKU-07-2425. Copy the formula down. What is the value for Check Digit 1 (cell H5)?

Please enter just the number ####.

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q4. In B5 we need to see the material in upper case. Copy the formula down. What is the value for Check Digit 2 (cell H6)?

Please enter just the number ####.

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q5. In C5 we want to create a Distributor Code, which is the first 5 characters of the Distributor Name. Which function did you use?

Please enter just the function name all in UPPERCASE letters with no equal sign, brackets or arguments.

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q6. To avoid confusion, make a change to the formula in C5 so that all Distributor Codes show in all lower case. Copy the formula down. What is the value for Check Digit 3 (cell H7)?

Please enter just the number.

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q7. Many of the part descriptions are too long, so in D5 create a calculation to get the first 8 characters of the part description and then copy the formula down. That’s not ideal, so we want to change it to get all the text to the left of the first comma. Which function will allow us to locate the position of the first comma?

Please enter just the function name all in UPPERCASE letters with no equal sign, brackets or arguments.

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q8. Change the calculation in D5 to retrieve the part description up to the first comma. Copy the formula down. What is the value of Check Digit 4?

Please enter just the number ####.

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q9. The Distributor Address is in the format: “Street Address, Postcode, State”. In E5 enter a calculation that will retrieve just the post code from the Distributor Address. Which of the following combination of functions could you use to achieve this?

RIGHT and CONCAT
MID and FIND
LEFT and CONCAT
LEFT and FIND

Q10. Change the calculation in E5 to also include the State before the postcode, e.g. NSW2007. Copy the formula down. What is the value of Check Digit 5?

Please enter just the number ####.

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Excel Skills for Business: Intermediate I Week 2 Quiz Answers

Quiz 1: Introducing Named Ranges

Q1. Which of the following named ranges is valid?

Sales 2016
_Sales2016
Qtr1
Quarter-1

Q2. Named ranges behave like absolute cell references

True
False

Q3. Named ranges always contain a single cell.

True
False

Q4. Which of the following are valid characteristics of names for named ranges?

(Multiple answers possible. Partial credit awarded)

Underscores
Restricted length (no more than 255 characters)
Letters
Labels used in cell references
Hyphens

Q5. Uma wants to create a name for the range showing the Expenses for 2017. She types in Exp2017 but Excel rejects the name. What is the problem?

Exp2017 is a cell reference
Exp2017 is a mix of letters and numbers which is not permissible for names
Exp2017 is a heading in the document so it cannot be used as a name

Q6. We can use the name box to:

(Multiple options possible. Partial credit awarded)
Navigate to the range of an existing named range.
Change the name of an existing named range.
Name a new named range.

Q1. Which of the following does the Name Manager allow us to do?

(Multiple answer possible. Partial credit awarded)

Change the scope of a named range.
Delete a named range.
Change the range of a named range.
Add a named range.

Q2. The same range can have more than one name

True
False

Q3. The Name Manager tool can be found on which ribbon tab?

Formulas
Insert
Data
Home

Q4. Uma and Sean started their Monday morning with an argument. Uma thinks if you accidentally delete the wrong named range there is no need to worry, Excel will replace any range in a calculation that depends on this range with cell references. Sean strongly disagrees and suggests that the calculation will break down. Who is right?

Uma
Sean

Q5. How do you find out whether there are any named ranges in a workbook?

(Multiple options possible. Partial credit awarded)

Click on the Name Manager.
Click on the Name Box.
Press F4.

Quiz 2: Test your Skills: Named Ranges

Q1. A travel expenses template has been compiled for your company to make it easier for staff to record their expenses when travelling to branches in other countries. It has recently been updated and the named ranges have been damaged in the process so most of the formulas are returning an error. You need to correct the named ranges to fix the problem.

Have a look at the Travel Expense worksheet. Note there are quite a few errors. Start by addressing the problem of the missing exchange rates by naming the ranges. Go to the Currency Rates worksheet and use Create from Selection to name all the rates (A4:B12) using the labels in column A.

What value is now showing for the Other Expenses in K6?

Don’t enter the currency symbol – please enter the number as ##.##

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q2. While the calculation of Other Expenses is looking better it is still not correct. Open the Name Manager. There is a range called Coffee that is no longer used, so delete it. Now have a look at Ex_Rate, it only goes to row 14, which explains the incorrect calculation. Edit it to go from L11:L21. Click OK and close the Name Manager.

What is the corrected value for Other Expenses in K6?

Don’t enter the currency symbol – please enter the number as ##.##

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q3. Let’s fix Travel Costs next. Open the Name Manager, there is a named range called Travel_Costs, but this is the wrong name, change it to TravelCosts and click OK and close the Name Manager.

What is the corrected value for Transportation Expense in K3?

Don’t enter the currency symbol – please enter the number as ##.##

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q4. Next, Lodging Costs. Use any method you think suitable to give the name Lodging_Costs to range F11:F21. What is the corrected value for Lodging Expense in K4?

Don’t enter the currency symbol – please enter the number as ##.##

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q5. And now to fix meals, let’s be efficient and use Create from Selection to name all three ranges simultaneously. Select G10:I21 and click Create from Selection. What is the corrected value for Meal Expense in K5?

Don’t enter the currency symbol – please enter the number as ##.##

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q6. Our Travel Expense worksheet is now looking good, but we would also like to complete a breakdown of expenses by region. Start by adding the following named ranges:

E11:J14– London
E15:J18– Paris
E19:J21– Mumbai

Now go to the Summary By Region worksheet and observe the calculated values for London. What was the total amount spent in London in Pounds (C4)?

Don’t enter the currency symbol – please enter the number as ##.##

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q7. Enter a formula in C5 to add up the total amount spent in Paris (use the named range you have just created). Then do the same in C6 for Mumbai.

What was the total amount spent in Paris in Euros (C5)?

Don’t enter the currency symbol – please enter the number as ##.##

Q8. In D5 create a calculation to convert Euros to Dollars by multiplying the Euros spent (C5) by the exchange rate for Euro (which is named EUR). Perform a similar calculation to convert the Indian Rupees to Dollars.

What was the total USD spent in Mumbai?

Don’t enter the currency symbol – please enter the number as ##.##

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Q9. Click in D7 and use Autosum to get the total spent in USD. If we were to now name the range D4:D6, would the formula just created in D7 automatically change to use the named range? You should not actually create this named range.

Yes, it would change immediately.
Yes, but only when the formula is next refreshed.
No, but you could use the Apply Named Range to apply it.
No, you would have to manually change the formula.

Q10. Click in B9 (still in Summary By Region), and use the Paste Names tool to Paste all the named ranges into your workbook. What value is in B23?

consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters

Excel Skills for Business: Intermediate I Week 4 Quiz Answers

Quiz 1: COUNT functions

Q1. Given the following information, what would Excel return as the result for cell B10, if the formula was input as =COUNT(B2:B8)? Excel Skills for Business: Intermediate I Quiz Answers

Q2. Given the following information, what would Excel return as the result for cell B10, if the formula was input as =COUNTA(B2:B8)? Excel Skills for Business: Intermediate I Quiz Answers

Q3. Given the following information, what would Excel return as the result for cell B10, if the formula was input as =COUNTBLANK(B2:B8)? Excel Skills for Business: Intermediate I Quiz Answers

Q4. For the following data, what formula(s) could you use to count the number of days that are being specified in Column A?

(Multiple answers are possible. Partial credit will be awarded) Excel Skills for Business: Intermediate I Quiz Answers

=COUNTBLANK(A2:A8)
=COUNTA(A2:A8)
=COUNT(A2:A8)
=COUNTDATE(A2:A8)

Q1. For the following information, what formula would you type into cell C12, to return the total number of calls made by Yeheli? Excel Skills for Business: Intermediate I Quiz Answers

=SUMIFS(C2:C9,B2:B9,”Yeheli”)
=SUMIFS(B2:B9,”Yeheli”)
=SUMIFS(B2:B9,C2:C9,”Yeheli”)
=SUMIFS(C2:C9,”Yeheli”)

Q2. For the following information, what formula would you type into cell C13, to return the total number of calls made by Rahul? Excel Skills for Business: Intermediate I Quiz Answers

=SUMIFS(B2:B9,B2:B9,”Rahul”)
=SUMIFS(C2:C9,C2:C9,”Rahul”)
=SUMIFS(B2:B9,C2:C9,”Rahul”)
=SUMIFS(C2:C9,B2:B9,”Rahul”)

Q3. What would the following formula return: “=SUMIFS(C2:C9,B2:B9,”Yeheli”,A2:A9,”15/02/2013″)” ? Excel Skills for Business: Intermediate I Quiz Answers

Q4. For the following information, what would the formula below return: “=SUMIFS(C2:C9,B2:B9,”Yeheli”,B2:B9,”Rahul”)” ? Excel Skills for Business: Intermediate I Quiz Answers

We have a chart where the data approximately follows a straight line, which trendline would be the best fit?

Power
Logarithmic
Linear
Exponential

Q2. A graph shows a dramatic increase in sales for the first year followed by a steady levelling off in subsequent years. Which trendline would be the best fit?

Exponential
Power
Linear
Logarithmic

Q3. Of the values below, which R-squared value suggests the best fit?

1.980
0.981
0.198

Q4. Another challenge for you to solve in Excel. Type in the data and create a line chart for Revenues – then solve this question: What is the R-squared value of a linear trendline? Excel Skills for Business: Intermediate I Quiz Answers

1.0000
0.3617
0.0000
0.4159

Q5. Another challenge building on the data we got you to type into Excel for the previous question. Create a line chart for Revenues – then solve this question. What is the R-squared value when creating an exponential trendline? Excel Skills for Business: Intermediate I Quiz Answers

0.0000
0.4159
0.3617
1.0000

Quiz 2: Test your Skills: Summarising Data

Q1. The attached workbook is needed to answer all the s associated with this quiz.

Use Create from Selection to name each of the columns of data in the Ealing Property Sales sheet.

Check the Name Box to see all your named ranges have been created correctly. What name has been applied to the data in Column D?

Year-Sold
Year_Sold
YearSold
Year Sold

Q2. In C3 use a COUNT function to count the values in the named range ID.

What answer does the COUNT function return?

Please enter just the number.

the total Price Paid for all Terraced properties sold in 2014. Make any necessary adjustments and then drag the formula down

Q3. This is not the result we were hoping for, look carefully at the ID column, can you see why we got this answer?

Why did the COUNT return this result?

Because some IDs have spaces
Because some IDs are blank
Because some IDs contain text characters
Because some IDs are invalid

Q4. Have a look at column J (Flat Number), note that a lot of the cells are blank.

Which function would you use to count the number of blank cells in a column?

Please enter just the function name all in UPPERCASE letters with no equal sign, brackets or arguments.

the total Price Paid for all Terraced properties sold in 2014. Make any necessary adjustments and then drag the formula down

Q5. On the Summary Data sheet, in cell B4, use a function to sum the Price Paid for all properties of type Terraced. Copy the formula down.

What was the total Price Paid for Flats?

Don’t enter the currency symbol or decimal points, just the plain number of the format #####

the total Price Paid for all Terraced properties sold in 2014. Make any necessary adjustments and then drag the formula down

Q6. In C4 create a formula to sum the total Price Paid for all Terraced properties sold in 2014. Make any necessary adjustments and then drag the formula down and across to complete the table.

Which of these formulas is correct?

=SUMIFS(Price_Paid,Property_Type,$A4,Year_Sold,C$3)
=SUMIFS(Price_Paid,Property_Type,$A$4,Year_Sold,C3)
=SUMIFS(Price_Paid,Property_Type,$A$4,Year_Sold,C$$3)
=SUMIFS(Price_Paid,Property_Type,A4,Year_Sold,$C$3)

Q7. In F4 create a sparklines showing the sales trends for terraced houses from 2014 to 2016. Copy the sparkline down to F8.

Which of these property types follows a completely different trend to the others?

Semi
Other
Flat
Detached

Q8. Click in A12. Note the drop down that allows you to select different Towns, leave it set to London. In B13 create a calculation that will show the number of properties sold in the selected town for July 2015. (Note you will need to add criteria to check Year Sold and Month Sold). Copy the formula down to get results for the other months.

Which Month had the lowest number of sales?

Type out the full name of the month.

June

Q9. In C13 create a formula to sum the total price paid for properties sold in the selected region (London) for July 2015. (Note you will need to add criteria to check Year Sold and Month Sold.) Copy the formula down to get results for the other months.

Which Month had the second lowest total sales?

Type out the full name of the month.

July 2016

Q10. Select the range A12:C18 and create a line chart. Put the Total Sales series on a secondary axis and change it to a Clustered Column chart.

Which of the following most closely resembles your chart?

Option 1
Option 2
Option 3
Option 4

Q11. In your new chart select the Number Sold series and add a trend line. Show the R² value. Compare the results you get from the different trend line options.

Which of the following trendline options yields the best R² value?

Exponential
Linear
Logarithmic
Power

Q12. Change your trendline to a Polynomial Order 2.

What R² value does the Polynomial Order 2 show?

0.6789.

Excel Skills for Business: Intermediate I Week 5 Quiz Answers

Quiz 1: Creating and Formatting Tables

Q1. You need to select all your data before converting it to a table.

True
False

Q2. The name of a table can be changed in the following way:

Click the Tables Tools Design tab that appears when clicked anywhere in the table and navigate to Table Name in the ribbon.
Click anywhere in the table and without going to the Design tab, find Table Name in the ribbon.
Right click anywhere in the table, go to Table in the context menu and find Table Name.

Q3. In tables, named ranges will automatically extend when you add an extra row/column.

True
False

Q1. You can access the sorting and filtering tools in the following ways:

(Multiple answers possible. Partial credit will be awarded).

In the right-click menu.
Data tab in the ribbon.
Going to the Table Tools Design tab that appears when you are clicked anywhere on the table and navigating to Sort.
Filter buttons found in the header row of the table.

Q2. Uma told Sean that Excel automatically recognises that some columns contain a certain kind of format e.g. (date format, text format) and provides useful filters in light of this e.g. text filters for fields containing text. Is Uma right?

Yes
No

Q3. Using the attached Workbook:

Go to the Table Tools Design tab which appears when clicked on the table and go to Table Style Options and check (tick) the Total Row. Now, go to the Total Row in your table, hover to Annual Salary, press the drop down button and go to StdDev.

What value do you get?

1958400
184 213 731.09
13572.54

Q1. You can convert a table to a range by:

(Multiple answers are possible. Partial credit will be awarded).

Going to the Data tab in the ribbon and navigating to Convert to Range.
Going to the Formulas tab in the ribbon and navigating to Convert to Range.
Right click context menu and going to table then Convert to Range.
Navigating to the Table Tools Design tab while clicked on the table and going to Convert to Range in Tools.

Q2. What property of subtotal reports makes it important to name the sheet on which the subtotal report is produced?

Subtotal reports can easily be mistaken for something else.
Subtotal reports contain important information and must be named.
The report is a snapshot of the database and won’t be updated should the database change, so a useful name can remind you of this.

Q3. Which of the following steps should you always take before you use the Subtotal feature in Excel?

Sort your data.
Convert your data to a Table.
Add a new row to display the subtotal.
Filter your data.

Quiz 2: Test your Skills: Tables

Q1. The attached workbook is needed to answer all the s associated with this quiz. Please download the file and open it in Excel before you start answering the s below.

Is the Supplier Phone number data (O3:P10) in a Table?

Q2. Convert the Discount Code data (O13:P15) to a table.

Add a new record directly below the table as follows:
Code: C
%: 15%
What is the corrected Average Discount % shown in P19?

Don’t enter the percentage symbol – please enter only the number as #.##

Q3. Convert the Homewares Inventory data to a table. Apply the table style White, Table Style Light 18. Turn off banded rows and turn on banded columns.

Which of the following most closely resembles your table?

This one:
This one:
This one:
This one:

Q4. Turn on the Total Row. Change the calculation for the Retail Price column to calculate the average retail price.

What is the average retail price?

Do not enter the currency symbol, please enter just the numeric value #.##

Table Tools Design tab which appears when clicked on the table and go to Table Style Options and check (tick) the Total Row. Now, go to the Total Row in your table, hover to Annual Salary, press the drop down button and go to StdDev.

Q5. The In Stock column displays the number of items in stock for each product. Using the Total Row to add a calculation, how many items are currently in stock for all products?

Q6. You have been informed that there may be duplicates in the data. Use the Remove Duplicates tool to remove any duplicate entries.

What is the corrected average retail price?

Do not enter the $ symbol, please enter just the numeric value #.##

Q7. You need to find out which products need re-ordering most urgently. Sort the data by the column In Stock from Smallest to Largest.

Enter the full product code for the product with the smallest number in stock.

Q8. You are doing an audit of products supplied by the company Kestrel. Filter the data by the supplier Kestrel.

What is the updated value for the total number of items in stock now?

Please enter just the number ###.

Q9. Clear the filter on Supplier and add a new filter to get the top 5% of stock items by retail price.

What is the average retail price for these items?

Don’t enter the currency symbol, please enter just the numeric value #.##

Q10. Clear all filters and turn off the total row. In the first empty row add the following record:

Product Code	HARD-026
Item Description	Chrome-Plated Face Frame Hinge
Supplier	PHISION
Department	Hardware
Origin	China
Location	Showroom
Rack	02
In Stock	100
Target Level	100
Reorder Level	50
Discount %	(Copy down from the row above)
Unit Cost	$6.32
Retail Price	$7.49

Turn the total row back on. What is the updated Average Retail Price?

Do not enter the currency symbol, please enter just the numeric value #.##

Excel Skills for Business: Intermediate I Week 6 Quiz Answers

Quiz 1: Creating and Modifying a Pivot Table

Q1. When you create a Pivot Table, your data must be in a Table beforehand.

True
False

Q2. You can only have one Pivot Table in a worksheet.

True
False

Q3. What combination of settings was used to create this Pivot Table?

This:
This:
This:
This:

Q4. Uma has just watched the video on creating Pivot Tables and she is excited that you don’t need to highlight the whole data range to create a pivot table, all you need to do is make sure that you are clicked on one of the cells in the data range. Has Uma understood the topic well?

Yes, of course you have to indicate to Excel where to find the data by being clicked in one of the cells that contains the data.
No, you can just specify the table/range and Excel will know where the data is located.

Q5. Which of the following is NOT part of a Pivot Tables?

(Multiple answers possible. Partial credit awarded).

Calculated item
Slicers
Sparklines
Calculated Field

Q1. Using the attached Workbook:

Create a filter by Customer Type. (Hint: drag Customer Type into Report Filter).

Filter by Home Office and Small Business. What is the total for 2016 Qtr 1 in VIC?

1481.08
12942.20
1308.49

Q2. Using the same Pivot Table as you did in 1, select Show Report Filter Pages. Which of the following is true?

Two new worksheets with new Pivot Tables have been added, one showing data for Home Office and one for Small Business.
A new worksheet with new Pivot Tables has been added for each of the Account Managers.
A new worksheet with new Pivot Tables has been added for each of the Customer Types.

Q3. Uma suspects that the figures for 2015 Qtr 1 may be incorrect due to missing sales entries. How can Uma quickly investigate this value in the Pivot Table?

Uma can double-click on the cell of interest to generate a list of all the records that yielded that value.
Uma will have to go back to the original source data.
Uma can right click on the cell of interest and select Value Field Settings (Field Settings in Mac).

Q4. Uma has accidentally closed her field list. How can she get it back?

Click away from the PivotTable and then click back on it.
Navigate to the PivotTable Analyse tab and click Field List
Double click on any cell in the pivot table.

Q1. Uma has just created a chart, however she doesn’t like how it looks like she feels that the legend and x-axis have been swapped. How can she rectify this problem?

(One or more answers are possible — partial credit will be awarded)

Right-click on the chart and navigate to Select Data, click on it and an option to Switch Row/Column will be found.
Whilst clicked on the chart go to the field list and drag the fields between the Legends fields and axis fields.
Whilst being clicked on the chart, go to the Analyze tab in the ribbon and navigate to Switch Row/Column Data.
Whilst being clicked on the chart, go to the Design tab in the ribbon and navigate to Switch Row/Column Data.

Q2. Uma can remember that when you create a Pivot Chart the Field filters appear on the Pivot Chart. Sean has just walked in talking about how useful Report Filters are. Uma now cannot recall if Report Filters also appear on the Pivot Chart. Do they? (Hop over to Excel and create a chart for a table that contains a report filter if you are confused as well).

Yes
No

Q3. Uma and Sean have not had a relaxing Friday, they cannot agree on whether it is possible to create a Pivot Chart by being clicked on the Pivot Table and navigating to the Insert tab and selecting a Chart under the Chart Tools. Is this possible?

Yes, this works fine
No, you have to the Analyze tab that appears when clicked on a Pivot Table.
No, this does create a chart but the chart isn’t responsive to the Pivot Table so is arguably pointless.

Q1. To connect a Slicer to a Pivot Table you have to be clicked on the pivot table.

Q2. Sean has asked you to briefly explain to him what a Slicer is. What is the best response?

Slicers are a very complicated concept that has been added to pivot tables in the newer versions of Excel, it is difficult to understand them and they are arguably pointless.
A Slicer is a filter that has been made user-friendly and now anyone can use it with ease by just clicking on what they want the spreadsheet to filter by and the pivot table and charts (dashboard) display accordingly.
Slicers are a more efficient way of creating pivot charts and pivot tables.

Q3. What options do you find under the Options tab which appears when clicked on a Slicer?

Slicer Caption
Field List Options
Report Connections
Change Data Source

Q1. The attached workbook is needed to answer all the s associated with this quiz.

Before creating pivot tables it is always a good idea to put your data into a table. Convert the sales data to a table. Rename the table Sales. Turn on the Total Row and in the total row in the Price Paid column select Average to get the average price paid.

What was the Average Price Paid?

Don’t enter currency symbol, please enter just the number ######.##

PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter

Q2. Turn off the Total Row. Create a Pivot Table in a new sheet to show the Total Price Paid for each Town.

What does the Pivot Table show as a total for Southall?

Please enter just the number, no currency symbol or decimals.

PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter

Q3. Modify the Pivot Table to show Year Sold in the columns.

What were the total sales for Northholt for 2016?

Please enter just the number, no decimals.

PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter

Q4. Change the value field settings to show the calculated values as a percentage of the Grand Total.

What percentage is shown for London in 2014?

Don’t enter the percentage symbol, just the plain number with two decimal places – ##.##

PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter

Q5. Still working in Sheet1, click into cell A15, and create a Pivot Table from your Sales data that shows total Price Paid for each Property Type. Rename the pivot PropertyType.

What was the Total Price Paid for Semi properties?

Please enter the plain number, with no currency symbol or decimals.

PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter

Q6. In the PropertyType pivot change the Summarise Values By setting to use a Count instead of a Sum.

How many Flats were sold?

PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter

Q7. Click in the PropertyType pivot and in the PivotTable field settings tick the Estate Type checkbox.

How many Leasehold Terraced properties were sold?

PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter

Q8. In the PropertyType pivot add Year Sold to the Filters section in the PivotTable field list. Change the filter to only show properties sold in 2014 and 2015.

How many Freehold Terraced properties were sold in this time period?

PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter

Q9. Still working in the PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter. If necessary expand the Year group for 2015.

How many Freehold properties sold in Quarter 3 of 2015?

PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter

Q10. Add a slicer to filter the PropertyType pivot by Postcode. Select all postcodes that begin with UB1 or UB2.

How many Leasehold properties sold in Quarter 2 of 2015 for these postcodes?

PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter

Quiz 2: Final Assessment

Q1. Have a look at the first 3 worksheets, they contain student marks for 3 terms. Now go to the Final Marks worksheet and use 3D-Formulas to get Benjamin Abbot’s class test average for terms 1, 2 and 4. Copy the formula across to I4 and then down for the rest of the students. What was the Average Final Mark (as shown in cell M4)?

Please enter the number with one decimal ##.#

Clustered Column pivot chart using the data in the second pivot table (if you have Excel for Mac select the data in A17:B20 and just create a regular chart). Add a linear trendline and display the R-squared value on the chart

Q2. Note that you have a sheet called Marks Term 3 but it is not in the right position. Move this sheet to sit between the sheets Marks Term 2 and Marks Term 4. Check the Final Marks Sheet, what is the average Final Mark now?

Please enter the number with one decimal ##.#

Q3. Select the range A3:J465 and use Create from Selection to name each of the columns of data. This should have corrected the missing stats figures. What was the median Final Mark (M5)?

Please enter the number with one decimal ##.#

Q4. Select the range L20:M26 and name it Grades. This should have corrected the grades calculations. What grade did Olivia Jones get?

Q5. In M10 use a formula to calculate the total number of Fail grades. Copy the formula down to M16. Note cell P4 which displays the Total Number of students who achieved a “C” should have changed colour. What colour is the cell?

Orange
Green
Yellow
Blue

Q6. In N10 create a mixed reference formula that will count how many of Mr Chang’s students got a Fail. Drag the formula down and across to complete the table. Observe P5, which shows the number of A’s achieved by Ms Sekibo’s students. It should have changed colour. What colour is it now?

Orange
Yellow
Green
Blue

Q7. Have a look at the worksheets Absences Term 1 through to Term 4, they contain a list of dates that students were absent. We need to create a summary showing a count of how many days each student was absent. Go to the Absence Report Sheet. Click in A4, and then use the Consolidate tool to consolidate the data on the other Absences sheets. The results look a bit odd, but that is because the count values have been formatted as dates. Change the formatting to General or Number. Sort the data by Total Absences. How many students were absent for more than 15 days?

Q8. Go to the Student Report worksheet. Some of the information still needs to be completed. Create a formula in D4 to return the Student’s full name, this should be First Name followed by a space and then Surname. The case must also be corrected so that all words start with a capital letter but everything else is in lower case e.g., Benjamin Abbot. Copy the formula down for all the other students. What is the value of the check digit in S4?

Q9. In E4 create a formula to generate the student email address. This should be their first initial, followed by their surname, followed by “@newcollege.com”, and must all be in lower case, e.g. [email protected]. Copy the formula down for all the other students. What is the value of the check digit in S5?

Q10. The last two digits of the student number indicate the year the student enrolled. In F4 create a formula that will put “20” followed by the last two digits of the Student ID, e.g. 2015. Copy the formula down for all the other students. What is the value of the check digit in S6?

Q11. We would like to get an idea of how students have progressed over the year. Click in M4 and create a sparkline line chart that charts the data in cells I4:L4. Copy the sparkline down for all the other students. Change the sparkline to show the highest point. Which of these sparklines represents Olivia Jones’ data?

This:
This:
This:
This:

Q12. Convert the data in the Student Report Sheet to a table. Name the table Report and change the style to Green Table Style Medium 21. Which of the following styles did you choose?

This:
This:
This:
This:

Q13. With the table still selected, turn on the Total Row. What are the Total Fees Owing?

Don’t enter the currency symbol, just the number and decimal places, e.g. #######.##

Q14. In the Total Row in the Year Enrolled column, chose the correct function to calculate the number of all students enrolled. How many are there?

Q15. Filter the table to show all Distance Learning students who owe more than $9,000. How many are there?

Q16. We would like to compare the results for different types of students. Clear all filters. Use the data in the table to create a pivot table (in a new sheet) that shows Grade in the Row Labels, Student Type in the Column Labels, and Count of Grade in the Values section. How many A’s did the Part Time Students get?

Q17. Change the pivot to show the values as a percentage of the column total. What percentage of Part Time students failed?

Don’t enter the percentage symbol, please just enter the number as ##.## (2 decimal places).

Q18. Mr Chang has observed that the students attending the college seem to be increasingly more able and more motivated. He would like to see if there is a pattern in the results based on enrolment date. Click in A17 and create another pivot table to show the average final mark by enrolment date. Add a filter field and change the filter to only show data for Mr Chang. Format the values to only show 2 decimal places. What was the Average mark for 2017?

Please enter the number with two decimal places.

Q19. Create a Clustered Column pivot chart using the data in the second pivot table (if you have Excel for Mac select the data in A17:B20 and just create a regular chart). Add a linear trendline and display the R-squared value on the chart. What is the R-squared value?

Please enter the number as #.#### (4 decimal places).

Q20. Have a look at the other trend line options and select the one that returns the best R-squared value. Forecast forward for 1 period. If the trend continues, students who enrol in 2018 are expected to get an average result closest to…

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

All Weeks Excel Skills for Business: Intermediate I Quiz Answers

Excel Skills for Business: Intermediate I Week 1 Quiz Answers

Quiz 1: Multiple Worksheets

Quiz 2: Test your Skills: Working with Multiple Worksheets & Workbooks

Excel Skills for Business: Intermediate I Week 2 Quiz Answers

Quiz 1: Changing Text Case (UPPER, LOWER, PROPER)

Quiz 2: Test your Skills: Text and Date Functions

Excel Skills for Business: Intermediate I Week 2 Quiz Answers

Quiz 1: Introducing Named Ranges

Quiz 2: Test your Skills: Named Ranges

Excel Skills for Business: Intermediate I Week 4 Quiz Answers

Quiz 1: COUNT functions

Quiz 2: Test your Skills: Summarising Data

Excel Skills for Business: Intermediate I Week 5 Quiz Answers

Quiz 1: Creating and Formatting Tables

Quiz 2: Test your Skills: Tables

Excel Skills for Business: Intermediate I Week 6 Quiz Answers

Quiz 1: Creating and Modifying a Pivot Table

Quiz 2: Final Assessment

Get All Course Quiz Answers of Excel Skills for Business Specialization

Newsletter Updates

Leave a ReplyCancel Reply

Related Posts

Critical Thinking: Reasoned Decision Making Week 03 Quiz Answers

Positive Psychology: Resilience Skills Coursera Quiz Answers

Positive Psychology: Character, Grit and Research Methods Quiz Answers

Trending now