EECS 1520 3.0 — Computer Use: Fundamentals

Winter 2016

Announcements

Lecture Schedule

Course Outline

Homework

Grades

Homework

• How to Prepare Your Homework Submission
• Cover Page:    Word Template    Completed Example

Chapters:    0    1    2    3    4    5    6    7    8    9   

Chapter 4 Homework

• Glade Manual, Chapter 4

Support Files

• Exercise 1
• Exercise 2

What to Submit

• Cover Page
• Sheet1 worksheet (formula view only) for Exercise 1.1
• Accounts worksheet (formula view only) for Exercise 1.2
• Binary_Conversion worksheet (formula view only) for Exercise 1.3
• Adder worksheet (data view) for Exercise 2
• Adder worksheet (formula view) for Exercise 2

Don't forget to put your name and student number in the header, and worksheet tab name in the footer, of each worksheet in the checklist.

Printing Very Wide Spreadsheets

Some of the spreadsheets produced in Lab 4 are very wide and are, for all practical purposes, impossible to print on a single page. You will have to print it across two or more pages. Here are the steps I suggest you go through to prepare your spreadsheet for printing: After switching to the formula view, adjust the column widths so that the entire formula is visible in each column. The ugliest situation is when one very thin column spills over onto another page. This can often be eliminated by adjusting the margins slightly and/or shrinking the printed spreadsheet slightly. Go to "Page Setup…" under "File". There are four tabs: Page, Margins, Header/Footer, and Sheet. The margins can be adjusted under "Margins". On "Page" you will first find "Orientation". Switching to "Landscape" turns the page sideways and gives you an additional 2-1/2 inches, which might do the trick. If not, under "Scaling" you can shrink (or enlarge) the printed output, just as you can on a photocopier. Under "Adjust to" you can enter a number and determine the desired magnification by trial and error. Enter a scale, click OK and view the results using "Print Preview". OR, you can let the computer determine the optimal magnification. Under "Scaling", the second line begins with "Fit to:". Here you enter two numbers. For "tall" enter a ridiculously large number like 100, so that this part has no influence on our goal. In the first box enter the number of pages wide we want. Start with 1 page and go up until you find an acceptable choice. After entering the number, click OK. Now go back into "Page Setup…" and look at the number next to "Adjust to". This is the optimal magnification the computer has determined to print on one, two, three or more pages. As a rule of thumb, if this number is less than 70% the magnification makes the spreadsheet potentially difficult to read and I'll try the next higher number of pages wide. Use "Print Preview" to see what the printed page will look like. Lastly under "Sheet" you can turn on grid lines on the printed page and also specify the order in which to print the pages: column by column or row by row. By this we mean columns or rows of pages, not the columns and rows of the spreadsheet itself.

Discussion, Hints & Tips

Exercise 1.1:

Straight forward, just follow the instructions.

Exercise 1.2:

A name like Smith John is turned into ID JSMITH

Exercise 1.3:

In this exercise you implement a spreadsheet that converts 8-digit (natural) binary numbers to decimal by using the formula found at the bottom of page 35 of the textbook. An 8-digit binary number has the form

and the formula is

In the terminology of the exercise, the named range value denotes the powers of 2, the named range bit the binary digits in the number and extension is the product of these two, namely the terms  in the formula.

Notice that when you enter a number into input, i.e. cell B2, leading zeros are dropped.  Transforming the input value to Normal form turns the input binary number into a string of exactly 8 zeros and ones including leading zeros.  You can right justify the Normal form so it looks like a number. (Numbers are right justified and text is left justified, by default.)

Take care in how you create the formulas you place in cells C6, C7 and C8.  Using the given named ranges you want to create formulas that can be copied by fill, right across columns C to J.

Exercise 2:

In this Exercise you write Excel formulas to simulate a 4-bit adder. This is equivalent to evaluating the formula at the bottom of page 35 of the textbook for n = 4,

where the 4-bit (bit = binary digit) number is

Note that this notation is slightly different from the textbook, which uses subscripts 4 through 1, rather than 3 through 0.

In the exercise, two numbers, A and B, are added. Each is a 4-bit two's complement representation. Using the above notation,

                      A =

where , the left-most digit corresponds to single cell named range A3_, etc.

Two things need to be done:

1)   the digits from A and B have to be extracted and placed in A3_, A2_, A1_, A0 and in B3_, B2_, B1_, B0. This is done using the functions LEFT, RIGHT or MID. For example, A3_ is obtained by =LEFT(A,1).  But, this only extracts a character that looks like a number, but is actually just text. To convert it to a number you have to use the function VALUE, so the final formula is

                               =VALUE(LEFT(A,1)).

2)   On the Adder worksheet you'll find a diagram for the 4-bit adder. Move it aside.  Study the named ranges we have defined, which you can find on the Comments worksheet.  Print out the diagram in the spreadsheet or use Figure 4.5 to label all the gates with these named ranges and the corresponding cell references. Then compare these with the labels in either Figure 4.4 or Figure 4.3.

For example, consider the left-most full adder. This adds the left-most two digits.

Now, all you need to do is make the substitutions in the formulas given on page 4-9 below Figure 4.4.

One more issue. The logical functions, AND, OR, NOT accept 0 or 1 as parameters but only produce TRUE/FALSE. To convert these back to 0 or 1, use an IF:

                            =AND(x,y)              produces a TRUE/FALSE,

but                                                                                                    

                            =IF(AND(x,y),1,0)        produces a 0 or 1