Assigned: 11/05/03
Due: 11/10/03 at the END of class
Remember: late penalties are heavy
Assignment:
Imagine
that you have been asked to create the spreadsheet, XY scatter graph and
scenario summary report shown on the attached pages. Note that formulas are required in all cells except for the
basic info that cannot be calculated. Print the Spreadsheet. Please note
that NO knowledge of Biochemistry is assumed. All necessary info is specified
in the detailed instructions.
Turn In:
1) Print Out of Spreadsheet, XY Scatter graph, and the scenario summary report. Make sure you enter your name on each!
2) Disk, with your name on it.
Details:
1) The spreadsheet should be formatted roughly as shown, including title, headings, totals, etc. The title should be centered across the left 10 columns using merge and center and be Bold. “Relative Probabilities” and “Prediction” should be centered across 3 columns using merge and center, and be bold. “Error” should be centered across two columns using merge and center, and be bold. Other headings are bold text. Border lines should be included as shown.
2) Amino Acids, and Relative Probabilities (3 columns) should be typed in as shown. Format to show the appropriate number of decimal places as shown. Hypothesized thresholds (Alpha, Beta, and Turn) represent assumptions and should be typed in as shown (along with their labels). Surround them with a dark border and fill the cells with a light blue background color. All other values that you see should be calculated using formulas!!
3) Predictions (Alpha, Beta, and Turn – 3 columns) must be calculated using formulas involving IFs, and using absolute references for any references to Hypothesized thresholds.
· An alpha prediction column entry should get a 1 if the Alpha relative probability is larger than the alpha hypothesized threshold – otherwise it should be a blank.
· A beta prediction column entry should get a 1 if the beta relative probability is greater than the beta hypothesized threshold – otherwise it should be a blank.
· A turn prediction column entry should get a 1 if the alpha relative probability is less than the threshold listed for Turn hypothesized threshold and the beta relative probability is less than the turn relative probability. Hint: your logical test for your IF will need to use the AND function.
4) The Sum column must be calculated using formulas. It is the total of the three preceding columns.
5) The Error columns (Too Many and Too Few) must be calculated using formulas that use IFs. There are too many predictions if the sum entry is more than one and too few if the sum entry is less than one. Otherwise, these should be blank.
6) At the bottom of each Error column, calculate (using formulas) the total number of errors of each type.
7) Total Errors must be calculated using a formula – it is the total of both types of errors.
8) Type your actual name where the example says “Your Name”. And type the credit acknowledgement in the cell below that.
9) Put the current date and time, file name and sheet name, page “of” possible pages in the header as shown – using fields so they automatically update.
10) Make sure that the whole spreadsheet prints on one page. Adjust under Page Setup if necessary.
11) Create the XY scatter graph from the (Alpha Helix and Turn Relative Probabilities) data as shown.
12) Make X axis, Y-axis, and Graph Title as shown – including YOUR ACTUAL NAME where the example has YOUR NAME HERE.
13) Adjust the X-axis and Y-axis as shown – so that the scales are as shown, and so minor ticks are shown outside the box.
14) Make sure that the background color on the graph is white!!!
15) Fit a curve to the data plotted, and include the equation and the R-squared value on the chart. Make sure that they show up clearly.
16) Put the current date and time, file name and sheet name, page in the header and footer as shown – using fields so they automatically update.
17) Create 3 scenarios for the spreadsheet using Excel’s capabilities – “Simplified Chou Fasman”, “More Restrictive Alpha Beta”, and “Easier Turn”. Use values for scenarios as shown in the printed scenario summary report.
18) Create the scenario summary report.
19) Add “Autogenerated by Microsoft Excel under the direction of Your Name” to the bottom of the report (except use your actual name instead of “Your Name”). This is the only thing that you should type on this page! (you may change the addresses under “Changing Cells” and “Result Cells” to descriptive names if you like)
20)
Put the current date and time in the header as shown –
using fields so they automatically update.
21) Print the spreadsheet, XY Scatter graph, and the report.