Problem Solving with Excel Coursera Quiz Answers

All Weeks Problem Solving with Excel Coursera Quiz Answers

Problem Solving with Excel Week 1 Quiz Answers

Quiz 1: Excel Basics and Navigation Quiz

Q1. In the image below, which number is next to the arrow that points to the formula bar?

• 1
• 2
• 3
• 4

Q2. The Excel screen is designed to allow for easy access to the key features of the program. You can click through the ribbon or use what key on your keyboard to toggle between different tabs on the ribbon and different buttons on each tab?

• Ctrl key
• Alt key
• Shift key
• Fn key

Q3. What button allows you to insert a formula step-by-step through the Excel user interface?

• A
• B
• C
• D

Q4. You have received an Excel spreadsheet from a client and you are unsure of where the contents of a cell came from. What should you do?

• Go to the cell and click Formulas > Trace Precedents
• Go to the cell and click Formulas > Evaluate Formula
• Go to the cell and click Formulas > Show Formula
• Go to the cell and click Formulas > Remove Arrows

Basic Functionality Quiz Answers

Q1. You have an Excel workbook that contains many different formulas. You want to copy the values, but not the formulas into another tab. What functionality would you use to do this?

• Paste Special
• Cell Locking
• Named ranges
• Traceability

Q2. The formula A$1 does which of the following?

• Anchors the formula to the selected cell
• Anchors the formula to the selected row
• Anchors the formula to the selected column
• Does not anchor the formula to anything

Q3. You receive a set of data from a client which contains a lists of names, sales amounts, locations and other information. What function will you use to assign an alias to each group of data?

• Traceability
• Cell locking
• Name ranges
• Paste special

Subtotal Quiz Answers

Q1. The subtotal function has the ability to perform mathematical functions such as: sum, average, count, product, min, max and more.

• True
• False

Quiz 2: Week 1 project quiz

Q1. After you have completed the Week 1 project, select the formula that displays in cell M33 in the sheet “Question.” This will verify that you successfully completed the project.

• =Question_Ans!$G33*Question_Ans!M$21
• =$G33*M$21
• =$M33*G$21

Problem Solving with Excel Week 2 Quiz Answers

Quiz 1: VLookup

Q1. In the image of the data set below, which number represents the lookup value?

• 2. Product code
• 3. Month
• 4. Quantity

Q2. Jerry is working with a set of data using the VLookup function. He continues to receive an error message because the data he is trying to look-up does not exist. Which function should Jerry use to get around the message?

• =HLookup function
• =IFError function
• =VLookuphelp function
• =SumIf function

Q3. True or False

HLookup is similar to VLookup – only it allows you to search for data horizontally.

• True
• False

Q4. What is the correct formula structure for completing a vLookup?

• =VLOOKUP (table_array,lookup_value,col_index_num, [range_lookup])
• =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
• =VLOOKUP (table_array, col_index_num, table_array, [range_lookup])
• =VLOOKUP (lookup_value, col_index_num, table_array, [range_lookup])

Data Cleansing – Values Quiz Answers

Q1. Which of the following data cleansing functions allows you to remove all spaces from a string, with the exception of single spaces between words?

• CLEAN function
• TRIM function
• SUBSTITUTE function
• VALUE function

Q2. Alex received a note that there was an update to some of the data he has been working on. What is the correct formula that Alex should use to substitute the new text for the old?

• =SUBSTITUTE(Old_Text, New_Text, Instance_num,Text)
• =SUBSTITUTE(Old_Text,NewText, Instance_num,Text)
• =SUBSTITUTE(Text,Old_Text,New_Text,Instance_num)
• =SUBSTITUTE(Text,New_Text,Old_Text,Instance_num)

Q3. Which function allows you to remove all non printable characters from text?

• VALUE function
• CLEAN function
• TEXT function
• SUBSTITUTE function

Data Cleanse – Text Quiz

Q1. Proper, Upper, Lower

• Proper, Mid, Lower
• Upper, Lower, Mid
• Upper, Lower, Find

Q2. Which text function does not allow you to select a substring from a string?

• Find function
• Right function
• Left function
• Mid function

Q3. You have been asked to supervise an intern and do not have the time to review and explain common mistakes in their data set. Which function can you use to to help your intern better understand why their data is not allowed?

• Upper function
• Lower function
• Data validation
• Find function

Quiz 2: Week 2 Quiz

Q1. Once you have completed the project, what is the formula in cell I21 on the  tab?

