Computer Application
3 years ago 8
Quiz6.pdf
Quiz6.pdf
Name: ________________________ Set: ____
Quiz 6 Excel
MICROSOFT EXCEL: Tasks Possible Marks
Your Marks
Section sub-total: 20 1. Open a new Excel file, and save it in your personal folder as Student#_Quiz6.xlsx 2. If there are more than one worksheet, keep one and delete the remaining. Rename
the worksheet as “Tank 1”. 1
3. Open the MaterialDensities.csv file (it is “comma-delimited”) in the Learning Hub and move or import it to your new workbook, to the left of your Tank sheet. 1
4. Using the screenshot below (next page), construct a worksheet on your “Tank 1” sheet which will calculate the volume and weight of a hollow spherical tank.
Formats: • Columns A:D have width 18.00 (131 pixels). • Cells A1:B1 is merged, blue background, white text, row height = 30.0 • The border around A1:B3 is a thick border. • Row 5 uses a manual line break within cells to get units on a 2nd line.
All other formats – including cell borders, alignment, cell formats including font, number formatting and decimal places – should correspond to the screenshot.
4
Data Table / Formulas: Outer Diameter:
• Enter the value 0.50 in cell A6. Complete a series in increments of 0.05, to a maximum outer diameter of 5.00
1
Inner Diameter: • Enter a formula in B6 and copy down.
d = D – 2t where d = inner diameter, D = outer diameter, t = thickness
• D and t must be cell references
2
Volume: • Enter a formula in C6 and copy down.
V = 4.189 * ( (0.5D)3 –(0.5d)3 ) • D, and d must be cell references and the powers must be represented as
powers (not something like D*D*D).
2
Weight: • Enter the formula W V e= × in D6 and copy down.
where e = density of aluminum found on the Material Densities sheet • V and e must be cell references
2
5. Convert the data on both worksheets to a table • Tank worksheet (A5:D96) – Table style Medium 2 with banded
columns but no banded rows • Material Densities worksheet (A1:B37) – Table style Medium 1 with no
banded rows or columns.
4
6. On the Tank worksheet, turn the Material information in B3 to a dropdown list which calls the materials from the Material Density worksheet. Include the following information:
• Input Message: Title - Material, Message – “Select Material from List” • Error Alert: Style – Warning, Title – “Warning”, Message – “Material
not in list” Make sure to save you worksheet with the material as “aluminum – melted”
3
Resave your document and submit to the Learning Hub Dropbox.
Quiz6.pdf
Name: ________________________ Set: ____
Quiz 6 Excel
MICROSOFT EXCEL: Tasks Possible Marks
Your Marks
Section sub-total: 20 1. Open a new Excel file, and save it in your personal folder as Student#_Quiz6.xlsx 2. If there are more than one worksheet, keep one and delete the remaining. Rename
the worksheet as “Tank 1”. 1
3. Open the MaterialDensities.csv file (it is “comma-delimited”) in the Learning Hub and move or import it to your new workbook, to the left of your Tank sheet. 1
4. Using the screenshot below (next page), construct a worksheet on your “Tank 1” sheet which will calculate the volume and weight of a hollow spherical tank.
Formats: • Columns A:D have width 18.00 (131 pixels). • Cells A1:B1 is merged, blue background, white text, row height = 30.0 • The border around A1:B3 is a thick border. • Row 5 uses a manual line break within cells to get units on a 2nd line.
All other formats – including cell borders, alignment, cell formats including font, number formatting and decimal places – should correspond to the screenshot.
4
Data Table / Formulas: Outer Diameter:
• Enter the value 0.50 in cell A6. Complete a series in increments of 0.05, to a maximum outer diameter of 5.00
1
Inner Diameter: • Enter a formula in B6 and copy down.
d = D – 2t where d = inner diameter, D = outer diameter, t = thickness
• D and t must be cell references
2
Volume: • Enter a formula in C6 and copy down.
V = 4.189 * ( (0.5D)3 –(0.5d)3 ) • D, and d must be cell references and the powers must be represented as
powers (not something like D*D*D).
2
Weight: • Enter the formula W V e= × in D6 and copy down.
where e = density of aluminum found on the Material Densities sheet • V and e must be cell references
2
5. Convert the data on both worksheets to a table • Tank worksheet (A5:D96) – Table style Medium 2 with banded
columns but no banded rows • Material Densities worksheet (A1:B37) – Table style Medium 1 with no
banded rows or columns.
4
6. On the Tank worksheet, turn the Material information in B3 to a dropdown list which calls the materials from the Material Density worksheet. Include the following information:
• Input Message: Title - Material, Message – “Select Material from List” • Error Alert: Style – Warning, Title – “Warning”, Message – “Material
not in list” Make sure to save you worksheet with the material as “aluminum – melted”
3
Resave your document and submit to the Learning Hub Dropbox.