CS 221 Fall 2011 Problem Set 1

The purpose of this assignment is to reinforce the concepts you have been taught in lecture and lab.

This assignment has two parts; you will submit a single "zip" archive containing your answers to all three parts.

To submit your solutions, follow these steps:

• Create a folder titled Last_First_HW1 (replacing "Last" and "First" with your actual names) on the Desktop.
• Place the three files containing your answers for the problems --- one ".txt" or ".pdf" and two ".xlsx" files, named as described below --- into that folder. Nothing else should be in the folder.
• Create a zip file containing the folder and its contents. (The main course web page has instructions for creating a zip archive.)
• Important: Verify that the contents of the zip archive are correct. It should contain the directory (folder) and the three files, and nothing else. (Do this by double-clicking the zip file.)
• Upload the zip archive via the portal.
• Important: save the folder and its contents to a flash drive or elsewhere, for two reasons: (i) in case something goes wrong with your submission; and (ii) we will sometimes build on your solutions in later problems.

Part 1: CS Concepts

1. In class we discussed the representation of floating-point numeric values using fixed numbers of bits. For this problem, you are to determine, for MATLAB and for Excel:
   1. The largest (greatest magnitude) positive and negative numbers that can be entered.
   2. The largest (greatest magnitude) positive and negative numbers that can result from a computation.
   3. The smallest positive and negative numbers that can be entered.
   4. The smallest positive and negative numbers that can result from a computation.
   Note that you will need to use scientific notation to represent these values.

   There are several ways to find the answer to this problem. Probably the easiest is to use the help function of the respective tools. (Hint: Search for "limits" in Excel, and something like "maximum positive floating" in MATLAB.)

2. In this problem you will experiment with numbers and calculations at the edge of the tools' limits.
   1. Open an Excel spreadsheet. Type "12345678901234567" (without the quotes) into cell A1. (Format the cell so it is wide enough to hold all the digits.) What is displayed in the cell?
   2. In cell A2, type the formula "=A1+1" (without the quotes). What is displayed in this cell?
   3. Based on the answers to these questions, what can you say about the maximum difference in magnitude of numbers to be added in Excel?
   4. Repeat the first three parts of this problem for MATLAB. (That is: assign the value 12345678901234567 to a variable, and then add one to it.)
   5. What happens if you exend the number you entered by one digit?

