Assigned: 10/29/03
Due: 11/03/03 at the start of
class (time will only be available in
class on 10/29)
Remember: late penalties are heavy
Assignment:
Imagine that you have been asked to create the spreadsheet shown
on the attached page. Note that formulas are required
in all cells except for the basic info that cannot be calculated. Print
the Spreadsheet.
Turn In:
1)
Print
Out of Spreadsheet
2)
Disk
Details:
1) Type in text headings as shown. Note that
· Autoformat will take care of some of the formatting, but not all. Be sure that if you use AutoFormat you use it before investing any effort into formatting
· “Speed up %”, “Difference from Mean,” “Absolute Value”, and “Above Threshold” headings are in one cell each
· “m / s at” should be in one merged cell, centered above 25 C and 1000 C columns
· “mi / hrs at” should be in one merged cell, centered above 25 C and 1000 C columns
· Average Speed of Gas Molecules should be in one merged cell, centered above the first 9 columns
2) Speeds in m/s should be typed as shown.
3) Speed up % shows the increase in speed from 25 C to 1000 C. Formulas must be used. Formatting should show result as a percentage with one decimal place.
4) Conversion is labeled and is typed as shown, boxed and colored light blue.
5) Threshold for Difference is labeled and is typed as shown, boxed and colored light blue.
6) Speeds in mi / hr must be calculated using formulas. Formulas using absolute (or mixed) addresses MUST be used. (1 m/s = 2.237 mi / hr)
7) Means are to be calculated for speeds at 25 C, 1000 C (regular and metric), and Speed Up %. Formulas must be used. Format data to have one decimal place.
8) Conditionally format the speeds at 25 C, 1000 C (regular and metric), and Speed Up % columns so that any values greater than the respective means are colored red.
9) Difference from mean is the difference between the speed up for a particular gas and the mean speed up. Formulas using absolute (or mixed) addresses MUST be used. Formatting should show result as a percentage with one decimal place.
10) Absolute Value must be calculated using a formula. There is a built function (fairly obviously named) specifically designed to do this, but this could also be done using IF. Formatting should show result as a percentage with one decimal place.
11) Above Threshold MUST be calculated using formulas. The formula must use an IF and an absolute address for Threshold for Difference. The resulting value should be “Yes” if the Absolute Value is greater than the threshold for difference. Otherwise it should be “No”. This is made use of below. Center the values in these cells.
12) Format as shown. Some thick borders will definitely need to be added to be as shown
13) Number of Gases Above Threshold is labeled, boxed, and must be calculated using a formula (hint: there is a built-in function appropriate for this task; “COUNTIF”)
14) “Prepared by “ then your actual name (instead of Your Name Here) is typed at the bottom
15) Include headers and footers on the spreadsheet as shown – file and sheet in header, date, time, page, and total number of pages in footer, using fields so they automatically update.
16) Ensure that the spreadsheet will print on one page
17) After saving the document again, print it.
Make sure you save your document to a floppy disk. Hand in the disk and your print out.