Excel Fundamentals for Data Analysis Coursera Quiz Answers

Get Excel Fundamentals for Data Analysis Coursera Quiz Answers

Enroll on Coursera

Excel Fundamentals for Data Analysis Coursera Quiz Answers

Week 1: Excel Fundamentals for Data Analysis

Quiz 1: Excel functions for combining text values

Q1. The worksheet contains the following data:

What will be the result of the formula =CONCAT(B2:D2)?

• #VALUE!
• HSBC,Shanghai,China
• HSBC, Shanghai, China
• HSBCShanghaiChina

Q2. The worksheet contains the following data:

What will be the result of the formula =TEXTJOIN(“,”,TRUE,B2:D2)?

• HSBC,Shanghai,China
• HSBC, Shanghai, China
• #VALUE!
• HSBCShanghaiChina

Q3. The worksheet contains the following data:

Which of the following formulas will give the result: HSBC Shanghai China?

• =B2&””&C2&””&D2
• =B2&C2&D2
• =B2&” “&C2&” “&D2
• =B2&”,”&C2&”,”&D2

Q4. The worksheet contains the following data:

Zara wants to join the value in B2 and the value in C2 separated by a space to get HSBC Shanghai. She enters the following formula:

=CONCATENATE(B2, ,C2)

She gets an error. Which of the following will correct the error and return the desired result?

• =CONCATENATE(B2,” “,C2)
• =CONCATENATE(B2 ” ” C2)
• =CONCATENATE(” “,TRUE,B2:C2)
• =CONCATENATE(B2,’ ‘,C2)

Quiz 2: Functions that split text data

Q1. The worksheet contains the following data:

What will the formula =LEFT(B2,4) return?

• BUSA
• 3015
• #VALUE!

Q2. The worksheet contains the following data:

What will the formula =RIGHT(B2,3) return?

• 301
• #VALUE!
• 015

Q3. Cell B2 holds the data: BUSA3015

What will the formula =MID(B2,4,2) return?

• 30
• A3
• #VALUE!

Q4. The workbook contains the following data:

What will the formula: =CONCATENATE(LEFT(B2,4),RIGHT(B3,4)) return?

• BUSAMQBS
• BUSA3010
• MQBS3015
• 30103015

Q5. The workbook contains the following data:

What will the formula: =LEFT(TEXTJOIN(“,”,TRUE,B2,B3),4) return?

• BUSAMQBS3010
• BUSA3015MQBS
• BUSA
• BUSA3015MQBS3010

Quiz 3: Combining text functions

Q1. The worksheet contains the following data:

What is the result of the formula: =FIND(“university”,B2)

• university
• 11
• Y20
• uni

Q2. The worksheet contains the following data:

What is the result of the formula: =LEN(B2)+LEN(B3)

• macuni
• #VALUE!
• mac uni
• 6

Q3. The worksheet contains the following data:

What is the result of the formula: =FIND(“Australia”,B2,12)

• 4
• 35
• 2
• #VALUE!

Q4. The worksheet contains the following data:

What is the result of the formula: =MID(B2,6,FIND(“-“,B2))

• 555-u
• 555-unive
• 555
• #VALUE!

Q5. The worksheet contains the following data:

What is the result of the formula: =CONCATENATE(LEFT(B2,1),LEFT(B3,1))

• ey
• #VALUE!
• mu
• ma

Quiz 4: Cleaning data and changing case

Q1. The worksheet contains the following data:

What will the formula: =TRIM(B2) return?

Q2. The worksheet contains the following data:

What will the formula: =LEN(TRIM(RIGHT(B2,11))) return?

• 10
• university
• 11
• VALUE!

Q3. What does the CLEAN function do?

• The CLEAN function removes unnecessary spaces within a string.
• The CLEAN function removes unnecessary spaces and some non-printable characters within a string.
• The CLEAN function cannot do anything on its own, without the use of the TRIM function as well.
• The CLEAN function removes some non-printable characters within a string.

Q4. The worksheet contains the following data:

Which of the following formulas will return QUA?

• =MID(UPPER(B2),4,3)
• =MID(PROPER(B2,4,3))
• =UPPER(MID(B2),3,4)
• =UPPER(MID(B2,3,4))

Q5. The worksheet contains the following data:

What will the formula: =CONCATENATE(PROPER(B2),” “,B3) return?

Quiz 5: Removing and replacing text characters

Q1. Which Excel function would be most suitable to converting “100 kilos” to display as “100 kg”?

• TRIM
• SUBSTITUTE
• CLEAN

Q2. What would be an appropriate formula using the function SUBSTITUTE to correct the contents of cell B2 which currently is Macquarie|University|Business School by replacing all instances of “|” with ” ” (a space)?

• =SUBSTITUTE(B2,” “,”|”)
• =SUBSTITUTE(B2,” “,|)
• =SUBSTITUTE(B2,|,” “)
• =SUBSTITUTE(B2,”|”,” “)

Q3. What would be an appropriate formula using the function SUBSTITUTE to correct the contents of cell B2 which currently is Macquarie|University|Business School by only replacing the first instance of “|” with ” ” (a space)?

• =SUBSTITUTE(B2,”|”,” “,1)
• =SUBSTITUTE(B2,”|”,” “,2)
• =SUBSTITUTE(B2,”|”,” “)

Q4. You will need to refer to the Tool Box of this course to answer this question. What will the following formula =UNICHAR(65) return?

• Australia
• 65
• A
• 1

Q5. You will need to refer to the Tool Box of this course to answer this question. What will the following formula =UNICODE(“A”) return?

• A
• Australia
• 65
• 1

Quiz 6: Cleaning and manipulating text: Test your skills

Q1. To do this assessment you should download this Excel workbook, follow the instructions, and answer the questions.

C1W1 Assessment
XLSX File
Download file
In column D we need a formula to generate the full name.

Which of the following formulas would return the result Tina DE SIATO in cell D23?

Q2. If we type the formula in cell D4: =PROPER(C4x” “xB4) to achieve the intended result of Question 1 above, what character is required to replace x?

• &
• $
• @
• +

Q3. If we wanted to change the formula in D4 to return Stevie BACATA, which of the following formulas would achieve this?

Q4. If we entered the calculation =PROPER(CONCAT(C4,” “,UPPER(B4))) in D4, what result would it return?

Q5. Fill in the Email column. The format for the email addresses is First Name, a “.”, Last Name, followed by @pushpin.com. It should all be in lower case. For example, Stevie Bacata’s email address is [email protected].

Which of these formulas would create the email addresses correctly? Multiple options may be correct.

Q6. The Location field contains the 2-digit floor number followed by a hyphen, then the wing followed by a space and then the four-digit extension. We need to split them up so that they are easier to access. For example, 02-West 2635 means Floor 02, West Wing, Extension 2635. Which function should we use to extract the floor number?

• LEFT
• CONCAT
• FIND
• SPLIT

Q7. Which formula could we use to return the extension number (the last four digits in column K) for each staff member?

Q8. We would like just the first letter of North or West to indicate the wing in column N. Which formula will give the desired result?

Q9. Since the wings are West and North, which have different numbers of characters, we cannot hard code the number 4. We will need to use a formula that changes with the wing.

What would be the result of the formula: =FIND(” “,K23)?

Enter answer here

Q10. We would like to return the full word of the wing. What formula will give the desired result?

Q11. There are a few names that need to be cleaned up. Bob Decker (row 22) has an unusual character at the end of his name. What is the code for this character?

Enter answer here

Q12. Which function or functions could you use to remove this unwanted character?

• TRIM
• UNICODE
• SUBSTITUTE
• CLEAN

Week 2: Excel Fundamentals for Data Analysis

Quiz 1: Converting Data with VALUE and TEXT

Q1. The worksheet contains the following data:

What will be the result of the following formula: =VALUE(RIGHT(C3,6)) ?

• #VALUE!
• 700.43
• AUD700.43
• D700.43

Q2. The worksheet contains the following data:

What will be an appropriate formula from the list below to return the value: 700.43 as numeric data?

