Get All Weeks Introduction to Data Analysis Using Excel Coursera Quiz Answers
Table of Contents
Introduction to Data Analysis Using Excel Week 01 Coursera Quiz Answers
Quiz 1: Reading Data into Excel
Q1. The chart shows text data for Columns A-D The format shown in the example above is:
[expand title=View Answer] Tab-delimited [/expand]
Q2. The chart shows text data for Columns A-D The format shown in the example above is:
[expand title=View Answer] Semicolon delimited [/expand]
Quiz 2: Basic Data Manipulation in Excel
Q1. To move to the edge of the current data region, press:
[expand title=View Answer] CTRL + arrow key [/expand]
Q2. Columns A, B and C have entries for Serial Number, Part Number and Revision respectively; The data above is sorted:
[expand title=View Answer] First by Part Number, then by Revision. [/expand]
Quiz 3: Arithmetic Manipulation in Excel
Q1. Typing which of the following into a cell would result in a cell value of 3?
[expand title=View Answer] =1+2[/expand]
Q2. A1-C3 have integers 1-9 in increasing order left-right Using the data in the example above, what is the value of the following formula?
[expand title=View Answer] 5 [/expand]
=A3+C1
Quiz 4: Basic Functions in Excel
Q1. The figure shows a table with numbers 1-9 in increasing order left-right in 3X3 matrix
In the example above, typing which of the following into a cell would result in a cell value of 3? Select all that apply.
[expand title=View Answer]
1.=1+2
2.=SUM(A1,B1)
3.=SUM(A1:B1)
[/expand]
Quiz 5: Functions Using Absolute and Relative References
Q1. Which symbol is used in a formula to “fix” a row or a column reference?
[expand title=View Answer] $ [/expand]
Q2. What would be the calculated value of the following formula?
=MAX(1,4,2,8,5)
[expand title=View Answer] 8[/expand]
Quiz 6: Functions Explained
Q1. The table on left is blank while table on right shows the desired output Let’s say you want each blank cell to equal its row header multiplied by its column header, as shown above. Which of the following cell B2 formulas would give the correct result when pasted into the remaining empty cells?
[expand title=View Answer] =$A2*B$1[/expand]
Q2. If you want to always reference cell C5 in a formula, even when that formula is copied down rows or across columns, the reference to cell C5 should be written as:
[expand title=View Answer] $C$5 [/expand]
Quiz 7: Introduction to Spreadsheets
Q1. Download the file “Store Sales 2011.txt” (you may need to right-click and select “save link as…”) Use the data in this file for the remainder of the assignment. Note that the date in the file is in the mm/dd/yyyy format, so while reading it into Excel please be vary of that.
The format used in the “Store Sales 2011.txt” file is:
[expand title=View Answer] Comma-delimited [/expand]
Q2. Import the file into Excel using the correct file format from the previous question. How many rows of data does the file contain?
[expand title=View Answer] You can open the file in Excel to determine the number of rows. After opening the file, you can check the row count by looking at the row number in the bottom bar of Excel. Subtract 1 from the total row count to exclude the header row. [/expand]
Q3. Sort the data by Order Date (oldest to newest) and then by Order Quantity (largest to smallest). After the data is sorted, what is the Order ID of the order in the 3rd row of data (i.e. spreadsheet row 4)?
[expand title=View Answer] To sort the data by Order Date and Order Quantity, select the data, go to the “Data” tab, and use the “Sort” function. First, sort by “Order Date” in ascending order, and then sort by “Order Quantity” in descending order. The Order ID of the order in the 3rd row can be found by looking at the sorted data. [/expand]
Q4. Using cell references, how could you calculate the unit price of the order in the first row of data?
[expand title=View Answer] =D2/E2 [/expand]
Q5. Insert a new column to the right of the “Sales” column, and type the name “Unit Price” as the header. Using cell references, calculate the unit price of the order in the first row, then copy and paste that formula down to the remaining rows.
Rounded to 2 decimal places, what is the Unit Price of the last row of data? (Do not include a dollar sign or commas in your answer)
[expand title=View Answer]To insert a new column for “Unit Price” and calculate the unit price for each row, you can enter the formula =D2/E2 in the first cell of the “Unit Price” column (assuming “Unit Price” is in column D and quantity is in column E), and then copy this formula down to the remaining rows. [/expand]
Q6. What is the largest Unit Price in this dataset? Hint: Use the MAX() function here.
[expand title=View Answer] To find the largest Unit Price in the dataset, you can use the MAX() function on the “Unit Price” column.[/expand]
Q7. How many orders were sent by Delivery Truck? Assume each line is counted as a single order.
Hint: Use the COUNTIF() function here.
To count the number of orders sent by Delivery Truck, you can use the COUNTIF() function with the criteria "Delivery Truck."
Q8. What is the total shipping cost of all orders sent by Delivery Truck?
Hint: Use the SUMIF() function here.
[expand title=View Answer] To calculate the total shipping cost of all orders sent by Delivery Truck, you can use the SUMIF() function with the criteria “Delivery Truck” on the Shipping Cost column. [/expand]
Q9. If you wanted to know the range, or the difference between the highest and lowest value, of the shipping costs in this dataset, which formula could you use?
[expand title=View Answer] =MAX(H2:H2003)-MIN(H2:H2003) [/expand]
Q10. What is the average shipping cost (to 2 decimal places) of all orders sent by Delivery Truck?
[expand title=View Answer] To calculate the average shipping cost of all orders sent by Delivery Truck, you can use the AVERAGE() function with a filter or criteria to select only the rows with “Delivery Truck” in the appropriate column. [/expand]
Week 2 Quiz Answers
Quiz 1: The “IF” Command in Excel
Q1. Remember: IF(logical_test, [value_if_true], [value_if_false])
The table shows A1, A2, and A3 entries as a dog, cat, and rabbit respectively Using the table above, what would be the result of the following formula?
[expand title=View Answer] Based on the provided table, the result of the formula =IF(A1=”dog”, “bark”, “no bark”) would be “bark” if the content of cell A1 is “dog,” and “no bark” if it’s anything other than “dog.”
=IF(A1=“dog”, “bark”, “no bark”)
[/expand]
Q2. Select all statements below that are true.
[expand title=View Answer]
=IF(1=2, “incorrect”, “correct”)
returns: incorrect
=IF(TRUE, “yes”, “no”)
returns: yes
[/expand]
Quiz 2: The “IF” Command in Excel Using Numerical Data
Q1. Given the formula
=IF(A1>1000, 1000, A1)
Which of the following is true?
[expand title=View Answer] No matter the value of A1, the highest value this formula will return is 1000. [/expand]
Q2. A1-C3 have integers 1-9; the IF() function entries are shown on the right
Assume the formula shown above was entered into cell D1, then copied down to D2 and D3. The resulting values in cells D1, D2, and D3 would be:
[expand title=View Answer] small, small, small[/expand]
Quiz 3: The “Nested IF” Command in Excel
Q1. The formula
=IF(FALSE, 1, IF(TRUE, 2, 3))
will return:
[expand title=View Answer] 2[/expand]
Q2. Given the formula
=IF(A1<10, “low”, IF(A1<20, “middle”, “high”))
If the value of A1 is 10, what will the formula return?
[expand title=View Answer]
If the value of A1 is 10, the formula:
=IF(A1<10, “low”, IF(A1<20, “middle”, “high”)) will return: middle In this formula, if A1 is less than 10, it returns "low." But if A1 is greater than or equal to 10, it goes to the next IF statement and checks if A1 is less than 20, which is true for the value 10. Therefore, it returns "middle." [/expand]
Quiz 5: Another “VLOOKUP” Example
Q1. Columns A and B have entries for size and label respectively; VLOOKUP entries and input parameters are shown on the right What will the VLOOKUP formula return? Note the last parameter.
[expand title=View Answer] M [/expand]
Q2. Columns A and B have entries for size and label respectively; VLOOKUP entries and input parameters are shown on the right What will the VLOOKUP formula return? Note the last parameter.
[expand title=View Answer] #N/A [/expand]
Quiz 6: The “HLOOKUP” Function in Excel
Q1. practice quizColumns A, B and C have entries for Apples, Bananas and Candy respectively; HLOOKUP entries and input parameters are shown below What will the HLOOKUP formula return?
Enter answer here
Q2. Rows 1 and 2 have entries for Size and Label respectively; HLOOKUP entries and input parameters are shown below What will the HLOOKUP formula return?
[expand title=View Answer] L [/expand]
Quiz 7: Professor ‘Know-it-all’ Needs Help!
Q1. Professor ‘Know-it-all’ wants to implement another system to call upon a group of students at random. She has created 4 groups:
Group 1: Students 1-5 Group 2: Students 6-10 Group 3: Students 11-15 Group 4: Students 16-20
Her VLOOKUP function does not correctly return a random group. What is wrong with her function below?
practice quizColumns A and B have entries for S.No and Group Name respectively; VLOOKUP entries and input parameters are shown on the right.
[expand title=View Answer] The [range_lookup] parameter should be “TRUE”. [/expand]
Q2. You
inform Professor K. that a much simpler solution to her problem above would be
to just use the RANDBETWEEN function to randomly return a value of 1, 2, 3, or
4 directly. Which of the following would correctly
[expand title=View Answer] =RANDBETWEEN(1,4) [/expand]
implement this solution?
Quiz 8: Spreadsheet Functions to Organize Data
Q1. Download the spreadsheet “Store Sales 2012.xlsx” below. Use the data in this spreadsheet for the remainder of the assignment.
The formula in cell H2 contains a referencing error that must be fixed before copying the formula down the column. If the formula were to be copied down the column without making any corrections, which of the following would be true?
[expand title=View Answer]
The formula would always return “Small”
The formula would always return “Medium”
The formula would always return “Large”
The formula would only return “Large” or “Small”
[/expand]
Q2. Assuming you want the formula in H2 to always reference the cell directly to its left, correct the formula. Once the formula is fixed, copy the formula down the column. What is the “Order Type” of Order ID 11428, placed on 1/21/2012?
[expand title=View Answer]
Small
Medium
Large
Extra Large
[/expand]
Q3. The boss decides that using only 3 levels—small, medium, and large—does not give the shipping department enough information. She wants to use the 10 levels shown in Lookup Table 1 (A3:B12). Write a formula in the “Expanded Order Type” column to assign the correct type to each order by “Order Quantity.” Copy the formula down to all the rows. Based on the layout of Lookup Table 1, should you use a VLOOKUP function or an HLOOKUP function?
[expand title=View Answer] VLOOKUP [/expand]
Q4. Using your newly created “Expanded Order Type” column, calculate the total “Sales” for all orders of “Medium” type (rounded to 2 decimal places). Hint: The SUMIF() function is useful here.
Enter answer here
Q5. The company gives a 1% discount on any Extra Large or larger orders. In the “Discount” column, create a formula that returns 0.01 if the “Expanded Order Type” is Extra Large, XX Large, or XXX Large, and returns 0 otherwise. You can accomplish this by using a (select all that apply):
[expand title=View Answer]
VLOOKUP formula
HLOOKUP formula
Nested IF formula
A single IF formula (without additional formulae)
[/expand]
Q6. Create the formula from the previous question and copy the formula down to all the rows. How many orders will have a discount applied?
Hint: The COUNTIF() function is useful here.
Enter answer here
Q7. The “Sales with Discount” can be calculated by applying the discount to the original “Sales” entry:
Sales with Discount = Sales – (Sales * Discount)
or
Sales with Discount = Sales * (1 – Discount)
Create a formula in the “Sales with Discount” column and copy it down to all the rows. What is the impact of the discount on total sales in 2012? In other words, what is the difference between the sum of the “Sales” and the sum of the “Sales with Discount” (rounded to 2 decimal places)?
Enter answer here
Q8. Currently, customers are responsible for paying the shipping costs. The sales team suggests that customers really dislike paying shipping costs, and that offering “free shipping” instead of the 1% discount would likely increase sales. Create a formula for the “Sales with Free Shipping” column that subtracts the “Shipping Cost” from the “Sales” only if the “Expanded Order Type” is Extra Large, XX Large, or XXX Large. Copy the formula down to all the rows. What would total 2012 sales have been if the company had offered free shipping instead of the 1% discount (rounded to 2 decimal places)?
Enter answer here
Q9. How much money would the company have saved in 2012 if it had offered free shipping instead of the 1% discount on Extra Large, XX Large, or XXX Large orders (rounded to 2 decimal places)?
Enter answer here
Q10. What would 2012 total “Sales” have been if the company had offered free shipping on any order shipped by Delivery Truck, and no additional discounts (rounded to 2 decimal places)?
Enter answer here
Week 3 Quiz Answers
Quiz 1: Using the “VLOOKUP” Function Across Worksheets
Q1. Second sheet in the VLOOKUP function is referred to as “Sheet 2”
Second sheet in the image is labelled as “Item Pricing” The VLOOKUP function above is intended to return the price from the second worksheet, but it has been incorrectly entered. To fix it, ‘Sheet 2’ should be replaced by:
[expand title=View Answer]‘Item Pricing’[/expand]
Q2. Once the function above is corrected, what value will it return?
Enter answer here
Quiz 2: Data Filtering in Excel
Q1. Based on the dropdown symbol, which of the following has some sort of filtering applied? Select all that apply.
[expand title=View Answer] The filter dropdown button to the right of “Animal” has a funnel icon[/expand]
Q2. The last row with mouse is #45 and the first entry in the column is “Animal”
The column above is filtered to only show “Mouse” entries. If the column were NOT filtered, how many total Animal entries would there be? Hint: Don’t forget that the heading row does not count as an entry.
Enter answer here
Quiz 3: Use of Pivot Tables in Excel
Q1. the question explains the graphic To set up a Pivot Table with years listed vertically as shown above, the Year field should be dragged into the following Pivot Area:
[expand title=View Answer] ROWS [/expand]
Q2. the question explains the graphic To set up a Pivot Table with years listed horizontally as shown above, the Year field should be dragged into the following Pivot Area:
[expand title=View Answer] COLUMNS [/expand]
Quiz 4: More Pivot Table Options
Q1. In the Pivot Table above, in which pivot table field area was the Gender column used? Select all that apply.
[expand title=View Answer]
COLUMNS
VALUES
[/expand]
Q2. In 1992, how many women participated in the sport of gymnastics?
Enter answer here
Quiz 5: Application of Pivot Tables to Numeric Data
Q1. The chart shows the pivot table output Which of the following would have created the Pivot Table above?
[expand title=View Answer] Filter-Product Category, Rows- Product container and Columns-Shipping Cost [/expand]
Q2. Which of the following is true about Pivot Tables? Select all that apply.
[expand title=View Answer]
Pivot Tables can be filtered by multiple columns.
Pivot Tables automatically calculate grand totals of rows and columns.
Dates in a Pivot Table can be grouped by years, quarters, months, days, hours, minutes, and seconds.
[/expand]
Quiz 6: Introduction to Charts in Excel
Q1. For months 7-9, the range for Dataset A is (-42,20), B is (-90,0) and C is (50,70)
The Line Chart above shows the data trends for three different datasets. Which dataset has the highest values for the months in the 3rd quarter of the year (months 7, 8, and 9)?
[expand title=View Answer] Dataset C [/expand]
Q2. The table shows monthly distributions (rows) of Datasets A, B and C (columns)
The Line Chart in the previous question was created using the data above. How could you update the Line Chart to use the month names (A2:A13) instead of numbers on the horizontal axis?
[expand title=View Answer] Right-click on the chart, choose “Select Data…,” and select: [/expand]
Quiz 7: Introduction to Filtering, Pivot Tables, and Charts
Q1. Download the spreadsheet “Store Sales 2009.xlsx” below. Use the data in this spreadsheet for the remainder of the assignment.
The “Compiled Information” tab was created to pull in the data from the other tabs using formulas. The formula in the “Shipping Cost” column was entered incorrectly. Fix the formula in cell D3, then re-copy the correct formula down to the remaining rows. What was the problem with the original formula?
[expand title=View Answer]
The formula did not reference the correct tab.
The formula should use a locked reference for the table_array parameter.
The formula should use a locked reference for the lookup_value parameter.
The formula should be written with the HLOOKUP function.
[/expand]
Q2. On the “Compiled Information” tab, a VLOOKUP formula has been pre-entered into cell E3. This formula was written correctly, and it uses references to the numbers in cells E1 through G1 to determine the correct index_number parameter. Fill in cells F1 and G1 with the correct index numbers, then copy the formula in cell E3 down to all the rows in columns E, F, and G. What number did you enter into cell G1?
Enter answer here
Q3. Now that the data is updated, create a pivot table that contains all of the data on the “Compiled Information” tab. Using the PivotTable Fields side bar, drag the “Unique ID” field to the VALUES area and summarize the information by Count. What is the Count of Unique ID?
Enter answer here
Q4. Update the pivot table to show “Order Date” grouped by month. Drag the “Sales” field into the correct area to show the Sum of Sales for each of the months listed. Which month had the lowest sales? (Your answer should be the name of the month.)
Hint: Remember that you can sort the data once it is in the pivot table!
Enter answer here
Q5. Continuing from the previous question, update the pivot table to show the Average of Sales by month. Which month had the highest average? (Your answer should be the name of the month.)
Enter answer here
Q6. Which Customer Segment contained the single order with the highest shipping cost?
[expand title=View Answer]
Consumer
Corporate
Home Office
Small Business
[/expand]
Q7. In the Corporate Customer Segment, what was the most common order quantity?
Enter answer here
Q8. In all segments, how many orders were placed in one quarter of the year but shipped in the next quarter (For example, an order in which Order Date = Qtr1 2009 and Ship Date = Qtr2 2009)?
Enter answer here
Q9. Create a line chart that shows the average of “Sales” for each customer segment by month, where each customer segment is represented by a line on the chart. Which of the following line charts shows the average sales data?
[expand title=View Answer]
The range on y-axis is 0-6000 with scale of 1000 units
.
The range on y-axis is 0-200000 with scale of 20000 units
.
The range on y-axis is 0-50 with scale of 5 units
.
The range on y-axis is 0-100 with scale of 10 units
[/expand]
.
Q10. The line chart shown follows a zig-zag pattern with decreasing peaks left-right
Line charts are useful for looking for trends in data and gaining quick insights. The chart above shows total Sales in 2009 by month in the Consumer segment. Which of the following could be inferred from the chart? Select all that apply.
[expand title=View Answer]
Month to month sales numbers are relatively volatile (>10% difference in sales compared to previous month).
In 2009, March and April were the months with the highest total sales.
In general, total sales numbers continued to rise
throughout 2009.
In 2009, June was the month with the lowest total sales.
[/expand]
.
Week 4 Quiz Answers
Quiz 1: Line Graphs
Q1. How can one change the formatting of a graph’s axis? Click all that apply.
[expand title=View Answer]
Right click anywhere inside the chart
Right click on the axis
Use the layout tab
Left click on the axis
[/expand]
Q2. Examine the line chart below (click the file link to view a larger version of the image). What will happen to the chart when one changes the content of cell B1 to “United States.”
[expand title=View Answer] The current line for Brazil will be relabeled “United States” in the legend. [/expand]
Quiz 2: Bar Graphs and Pie Charts
Q1. Approximately how much higher is the GDP of China compared to Brazil? Use billions of dollars, ex. 40,000,000,000 should be written as 40.
[expand title=View Answer] James P. Grant [/expand]
Left column- Brazil’s GDP at ~ $15 B; Center- China’s GDP at ~$60 B
Enter answer here
Q2. What can we not learn from the following pie chart?
[expand title=View Answer]Altogether, Adam, Brad, and Carol sold $100 worth of goods.[/expand]
Quiz 3: Pivot Charts
Q1. Pivot charts are powerful because they allow you to…
[expand title=View Answer] make charts interactive.[/expand]
Q2. The two sets of columns depict average values of unit and dollar sales respectively
What’s the fastest way to change the pivot chart above to view totals of unit and dollar sales?
[expand title=View Answer]
Change
“value field settings” for each variable from “average” to
“sum” of that variable.
[/expand]
Quiz 4: Scatter Plots
Q1. Which of the following reflects the main strength of a scatter plot?
[expand title=View Answer] Scatter plots help visualize relationships between two variables. [/expand]
Q2. What is the relationship between employee experience and sales demonstrated in the chart below?
[expand title=View Answer] As experience increases, sales increase. [/expand]
Quiz 5: Histograms Part 1
Q1. The columns in chart decrease in height (salaries) from left to right Based on the histogram above, what can you say about income?
[expand title=View Answer] Income is skewed to the right. The majority of individuals in this dataset have low salaries relative to others in this dataset.[/expand]
Quiz 6: Histograms Part 2
Q1. What do you need to do in Excel versions prior to 2016 to create a histogram?
[expand title=View Answer]
Create bins for your data.
Enable DataAnalysis ToolPak.
Make sure the minimum value is larger than the smallest bin value.
[/expand]
Q2. The graph shows 14 columns in 0-160 range The graph shows 7 columns in 0-160 range
What was done to transform the first histogram into the second one?
[expand title=View Answer] Bin widths doubled. [/expand]
Quiz 7: Advanced Graphing and Charting
Q1. Download the spreadsheet “chicagocrimedata.xlsx” below. Use the data in the “Question 1 – get data” tab for the remainder of the assignment.
Fill in the tab, “Question 1 – get data” using the VLOOKUP formula. What is the value in cell C200?
Enter answer here
Q2. Use a pivot chart to create a bar plot of the average assaults by day of week. Which days of week has the fewest assaults on average?
[expand title=View Answer]
Weekdays (Monday – Friday)
Weekends (Saturday and Sunday)
Sunday and Monday
Friday and Thursday
[/expand]
Q3. Modify the pivot plot to plot the average temperatures by day of week. What does it look like?
[expand title=View Answer]
The frequency on the y-axis varies from 60-80
The frequency on the y-axis varies from 51.4-53.2
The frequency on the y-axis varies from 56-63
The frequency on the y-axis varies from 2900-3300
[/expand]
Q4. Create a scatter plot of Assault (Y axis) vs. Temperature (X axis). What does it look like?
[expand title=View Answer]
The chart shows a scattered plot with a bell shaped trend
The scatter plot is upward sloping with closely aligned data points ranging from 28~110 along x-axis
The scatter plot is upward sloping with data points ranging from 700~1600 aling x-axis
The scatter plot is upward sloping with data points ranging from 10~100 along x-axis
[/expand]
Q5. What is the possible relationship between crime and temperature?
[expand title=View Answer]
Crime increases as temperature rises.
Crime decreases when the temperature rises.
Temperature and crime are unrelated.
Temperature increases as crime rises.
[/expand]
Q6. Find the minimum and maximum number of assaults, and create 10 evenly spaced bins that cover the data on assaults. What is the approximate bin width?
[expand title=View Answer]
8
10
15
2
[/expand]
Q7. Create a histogram using the assault data and 10 bins. What can you conclude based on the histogram?
[expand title=View Answer]
The distribution of assaults is skewed to the right.
The distribution of assaults is symmetric.
100 assaults is about a typical day in Chicago.
Crime is highest midweek?
[/expand]
Introduction to Data Analysis Using Excel Course Review
In our experience, we suggest you enroll in Introduction to Data Analysis Using Excel courses and gain some new skills from Professionals completely free and we assure you will be worth it.
Introduction to Data Analysis Using Excel for free, if you are stuck anywhere between a quiz or a graded assessment quiz, just visit Networking Funda to get Introduction to Data Analysis Using Excel Coursera Quiz Answers.
Get All Course Quiz Answers of Business Statistics and Analysis Specialization
Introduction to Data Analysis Using Excel Coursera Quiz Answers
Business Applications of Hypothesis Testing and Confidence Interval Estimation Quiz Answers
Linear Regression for Business Statistics Quiz Answers
