1. Test data
Using the provided spreadsheet data from the tab names "test data", do the following:
i) Create a new tab named "test data answers".
ii) Create a new table on your new tab to display the following information:
average value for meas 1
average value for meas 2
max value for meas 1
max value for meas 2
min value for meas 1
min value for meas 2
standard deviation for meas 1
standard deviation for meas 2
Keep the formulas in your table that you use.
Round your answers to a reasonable number of significant digits.
2. Sales data
Using the provided spreadsheet data from the tab named "sales data", do the following:
i) Create a new tab named "sales data answers".
ii) Create a table (or multiple tables) on your new tab to display the following information:
Average sales for all data
Average order quantity for all sales
# of sales for all data
Max sales value
Date of max sales value
min sales value
date of min sales value
# of sales where the order quantity was greater than 25
average sales if the order quantity was greater than 25
Keep the formulas in your table that you use.
Round your answers to a reasonable number of significant digits.
3. Coffee Temp
You enjoy coffee, but are particular about the temperature (T). If the temperature is greater than or equal to 70 degrees Celsius, the coffee is too hot to drink; less than or equal to 45 degrees Celsius is too cold for you.The initial temperature that your coffee pot brews is represented by T0. The cooling of your coffee can be modeled by the equation below, where time (t) and the cooling factor (k) are in units per second:
T = T0e-kt
i) Create a new tab named "coffee temp"
ii) At the top of the worksheet, create an area where the user can modify four properties of the coffee. For a sample test case, enter the following data
? Initial temperature (T0); for the initial problem, set to 80oC.
? Cooling factor (k); set to 0.001 per second [s-1].
? Temperature above which coffee is "too hot" to drink (Thot); set to 70oC.
? Temperature below which coffee is "too cold" to drink (Tcold); set to 45oC.
iii) Create a temperature profile for the coffee:
? In column A, generate a time range of 0-300 seconds, in 15-second intervals
? In column B, generate the temperature of the coffee, using the equation given and the input parameters set by the user (T0 and k).
iv) In column B, the temperature values should appear on a red background if the coffee is too hot to drink, and a blue background if it is too cold using conditional formatting.
v) In column C, create a warning next to each temperature that says "Do not Drink" if the calculated temperature in column B is too hot or too cold in comparison with the temperature values the user enters.
Attachment:- hw4_data.xlsx_.rar