CS 130 Lab #3: Binary conversion, via "programming"
In this lab you will "write a program" to do conversion to binary numerals
(and other bases as well) using a spreadsheet (Microsoft Excel will do,
and is installed on the lab machines, although you could use any spreadsheet).
The goals of this third lab of the course are as follows:
- simple skills: to learn a bit (!) about the practical use of spreadsheets
- a first program: to get a computer to do what you already know how to do
yourself, via written "instructions"
- abstraction: to see an example of how a solution to a problem can be
abstracted or generalized away from some specific details
- an experiment: how well can students in the course do in finding and
reading technical documentation on their own?
Here are some concepts that will be introduced briefly during lecture and
which you will find useful in developing your spreadsheet;
you will be expected to fill out the background on these concepts by reading
documentation on the spreadsheet program.
You may also wish to use various formatting options (font, color, number,
border) to make your spreadsheet easier to read and understand.
- value: a value is, for our purposes, a fixed number entered into a
spreadsheet cell (the little boxes) or a number which appears
in the cell, but was calculated according to a formula.
- formula: a formula is an algebraic expression (using e.g. + and *)
which specifies how a cell's displayed value is to be computed.
A formula is indicated by typing an "=" sign as the first entry into
the cell's contents.
- reference: in addition to operators (+, *, -, etc.) and fixed numerals
(1, 2 10, etc.), a formula may contain references to other cells: these
are written using a column letter and row number (e.g., A1, B12, Z17).
The values in the referenced cell will be "filled in" to compute
the value of the formula (and modified whenever the value of the
referenced cell is).
- relative and fixed references: cell formulas are often duplicated by
cutting and pasting or by "filling" (see below). When this happens, an
unadorned reference in a formula will be replicated so that it reflects
changes in the new cell position. For example, A1 might become B1 in
a copy of the formula moved to the right, relative to the original.
If you wish to preserve a reference to a fixed, absolute cell position,
use a fixed reference of the form $A$1 or $C$12: it will not change
when the formula is copied or filled.
- filling: in many cases, we want to caculate the same sort of formula
for many different related values in the spreadsheet, typically
arranged across a row or column. We can do this automatically by
putting in one formula, to serve as the "example" and then filling
this formula in to the other relevant cells (in most cases, the
formula will be automatically changed so that successive formula results
refer to successive input values). To fill in a row or column area with
such formulae, select the "example" cell (which must be written in
first), then drag your mouse to include the cells to be filled in;
finally choose "Fill left" (or "Fill down", "Fill right", etc.) from
the fill options in the edit menu.
- functions and arguments: addition, subtraction and other common
algebraic operations can be written into formulae using the usual infix
notations familiar from arithmetic (+, *, etc.). But other functions,
including less common algebraic ones as well as those specifically
related to spreadsheets, can also be used. Most of these are written
using a different syntax which puts the function name (often a word)
first, followed by a number of arguments in parentheses and separated
by commas. For example "MOD(7,2)" will evaluate to 1, since the MOD
function returns the remainder of its first argument when divided by
the second argument. These functions can be used in conjunction with
other functions and operations, e.g.:
= 3 * (MOD(28/4),3) + 1
which evaluates to 4. Other useful functions include the TRUNC
function, which will round down (or truncate) a number to the next
smallest integer, so that there are no digits after the decimal point.
A binary converter
For the lab you should write a binary converter in a spreadsheet, as
demonstrated in class (we will generalize to other bases below). There
should be room for an input, for a reasonably large row of binary digits
and a cell for a "check", i.e., a calculation which checks the accuracy of
your answer by re-computing the total value and displaying it in decimal.
So, you might want to use the following strategy:
Here's a picture showing what your final lab might look like (you don't need to follow
this format exactly, in terms of fonts and colors for example, but the main features
should all be there):
- the input: just a cell in which the user can type the input value;
you may want to label it with a fixed-text cell nearby ("Input:").
- powers: you can make a list of the powers of 2 (from right to left is
best) by putting a 1 in some cell to the right, and then
specifying that each cell to the left should be multiplied by 2.
- "remnants": to compute the binary place values, you can use cells
which start from the input, and then successively divide by 2, each
time making sure to truncate to an integer number (use the TRUNC
function, with a 0 second argument).
- digits: the row of binary digits can be computed by letting each digit
be the remainder of dividing the corresponding "remnant" by 2; this
can be written using the MOD function, with a second argument of 2.
- values: in order to check the result, you can mulitply each binary
digit by the corresponding value in the row of powers of 2, then sum
these values using "+" or the "SUM" function.
Abstracting out the base
Once you have gotten your program to work for a base of 2, you should go through
and re-write it so that the program works with any base specified by the user
(in some fixed, labeled cell).
Results to demo
A spreadsheet which can convert any number into a series of digits in any base,
and which checks its own work by calculating the summed value of those digits in
the given base and re-displaying the result in decimal.