• =(VALUE(C3,”AUD”,””)&SUBSTITUTE(C3,”AUD”,””)
• =VALUE(SUBSTITUTE(C3,”AUD”,””))
• =SUBSTITUTE(VALUE(C3,”AUD”,””))
• =SUBSTITUTE(C3,”AUD”,””)

Q3. The worksheet contains the following data:

What will be an appropriate formula to convert this data into Wednesday given that the 29th of December 1982 is a Wednesday?

• =TEXT(F3,”DD”)
• =TEXT(F3,DDDD)
• =TEXT(F3,DDDDDDDDD)
• =TEXT(F3,”DDDD”)

Q4. The worksheet contains the following data:

What will be the result of the following formula: =TEXT(F3,”DDD/M/YYYY”) ?

• Wednesday/1/1982
• Wed/12/1982
• Wed/1/1982
• Wednesday/12/1982

Q5. The worksheet contains the following data:

What will be the result of the following formula:

=”On “&TEXT(F3,”ddd/m/yyyy”)&” due “&VALUE(RIGHT(C3,6))

• On Wed/12/1982 due 700.43
• On Wed/12/1982dueAUD700.43
• On Wed/12/1982due700.43
• On Wed/12/1982 due AUD700.43

Quiz 2: Understanding dates and basic date functions

Q1. Currently, cell R2 is formatted as text and currently holds the value 12. If we convert this to a short date format using the day/month/year system, what will be the result?

• 12/01/2000
• 12/01/1900
• 12
• Thursday, 12 January, 1900

Q2. Assume that today is the 1st of January, 2020 and we are using the day/month/year format for dates. What will be the result of the formula: =TODAY()+7

• 1/01/2027
• #VALUE!
• 8/01/2020
• 1/08/2020

Q3. Assume that today is the 21st of January, 2020 and we are using the day/month/year format for dates. What will be the result of: =TODAY()-7

• 14/01/2020
• 31/01/2020
• 21/01/2013
• #VALUE!

Q4. What does the NOW() function do?

• It provides the current time and displays this on the spreadsheet.
• It provides the time that the formula was first typed.
• It is a function that cannot be used by itself.
• It provides the time that the spreadsheet was opened in the current instance.

Q5. The worksheet contains the following data:

The following formulas are typed into the following cells:

1. P2: =DAY(N2)
2. Q2: =MONTH(N2)
3. R2: =YEAR(N2)

What will the cells P2, Q2, and R2 hold respectively?

• 25 | 10 | 2020
• 25 | 10 | 20
• Sunday | October | 2020
• 25 | October | 2020

Q6. Cell N3 currently holds the value: 1/07/2019 (see below), the date that a payment was due (1st of July, 2019).

What would be an appropriate formula from the list below, that returns the number of days overdue? (Note: you have to re-format the cell that contains the result to General)

• =N3-TODAY()
• =DAY(TODAY()-N3)
• =TODAY(N3)
• =TODAY()-N3

Quiz 3: Generating Valid Dates using the DATE function

Q1. The worksheet contains the following data:

What will be the result of the following formula: =MONTH(G3) ?

• #VALUE!
• 04
• 4
• April

Q2. The worksheet contains the following data:

What will be the result of the following formula: =MONTH(1&G3)&”/2020″ ?

• 1/04/2020
• 4/2020
• 1/April/2020
• April/2020

Q3. The worksheet contains the following data:

What would be an appropriate formula from the list below to generate the first day of this month given that the year is 2020?

• =DATE(2020,MONTH(1&B2)&1)
• =DATE(2020&MONTH(1B2)&1)
• =DATE(2020,MONTH(B2),1)
• =DATE(2020,MONTH(1&B2),1)

Q4. The worksheet contains the following data:

What would be an appropriate formula from the list below to generate the last day of this month given that the year is 2020?

• =DATE(2020,MONTH(1&B2)+1,1)+1
• =DATE(2020,MONTH(1&B2)+1,1)-1
• =DATE(2020,MONTH(1&B2)-1,1)-1
• =DATE(2020,MONTH(1&B2)-1,1)+1

Q5. The worksheet contains the following data:

To generate the last day of this month, the following formula is used: =DATE(2020,MONTH(1&B2)+1,1)-1. Which option explains the roles of +1, 1 and -1 within the formula the best?

• The +1 takes the date forwards by a month, while ,1 makes it the first of the month ahead. Then the -1 takes it forwards by a day, which is the last day of the original month.
• The -1 takes the date backwards by a month, while ,1 makes it the first of the original ahead. Then the +1 takes it forward by a day, which is the last day of the original month.
• The -1 takes the date backwards by a month, while ,1 makes it the first of the previous month. Then the +1 takes it forward by a day, which is the last day of the previous month.
• The +1 takes the date forwards by a month, while ,1 makes it the first of the month ahead. Then the -1 takes it back by a day, which is the last day of the original month.

Quiz 3: Calculations with Dates and using DAYS, NETWORKDAYS and WORKDAY

Q1. Of these three functions, which of these functions returns a number of days: DAYS, WORKDAY, NETWORKDAYS

• WORKDAY, NETWORKDAYS
• DAYS, WORKDAY, NETWORKDAYS
• DAYS, NETWORKDAYS
• DAYS, WORKDAY

Q2. An invoice is due 10 working days after the invoice date. Which of the following would be an appropriate formula, to get the due date, if the invoice date is 3/01/2020 and this value is held in cell D2?

• =NETWORKDAYS(D2,10)
• =WORKDAY(D2)+10
• =WORKDAY(D2,10)
• =DAY(D2)+10

Q3. An invoice is due 10 working days after the invoice date. Which of the following would be an appropriate formula, to get the due date, if the invoice date is 3/01/2020 and this value is held in cell D2, and given that the country has a 3-day weekend of Saturday, Sunday, and Monday?

• =WORKDAY(D2,10,”1000011″)
• =WORKDAY.INTL(D2,10,”1000011″)
• =WORKDAY.INTL(D2,10,3)
• =WORKDAY(D2,10,3)

Q4. The invoice date is the 3rd of January, 2020, i.e., 3/01/2020 which is held in cell D2. We want to work out how many working days are there between the invoice date and the end of the year, given that the country has a 3-day weekend of Saturday, Sunday, and Monday. What would be an appropriate formula from the list below?

• =NETWORKDAYS.INTL(D2,31/12/2020,”1000011″)
• =NETWORKDAYS.INTL(D2,31/12/2020,1000011)
• =WORKDAY.INTL(D2,”31/12/2020″,”1000011″)
• =NETWORKDAYS.INTL(D2,”31/12/2020″,”1000011″)

Q5. The syntax for NETWORKDAYS is NETWORKDAYS(start_date, end_date, [holidays]). If there are 3 public holidays held in cells H3, H4, and H5. Which of the following formulas would work when trying to work out the number of working days in 2020?

• =NETWORKDAYS(1/01/2020,31/12/2020,H3:H5)
• =NETWORKDAYS(“1/01/2020″,”31/12/2020”,H3:H5)
• =NETWORKDAYS(1/01/2020,31/12/2020,H3,H4,H5)
• =NETWORKDAYS(“1/01/2020″,”31/12/2020”,H3,H4,H5)

Quiz 4: More sophisticated date calculations with EOMONTH and EDATE

Q1. Cell F3 holds the value: 3/04/2020, i.e., the 3rd of April, 2020.

What will the following formula return (after re-formatting the cell to ‘Date’): =EDATE(F3,1)?

• 3/04/2020
• #VALUE!
• 30/04/2020
• 3/05/2020

Q2. Cell F3 holds the value: 3/04/2020, i.e., the 3rd of April, 2020.

Which of the following is an appropriate formula to calculate the end date of this month?

• =EOMONTH(F3,1)
• =EOMONTH(F3)
• =EOMONTH(F3,0)
• =EOMONTH(F3,31)

Q3. Cell F3 holds the value: 3/04/2020, i.e., the 3rd of April, 2020.

What is the result of the following formula (after re-formatting the cell to ‘Date’): =EOMONTH(EDATE(F3,1),1) ?

• 31/05/2020
• #VALUE!
• 30/06/2020
• 30/04/2020

Q4. Cell F3 holds the value: 3/04/2020, i.e., the 3rd of April, 2020.

What will be the result of the following formula (after re-formatting the cell to ‘Date’): =EDATE(F3,1)-1 ?

• 2/04/2020
• 2/05/2020
• 3/04/2020
• 3/05/2020

Q5. Cell F3 holds the value: 3/04/2020, i.e., the 3rd of April, 2020.

What does the following formula effectively generate =WORKDAY(EDATE(F3,1)-1,1) ?

• The next working day after the end of the month.
• The next working day after a year.
• The next working day after a month.
• The next working day after the end of the year.

Quiz 5: Working with numbers and dates: Test your skills

Q1. To do this assessment you should download this Excel workbook, follow the instructions, and answer the questions.

C1W2 Assessment
XLSX File
Download file
Populate Column B, Short ID, which is to contain the 4 digits of the Emp ID using the VALUE function. What is needed to replace XXXX in the formula for cell B7: =VALUE(RIGHT(XXXX))?

Enter answer here

Q2. What is the formula in cell B27?

Enter answer here

Q3. Input the following formula: =TEXT(G7,”DD/MMMM/YY”), into cell H7. What is the result?

Enter answer here

Q4. Insert today’s date in cell F1, what is the formula?

Enter answer here

Q5. Using the TODAY function, what is the most efficient formula to type in cell I1?

Enter answer here

Q6. Using the NOW function, what is the most efficient formula to display the date and time in cell C1?

Enter answer here

Q7. Using the DAY, MONTH, or YEAR functions, what is the most efficient formula that is required in cell F2?

Enter answer here

Q8. Using the DAY, MONTH, or YEAR functions, what is the most efficient formula that is required in cell F3?

Enter answer here

Q9. Using the DAY, MONTH, or YEAR functions, what is the most efficient formula that is required in cell F4?

Enter answer here

Q10. Populate cell I4 using the DATE function. What is to replace XXXX in the formula: =DATE(XXXX,F3,F2)?

Enter answer here

Q11. Populate cell I7 using the DAYS function. What is to replace XXXXX in the formula: =DAYS(XXXXX)?

Enter answer here

Q12. Using the NETWORKDAYS function, populate cell J7. What is the most efficient formula that is required?

Enter answer here

Q13. Populate Column L, Next Review, with the next workday after 180 workdays have passed since the Last Review. What will be the date of Stevie Bacata’s next review (shown in L7)?

• 28 December 2016
• 10 March 2017
• 2 January 2017
• 28 February 2017

Q14. Populate cell I2 using the EOMONTH function. What is the formula that is needed to do this most efficiently?

Enter answer here

Q15. Populate cell I3 using the EDATE function. What is the formula that is needed to do this most efficiently?

Enter answer here

Week 3: Excel Fundamentals for Data Analysis

Quiz 1: Cell Referencing and Naming

Q1. The cell F2 contains the following formula: =B2+C2+D2. What would be the resulting formula in cell F3, if we drag the fill handle down?

• =C2+D2+E2
• =B3+C3+D3
• =C3+D3+E3
• =B2+C2+D2

Q2. The tax rate, 15%, is in cell G2. Which of these cell references will allow us to use this value in a formula that appears down an entire column?

• $G$2
• G2
• G$2
• $G2

Q3. Instead of using absolute cell referencing, another way to refer to a tax rate of 15% which is cell G2, would be to:

• Click on cell G2, and then edit its name in the Name Range to create a Named Box.
• Click on cell G2, and then edit its name in the Name Box to create a Named Range.
• Click in the Name Box, then click in cell G2, then type in a name.
• Click in the Name Box, create a new name, and then click on cell G2.

Q4. Zara wants to create a name for the range for the tax rate of 15%. She types in Tax15, what is the problem?

• Tax15 is a cell reference.
• An underscore must separate letters from numbers.
• Tax15 is a heading in the document so it cannot be used as a name.
• Tax15 is a mix of letters and numbers which is not permissible for names.

Q5. Which of the following are valid characteristics of names for a named range? (tick all that apply)

• Labels used in cell references
• Hyphens
• Letters
• Numbers (at the beginning of a name)
• Restricted length (no more than 255 characters)
• Underscores
• Numbers (not at the beginning of a name)

Quiz 2: Defined Names and Create from Selection

Q1. When using Create from Selection you should select both data and labels.

• TRUE
• FALSE

Q2. When using Define Name you should select both data and labels.

• TRUE
• FALSE

Q3. The worksheet contains the following data:

We want to name each of the cells in column B using the labels in column A, which would be the fastest option?

• Define Name
• Double click each cell in Column B
• Create from Selection
• Name Box

Q4. Which shortcut will select a range of cells from the first selected cell to the end of a column (i.e., the first empty cell in that column)?

• CTRL+ALT+Arrow down
• CTRL+SHIFT
• CTRL+Arrow down
• CTRL+SHIFT+Arrow down

Q5. On which Tab of the ribbon are the Name tools located?

• Data
• Formulas
• Insert
• Home

Q6. A named range, arrivals, has been created for cells N2 to N82. No other named ranges have been created. What will the Name Box display when clicking on cell N3?

• arrivals2
• arrivals
• N3
• arrivals3

Q7. A named range, arrivals, has been created for cells N2 to N82. What does Scope mean?

• Whether the named range applies to the current Excel file or all Excel files on a computer.
• How many times the named range can be referred to in the spreadsheet.
• Whether the named range applies to a particular sheet or an entire workbook.
• How many sheets can refer to the named range.

Quiz 3: Managing Names

Q1. Which of the following does the name manager not allow us to do?

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

Q2. It is possible for the same range to have multiple names.

• TRUE
• FALSE

Q3. What is the quickest way to obtain a list of all the named ranges and the cells they refer to, for documentation purposes, in Excel or in a word processing document?

• Go to ‘Name Manager’ and click on ‘Export Names’
• Go to ‘Name Manager’ and click on ‘Paste Names’
• Go to ‘Use in Formula’ and click on ‘Paste Names’
• Go to ‘Use in Formula’ and click on ‘Export Names’

Q4. Zara accidentally deleted a named range. What will happen?

• Excel will replace any range in a calculation that depends on this range with cell references.
• Excel will not replace any range in a calculation that depends on this range with cell references.
• Excel will not allow you to delete a range that has already been named.
• Excel will only replace the first occurrence of this range in a calculation that depends on this range with cell references.

Q5. When sharing files with colleagues, the named ranges will revert to relevant cell references.

• TRUE
• FALSE

Quiz 4: Calculations with Named Ranges

Q1. What can you do if you forget the Named Ranges that apply to your spreadsheet? Click all that apply.

• Go to Use in Formula under the Formula Tab.
• Go to View named range list under the View Tab.
• While typing in a formula, right click and go to named range list.
• Use the shortcut key F3 and a list of named ranges will appear.

Q2. Named ranges are more efficient than using cell references when performing calculations because of the following reasons: (Click all that apply)

• Named ranges can reduce the chances of mistakes in formulas.
• The = can be avoided in formulas, making them easier to read.
• Some Excel functions only work with named ranges.
• Named ranges make formulas more meaningful.

Q3. Column B contains the named range, sales_price Column C contains the named range, shipping_cost. The formula: =SUM(sales_price)+SUM(shipping_cost) is typed into cell D2. What will be the result?

• #VALUE!
• The sum of column B and column C.
• The sum of cells B2 and C2.
• The product of cells B2 and C2.

Q4. Column B contains the named range, sales_price. Column C contains the named range, shipping_cost. What would be an appropriate formula to obtain the total of columns B and C in a single cell? (Click all that apply).

• =SUM(sales_price+shipping_cost)
• =SUM(sales_price)+SUM(shipping_cost)
• =sales_price+shipping_cost
• =SUM(sales_price:shipping_cost)

Q5. Column C contains the named range, shipping_cost. Column D contains the named range, location. What would be an appropriate formula to calculate the total shipping cost for all orders originating from the location of Sydney, given that location is the named range for column D?

• =COUNTIFS(shipping_cost,”Sydney”)
• =SUMIFS(shipping_cost,location,”Sydney”)
• =SUMIFS(shipping_cost,”Sydney”)
• =COUNTIFS(shipping_cost,location,”Sydney”)

Quiz 5: Automating Data Validation with Named Ranges

Q1. To quickly create a named range: highlight the data, including the heading and press:

• CTRL + F3
• CTRL + F4
• CTRL + SHIFT + F4
• CTRL + SHIFT + F3

Q2. Applying named ranges automatically embeds conditional formatting into your spreadsheet.

• TRUE
• FALSE

Q3. One example of automation with named ranges is in the creation of a dropdown list. In the Data Validation dialog box below, what has been done? (Click all that apply)

• Other values input into the relevant cell will be converted to “#VALUE!”
• Source > =Locations is referring to the named range of Locations
• Data Validation only allows the values specified in the parameters.
• Allow > List has created a dropdown list
• Other values input into the relevant cell will be highlighted in red.

Q4. If a brand new city is added at the bottom of the named range of Locations, the data validation that was created below will automatically update.

• TRUE
• FALSE

Q5. What is the difference between the functions COUNT and COUNTA?

• COUNT only counts cells containing numbers whereas COUNTA counts all non-empty cells.
• COUNT ignores empty cells but COUNTA counts them as well.
• COUNT accepts only one argument whereas COUNTA accepts up to 255.
• COUNTA only counts non-numeric data and COUNT only counts cells containing numbers.

Quiz 6: Defined Names for working more effectively with data: Test your skills

Q1. To do this assessment you should download this Excel workbook, follow the instructions, and answer the questions.

C1W3 Assessment
XLSX File
Download file
Have a look at the Travel expense calculator 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 using the labels in column A.

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

Enter answer here

Q2. While the calculation of Other Expenses is looking better it is still not correct. Open the Name Manager.

Have a look at the named ranges for Ex_Rate and Other, they only go to row 14, which explains the incorrect calculation. Edit Ex_Rate to go from L11:L21 and change Other to go from J11:J21. Click OK and close the Name Manager.

Other Expenses has been corrected. What is the total for Other as shown in K6?

Enter answer here

Q3. Let’s fix Total Transportation 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

What is the value for Transport as shown in K3 (one or two decimal places only)?

Enter answer here

Q4. Next, Accommodation Costs, use any method you think suitable to give the name Accommodation_Costs to range F11:F21.

What is the total for Accommodation as shown in K4 (one or two decimal places only)?

Enter answer here

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 total cost of Meals as shown in K5 (one or two decimal places only)?

Enter answer here

Q6. Our Travel expense calculator worksheet is now looking good, but we would also like to complete a breakdown of expenses by region. Start with adding the following named ranges:
E11:J14 – London
, E15:J18 – Paris , E19:J21 – Jakarta
. Now go to the Summary By Region worksheet and observe the calculated values for London.

What is the summary value for London in the local currency? (no commas)

Enter answer here

Q7. Stay on the Summary By Region worksheet. Enter a formula in C5 to add up the total amount spent in Paris (used the named range you just created). Then do the same in C6 for Jakarta.

Using the named range and the SUM function, what are the formulas to use here?

Note: you should have one answer for Paris and one answer for Jakarta. Just type the answer for one of these as your answer to this question.

Enter answer here

Q8. Stay on the Summary By Region worksheet. In D5 create a calculation to convert Euros to Dollars by multiplying the Euros spent (C5) by the exchange rate for Euro (which uses the named range EUR). Perform a similar calculation to convert the Indonesian Rupees to dollar (using the correct named range).

What is the formula in D6?

Enter answer here

Q9. Click in D7 (still in Summary By Region), and use Autosum to get the total spent in USD.

What is this value? (no commas, no characters, no dollar signs)

Enter answer here

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 are the contents of cell C28? (Cut and paste your answer here to avoid errors.)

Enter answer here

Q11. Look at the Travel expense calculator worksheet. What is the value of Total Meals in cell K5? (no characters or commas)

Enter answer here

Q12. Still on the Travel expense calculator worksheet. What is the value of Total Other Expenses in cell K6? (no characters or commas and rounded to the nearest whole number with no decimal point)

Enter answer here

Q13. How many rows are in your Name Manager?

Enter answer here

Q14. In the Travel expense calculator sheet, what is the value of Total Trip Expenses in K7 (rounded to the nearest dollar, input as a number with no “$” and no commas)?

Enter answer here

Q15. In the Travel expense calculator sheet, what is the value of L21 (to three decimal places)?

Enter answer here

Excel Fundamentals for Data Analysis Course Review:

In our experience, we suggest you enroll in Excel Fundamentals for Data Analysis courses and gain some new skills from Professionals completely free and we assure you will be worth it.

Excel Fundamentals for Data Analysis course is available on Coursera for free, if you are stuck anywhere between a quiz or a graded assessment quiz, just visit Networking Funda to get Excel Fundamentals for Data Analysis Coursera Quiz Answers.

Get All Course Quiz Answers of Excel Skills for Data Analytics and Visualization Specialization

Excel Fundamentals for Data Analysis Coursera Quiz Answers

Data Visualization in Excel Coursera Quiz Answers

Excel Power Tools for Data Analysis Coursera Quiz Answers