• =IFERROR(VLOOKUP($B21,Data_Set_Clean_Ans!$B$8:$M$108,9,FALSE),”-“)
• =IFERROR(VLOOKUP($B21,Data_Set_Clean!$B$8:$M$108,9,FALSE),”-“)
• =IFERROR(VLOOKUP($B21,Data_Set_Clean!$B$8:$M$110,9,FALSE),”-“)

Q2. Once you have completed this project, what is the formula in cell K9 on the Data_Set_Clean tab?

• =VLOOKUP(F9,Data_Lookup!$C$14:$J$25,6,FALSE)
• =VLOOKUP(F9,Data_Lookup_Ans!$C$14:$J$25,6,FALSE)
• =VLOOKUP(F9,Data_Lookup_Ans!C14:J$25,6,FALSE)

Problem Solving with Excel Week 3 Quiz Answers

Quiz 1: Logical Functions Quiz

Q1. Which two logic statement functions allow you to use multiple logical tests in evaluating a data set where all tests must be true?

• If + or function
• If + and function
• Countif + Sumif function
• If + Sumif function

Q2. Which logic statement function allows you to combine multiple IF statements into one set of tests to run additional formulas based on the outcomes of previous tests?

• If function
• If + or function
• Nested IF functions
• Countif function
• Sumif function

Q3. You receive a large data set and have been asked to find out how many occurrences took place from May to August. Which logic statement function would be best suited to use?

• Countif function
• Embedded if function
• Sumif function
• Sumifs + Countifs function

Q4. Which field in the Sumif formula below is NOT a mandatory field?

= SUMIF(range, criteria, [sum_range])

• Criteria
• Range
• sum_range

Pivot Tables Quiz Answers

Q1. A pivot table is most commonly used to do which of the following?

• Facilitate the use of multiple logical tests in evaluating a data set
• Extract data from a large, detailed data set and transform data into easy-to-read information
• Convert text string that represents a number into a number

Q2. Looking at the ribbon, which tab will you find the Pivot table option?

• Insert
• Formulas
• Data

Q3. In the list below what are the correct steps for creating a pivot table?

1. Click on the Pivot table function
2. Highlight your data
3. Click on the area where you want the table

1. Highlight your data
2. Click on the Pivot table function
3. Click on the area where you want the table placed
4. Drag the items from the list to the filter, row or column

1. Highlight your data
2. Right click
3. Select Pivot table
4. Click on the area where you want the table placed
5. Drag your items from the list to the filter, row or column

1. Click on the Pivot table function
2. Highlight your data
3. Click Ctl +Shift+All
4. Click on the area where you want the table placed
5. Drag your items from the list to the filter, row or column

Q4. True or False

Pivot tables automatically update whenever you make changes to a data source.

• True
• False

Quiz 2: Week 3 Project Quiz

Q1. In Exercise 3, how many sites are listed as need to hire (O189:O200)?

• 1
• 2
• 3
• 4

Q2. Once you have completed Exercise 3, what is the correct formula in cell K197?

• =SUMIFS(Head_Count,Office_Name,$B197,Role,”Finance”,AvgHourlyCost,”>=”&K$185,AvgHourlyCost,”<=”&K$186)
• =COUNTIFS(Head_Count,Location_Name,$B197,Role,”Finance”,AvgHourlyCost,”>=”&K$185,AvgHourlyCost,”<=”&K$186)
• =SUMIFS(Head_Count,Location_Name,$B197,Role,”Finance”,AvgHourlyCost,”>=”&K$185,AvgHourlyCost,”<=”&K$186)

Q3. In Exercise 5, what is the total headcount for Buffalo shift 2 employees?

• 11
• 279
• 519

Problem Solving with Excel Week 4 Quiz Answers

Quiz 1: Statistical Forecasting Quiz

Q1. You are asked to predict total store sales based on the number of visitors to the store. You are provided a data set containing sales and store visits data over time. What is a good way to make this prediction using these data points in Excel?

• Standard deviation
• Regression analysis
• Covariance
• R-Squared

Q2. What statistical method do we use to quantify the relationship between changes in the independent variable and changes in the dependent variable?

• R-Squared
• Covariance
• Linear regression
• Slope

Q3. What do we use as a measurement of the average dispersion of values in a dataset around the average value?

• Multicollinearity
• Standard deviation
• Correlation
• Covariance

Q4. How should we interpret a correlation coefficient of 1?

