Homework: what to submit
Chapter 4
Exercises to be submitted this week:
Chapter (Lab) 4: Exercises 1.1, 1.2 and 1.3
Exercise 2 (Binary Adder)
Check list:
· 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 your name and student number in the header and
worksheet tab name in the footer of each worksheet in the
checklist.
A note about 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:
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
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.
Label in Label in
Fig. 4.4 Fig. 4.5
A A3_ (cell E4)
B B3_ (cell D4)
XOR1 XOR3 (cell E6)
AND1 AND3 (cell D6)
C OR2 (cell F10)
Sum XOR23 (cell E8)
AND2 AND23 (cell D8)
Carry OR3 (cell C10)
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