3. Convert the following (decimal) numbers to binary. (See the slides from Tuesday 6 September lecture for instructions if you don't know how.) Show the steps of your work to get credit.
   1. 592
   2. 1,333
   3. 2,777,981

4. What decimal numbers do the following 16-bit (positive) binary numbers represent? Show the derivation of your answer to get credit.
   1. 1000001011100011
   2. 0000011111110010

Part 2 Excel Problems

5. Tutorial 2.3 in Chapter 2 of your textbook shows an example of computing the distance traveled by a 20-speed bicycle per revolution of the crank (pedals), for each combination of chainring (small or large gear on the crank) and cassette (rear sprocket). Go through that example in preparation for this problem.

   You will create a spreadsheet along similar lines, but with a slightly different layout and purpose. (The gear sizes are those of Prof. Calvert's 28-year-old Dawes 10-speed.)

   1. In cells A1:E1, type the headings "Chainring Gear", "Cassette Gear", "Chainring Teeth", "Cassette Teeth", and "Gear Ratio" (leaving off the quotes).
   2. The idea is to create a table showing the gear ratio for all possible combinations of gears, from (1,1) to (2,5). The numbers of teeth on the chainring gears are 40 and 52. The numbers of teeth on the five cassette gears are 28, 24, 20, 17, and 14. Fill in Column E with a formula that gives the ratio of the number of teeth on the chainring gear to the number of teeth on the cassette gear.

      Format the cells so the gear ratios are presented to two decimal places. Also, make the column headings bold.

      When you finish, the spreadsheet should have 10 rows, arranged like this:

      Chainring Gear	Cassette Gear	Chainring Teeth	Cassette Teeth	Gear Ratio
      1	1	40	28	1.43
      1	2	40	24	1.67
      ...
      2	1	52	28	1.86
      2	2	52	24	2.17
      ...

   3. Now sort the rows according to gear ratio, from lowest to highest.
   4. Another bike has three chain rings with 34, 44, and 54 teeth. It also has 9 cassette gears, with 25, 23, 21, 19, 17, 15, 13, 12, and 11 teeth. Expand your spreadsheet to accommodate this larger number of gear combinations, and input these numbers. Again, sort the rows by gear ratio. Save the final spreadsheet as HW1Part2_1.xlsx.
   5. You may have noticed that this spreadsheet has quite a lot of redundant information in it. In particular, the number of teeth on each chainring has to be input once for each cassette gear, and the number of teeth on each cassette has to be input once for each chainring. We would like to only store the number of teeth once for each gear. Later we will see how to do this; you may want to see if you can come up with a way on your own.

6. In this problem you will create a spreadsheet for storing and computing students' grades. The grades themselves and the student ID numbers will be filled in by using the built-in Excel function RANDBETWEEN().
   1. Read the Excel Help information on the function RANDBETWEEN() and make sure you understand how it works; experiment with it if you need to.
   2. Your spreadsheet will have 10 columns. The layout should be lie that shown below; the first row is filled in as an example.

      Student ID	HW1	HW2	HW3	HW4	Q1	Q2	Final	Total	Grade
      101078	79	88	100	55	88	99	40	70.95	C

      There are four homework assignments, worth a total of 50% of the final grade; two quizzes, worth 10% each, and a Final Exam worth 30% of the final grade. (Each grade is out of 100 points possible.) The column "Total" is the weighted sum of the scores, using the above weights. The "Grade" column contains a letter grade, assigned according to the usual scale:

      90 <= Total	A
      80 <= Total < 90	B
      70 <= Total < 80	C
      60 <= Total < 70	D
      Total < 60	E

   3. Fill in the column for "Total" and "Grade" with formulas that give the correct answer. (You can use the first line in the table above as a test case.)

      Note: make sure your formula for the Total is general - it should not need to be changed, e.g., if another homework assignment is added. The right way to do this is to sum the scores from one component (e.g., homework assignments), then divide by the total points possible on that component (in this case, 400), then multiply by the percentage of the grade that comes from homeworks (in this case, 50). Do the same thing for each component and add them up.

      Hint: you can compute the total points possible on a component by counting the number of grades in that component using the COUNTIF() function and multiplying by 100. You might want to leave a blank column between each pair of components as a "marker".

      You will need to use a nested IF-expression in the Grade column.

   4. Now fill in 20 rows of the table using the RANDBETWEEN() function. Student ID numbers should be between 100001 and 199999. The minimum score on a homework assignment is 0. The minimum score on a quiz is 40, and the minimum score on the final is 25.

      Verify that your formulas are correct by hand calculation.

   5. Save the spreadsheet and re-open it. Notice that the values, including the student IDs, have all changed. (This is obviously not a very good way to do grading.)

      We would like to generate the values using RANDBETWEEN() and then "freeze" them. We can do this using the "Paste Special" function.

      1. Select the entire table, excluding the header. Do not select any columns outside of the 10 shown above.
      2. From the main menu, select Edit->Copy (or type ctl-C).
      3. Click in the "Student ID" column of the first empty row below the table.
      4. From the main menu, select Edit->Paste Special...
      5. In the popup window, under "Paste", choose "Values", then click "OK". Notice that the values in the original table changed when you pasted. This is because RANDBETWEEN chooses a new random value each time the spreadsheet is recalculated. However, the values pasted are the same ones that were copied.
      6. Now hide the original 20 rows, by clicking on the upper border of the 21st row and dragging it up to the bottom of the row containing the headers.

      Now you should be able to save and re-open the spreadsheet without the (visible) rows changing.

   6. Save the spreadsheet as HW1Part2_2.xlsx.