ENGG1003 IT Foundation Assignment 1 Spreadsheet Exercise
ENGG1003 Assignment
Spreadsheet Exercise (Assignment 1) - Attempt 1
- You need to answer ALL the questions in the exercise.
- You can only try 2 times in total. Your final score would be the MAXIMUM points you got among the 2 trials .
- Remember to SAVE your answer before you leave. Otherwise, all answers will not be saved for you.
- Download Data Set 1
Question 1 (5 marks)
Please answer this question using the data in "Data Set 1". If you correctly rename the worksheet "Rename Me" to "Monthly Report", the value in cell A2 will change from "N/A" to a numerical value. What is that numerical value?
Answer
Question 2 (5 marks)
Please answer this question using the data in "Data Set 1". In the worksheet "Copy&Paste", copy the cells N5:P11, and paste the copied cells "as values" into cells B22:D28. After you have successfully copied the cells "as values" (not as formula) into the target cells, what is the integer shown in cell B1?
Answer
Question 3 (5 marks)
In an Excel worksheet named "Balance", how can one specify in a formula the cell reference of a cell located at row 3, column 4 in another worksheet named "Sheet2"?
A. D3!Sheet2 B. C4!Sheet2 C. Sheet2$C$4 D. Sheet2!C4 E. Sheet2!D3
Save Answers
Question 4 (5 marks)
In Excel, if we were to round a number in cell B2 to the nearest integer (i.e., number with zero decimal places), and store the result in cell C2, which of the following formulas can we use in C2? For examples, If the value in B2 were 123.678, the value in C2 should become 124 If the value in B2 were 123.123, the value in C2 should become 123
A. =ROUNDTOINT(B2) B. =ROUND(B2) C. =TRUNC(B2) D. =ROUND(B2, 0) E. =ROUND(0, B2)
Question 5 (5 marks)
Please answer this question using the data in "Data Set 1". What is the size of the least expensive product that is brown in color?
Hint: You can use the "Filter" feature of Excel to help you find the answer.
A. Small B. Extra Large C. Extra Small D. Large E. Medium
Question 6 (5 marks)
In an Excel worksheet, suppose a formula =C$4+$C4
is copied from A2 to D1, what will the formula become in D1?
A. =C$3+$F4 B. =C$4+$C4 C. =F$4+$C3 D. =C$3+$C3 E. =D$3+$D3
Save Answers
Question 7 (5 marks)
Please answer this question using the data in "Data Set 1", and please exclude comma (,) in your answer. What is the sum of "Stock Quantity" of the products from number 105 to number 2893?
Answer
Question 8 (5 marks)
Please answer this question using the data in "Data Set 1", and please round your answer to two decimal places, and do not include any dollar sign ($) and comma (,) in your answer. What is the positive difference between the average unit price and the median unit price of all 5000 products?
Answer
Question 9 (5 marks)
Please answer this question using the data in "Data Set 1", and please round your answer to two decimal places, and do not include any dollar sign ($) and comma (,) in your answer. Suppose the "Total Value" of a product is equal to "Unit Price * Quantity" of the product. What is the median "Total Value" among the first 2500 products (i.e., products from number 1 to number 2500)?
Answer
Save Answers
Question 10 (5 marks)
Please answer this question using the data in "Data Set 1". How many different products meet both of the following attributes?
- Color = Red
- Discount Type ≠ A
Hint: You can use countifs(), countif(), or pivot table to help you find the solution.
Answer
Question 11 (10 marks)
Please answer this question using the data in "Data Set 1". Exactly 342 different products are in this color. What is this color?
Hint: You can use countif() or Pivot Table to help you find the answer. The worksheet named "Product_Attributes" contains a list of all possible colors.
Answer
Question 12 (10 marks)
Please answer this question using the data in "Data Set 1". If an item is "Brown" in color OR "Extra Small" in size, its "Promotion Unit Price" is calculated as Unit Price * 0.7 + 800. Otherwise, the Promotion Unit Price of the item is the same as its unit price. How many different products have a "Promotion Unit Price" that are more than $5000?
Hint: One possible approach to find the answer is as follow:
- In a new column, use IF() and OR() functions together to calculate the new unit price. (Use Google to search for Excel "OR Function" "IF Function" for manual or examples).
- Use COUNTIF() to count the number of Promotion Unit Price that met the condition specified in the question.
Answer
Type your answer here
Save Answers
Question 13 (10 marks)
Please answer this question using the data in "Data Set 1". Suppose the "Discounted Unit Price" of a product is equal to "Unit Price * (1 - Discount Rate)" of the product, where the "Discount Rate" is derived from the product's "Discount Type" according to the following table:
| Discount Type | Discount Rate |
|---|---|
| A | 0% |
| B | 10% |
| C | 15% |
| D | 5% |
| E | 20% |
How many different products have a "Discounted Unit Price" that are less than $1500?
Hint: One possible approach to find the answer is as follow:
- Insert a table that maps "Discount Type" to Discount Rate" into a worksheet.
- In a new column, use vlookup() to find out the discount rate for each product.
- In another column, calculate the "Discounted Unit Price" for each product.
- Use countif() to count the number of "Discounted Unit Price" that met the condition specified in the question.
Answer
Type your answer here
Question 14 (10 marks)
For this question, please use the data in the worksheet "Chart Data". Which of the following "100% Stack Bar" charts shows how different sizes (XS, S, M, L, XL) of Dress shirt, Tshirt, Nightshirt, Jacket, and Jersey were sold in March? Hint: Create a chart using only the data mentioned in the question, and compare the chart to those shown in the choices.
Question 15 (10 marks)
For this question, please use the data in the worksheet "Chart Data". Which of the following 3D pie charts shows the percentage of size-M items sold in March? Hint: Create a 3D pie chart using only the data mentioned in the question Change the chart style. To do so, click on the chart, select the "Design" menu, and then pick the style that matches those shown in the choices (try style 8). Compare the chart to those shown in the choices. Note: The Excel on your computer may have different default settings and uses different colors, textures, and font for your chart.
Save Answers
