Assigned: 04/07/03
Due: 04/11/03 at the start of
class (time will only be available in
class on 04/07)
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)
The
spreadsheet should be formatted as shown, including title, headings, totals,
etc. AutoFormat can only take care of SOME of the formatting, at best; you will
need to do some formatting on your own. The title should be centered across all
of the columns (using Merge and Center), and be 18 point, Bold. Grams and
Calories should be bold and underlined. Other column headings are bold text;
Food names are regular text, other row headings are bold. Border lines should
be included as shown.
2)
Grams
of protein, fat and carbohydrates need to be typed in as numbers. Formatting
should be used to control the number of digits after the decimal point.
3)
The
total grams must be calculated using formulas. Formatting should be used
to control the number of digits after the decimal point.
4)
Calories
for protein, fat, and carbohydrates must be calculated using absolute
references – using the numbers at the bottom of the sheet – a gram of protein
is four calories, a gram of fat is nine calories, and a gram of carbohydrates
is four calories. Formatting should be used to control the number of digits
after the decimal point.
5)
The
total calories must be calculated using formulas. Formatting should be
used to control the number of digits after the decimal point.
6)
Calories
per gram and percent of calories from fat must be calculated using
formulas. Formatting should be used to control the number of digits after the
decimal point, and to show the percent as a percent.
7)
The
“GOOD” or “BAD” for a food must be filled in using formulas involving Ifs and
absolute addresses – using the values at the bottom of the spreadsheet – grater
than 30% is bad, less than 10% is good, anything else is nothing.
8)
Max,
Median, Mean, and Min must be calculated using formulas. Format as
shown.
9)
Conditional
formatting should be (must be) used to format the highest value in each
of the last 6 numeric columns as red, and the lowest value as green.
10)
Type
your actual name where the example says “Your Name”.
11)
The
areas at the bottom of the spreadsheet holding “assumptions” (calories per
gram, and good and bad percentage), should be formatted to be surrounded by
thick black borders and with cells shaded a light blue.
12)
Put
in headers and footers as shown. Header should include file name and sheet. Footer
should include date and time and Page x of y. You must use real headers and
footers, so values will adjust automatically if appropriate (Don’t just type
this info info cells!!)
13) Make sure that the whole spreadsheet prints on one page. Adjust
under Page Setup if necessary