• No strong correlation between the two variables
• For every 1-unit increase in one variable, we observe a 1-unit decrease in the other variable
• For every 1-unit increase in one variable, we observe a 1-unit increase in the other variable
• None of the above

Q5. How do we interpret the Y-intercept of our linear regression?

• A measure of the average dispersion of values in a dataset around the average value.
• An indication of the strength and direction of the relationship between our independent and dependent variables.
• A number describing both the steepness and direction of the line graph of our linear regression function.
• The value which cannot be explained by our regression analysis, and is constant despite our measured relationship between variables. This also represents the value of our dependent variable when your independent variable is equal to zero.

Q6. You’ve completed a linear regression model using in-store sales data to store visits data. You observe a strong, positive correlation between the independent variable and the dependent variable. The r-squared value (i.e. the coefficient of determination) is close to 1. Standard deviation of the data set is quite low. It is reasonable to conclude that sales increased because of increases in in-store traffic?

• True. Linear regression analysis with the above outcomes suggests a strong, causal relationship.
• False. Linear regression analysis with the above outcomes does not imply any causal relationship.

Index Match Quiz Answers

Q1. Index Match allows you to complete which of the following functions?

• Measure profitability
• Ability to look vertically as well as horizontally with the same formula
• compare profitability investments
• Calculate subtotal against a range

Q2. Index formula is used to tell us which of the following?

• The value contained in a cell designated by the distance from the top left value
• The matching position of a cell within an index
• The value contained in a cell that is designated number of column to the right of the lookup value
• The starting position of an index within a dataset

Q3. When you use the match formula which of the following is the expected outcome?

• The lookup value
• The sum of all values that match the lookup value
• The value of the column next to the matching output of the lookup value
• The position within an array of a matching lookup value

Q4. Which of the following list the correct steps to use to lookup a value based on a horizontal lookup value and a vertical lookup value?

• Use two match formulas, the first to look up the corresponding row value and then column value
• Index an array and use a match formula to lookup the row position to a corresponding lookup value and then use another match formula to lookup the corresponding column position of another lookup value
• Use sumifs, with the first criteria being the vertical lookup value and the second criteria being the horizontal lookup value
• Use a Vlookup and AND function in combination. In the Vlookup for the lookup value use the AND function to list both the horizontal and vertical criteria

Financial Functions Answers

Q1. You are working with Net Present Value (NPV), which statement below reflects the current value of future cash flows?

• Future cash flows are worth less than the present value as the cash flows are discounted by a given discount rate
• Future cash flows are worth more in today’s dollars as the future dollars lose value due to depreciation
• Future cash flows are worth less because the dollar strengthens overtime
• Future cash flows are worth more as the future values will be taxed at a higher rate

Q2. Net Present Value (NPV) is the value of future dollars, in terms of today’s dollars, discounted based on given discount rates.

• True
• False

Q3. You are working with IRR formula, which of the following scenarios will not be computed?

• The cash flows begin with three negative years followed by 1 large positive year
• There is a negative cash flow after the first positive cash flow
• The NPV of the cash flows is less than 0

Q4. What are the four components of loan formulas?

• Rate, payments per year, current value, loan payment amount
• Rate, present value of the loan, future value of the loan, loan payment amount.
• Rate, number of payments, present value of the loan, loan payment amount

Quiz 2 :Week 4 Project Quiz

Q1. To get the daily interest rate, what should the client divide the yearly interest by?

• 7
• 12
• 365
• 730

Q2. What formula can be found in cell E25 on the Culminating Exercise sheet when complete?

• =(D25/1000)*INDEX($A$15:$C$16,MATCH(D25,$B$14:$B$16,1))
• =(D25/1000)*INDEX($C$14:$C$16,MATCH(D25,$B$14:$B$16,1))
• =(D25/1000)*INDEX($A$15:$C$16,MATCH(D25,$C$14:$C$16,1))

Q3. After evaluating the NPV for the advertising campaign, what is the NPV?

• 628
• 1,628
• 3,265

Q4. Should the client proceed with the investment?

• Yes
• No

Get All Course Quiz Answers of Data Analysis and Presentation Skills: the PwC Approach Specialization

Data-driven Decision Making Coursera Quiz Answers

Problem Solving with Excel Coursera Quiz Answers

Data Visualization with Advanced Excel Coursera Quiz Answers

Effective Business Presentations with Powerpoint Quiz Answers