CS 130 Lab #3: Binary conversion, via "programming"

Goals

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?

Concepts

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.

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.

You may also wish to use various formatting options (font, color, number, border) to make your spreadsheet easier to read and understand.

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:

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.

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):

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.