BCALC

by: Barry Kolbe and Bryan Schappel

Publication date: August 1988

A spreadsheet so easy to use that you don't need this article! Well, almost. But seriously, BCALC has many features that make it easy to use, the kind of ease of use you enjoyed with Atariwriter.

Simply binary load BCALC from DOS. Refer to your DOS manual on how to do this if you are not sure. Please remove the cartridge from your 800 computer or turn BASIC off on your XLIXE computer by holding the option key down when boot-ing up. When the program loads there will be a credit line at the top of the screen. Press any key to begin.

General information
BCALC is a spreadsheet using about 8K of memory. This leaves around 24K for data. BCALC has many of the features you would find in a commercial spreadsheet. The BCALC sheet is 64 columns wide (lettered AA-CL) and has 64 rows (numbered 01-64). The inter-section of each column and row is called a cells. So if my arithmetic is still correct that means there are 64 x 64 or 4096 cell. Each cell has an overhead of at least four bytes plus whatever data is entered.

There are three kinds of information you can store in a cell. The type of data entered is displayed on the right side of the top screen line.

  1. Text Data: This is anything that does not begin with a number 0-9 or the or "-" sign. It's possible to put numbers in as text (see below).
  2. Numeric data: a number that begins with 0-9, "-" or "." Numbers can be en-tered in scientific notation. For example, 3.45E-45. BCALC only accepts that part of in-put which is a valid number. For exam-ple, if you type: "-.234fgh" BCALC takes the "-.234" and ignores the rest. You may not type in commas. To display commas use the formatting commands which are discussed later.
  3. Functions: An arithmetic or algebraic expression starting with the "=" sign (in-versed also works). You mean I have to know algebra? Was my math teacher right in that there is a use for algebra? Perish the thought!

Entering functions is quite easy if one follows the rules. The five operations of ^ (exponentiation), * (multiplication), / (division), + (addition) and - (subtraction) are used. These are used as operators between numbers, cells or built-in functions. Be aware of the order of operations. My Dear Aunt Sally. Huh? This is a mnemonic to remember the order of operations: multiplications and divisions are done first in order from left to right. Next, additions and subtractions in order from left to right. Exponents and parentheses are done first however. You want an example? Sure thing.

     =3+((2+7)^2-4*6)/6

The innermost parentheses are done first: (2+7) becomes 9. The 9 is then squared (^2) yielding 81. Next 4*6=24 is done. Then the difference 81-24 = 57 is calculated. Dividing by 6 yields 9.5. Add to 3 equals 12.5. Remember that each number above could be replaced by a cell or a built-in function. For example:

     =@RND((1 +AB24/12) ^ 36) - BQ12

Always make sure that the cells used do not have text data or an error will result.

Since you might enter text data in lower case, BCALC also allows you to enter functions in lower case; so you won't have to toggle that CAPS key. Just type away!

Console keys
OPTION displays a status screen in the text window. The current filename, number of free bytes, recalculation status (on/off) and recalculation mode (row/column) are shown. Hit a key to exit.

SELECT puts you in the driver's seat. Well, actually in command mode. The text window will change color in this mode. You may use upper- or lowercase letters and even inverse video in commands. Some commands require an argument such as a cell reference (e.g., BG24) or a number. Zeros are necessary in cell references (e.g., CD02).

The commands are:

[DIR]ECTORY n displays up to 16 files on the screen. If you do not type a drive number n," Drive 1 is the default. Make sure there is a space between "DIR" and "n' That is also true for the other com-mands. Press a key to read more file-names. If there are exactly 16 files read in you will need to hit a key twice.

[WID]th ccrr sets the width of column cc to width rr. The width rr must be greater than one but less than 32.

[SAV]E Dn:filespec is used to save the sheet. You must type a complete filespec including the "Dn:". Multiple drives are supported as well as a RAMDisk. If you attempt to save a sheet using the name of a file that already exists, you are asked if you wish to replace it. Merely hit the "Y" or "N" key to make your choice.

[LOA]D Dn:filespec is used to load a sheet that was saved. Seems reasonable.

[GOT]O ccrr is the fast way to go from here to there or at least to cell ccrr. cc must be in the range AA-CB and rr in the range 01-47.

[SET] ccrr sets the column and row references that are displayed at the bottom of the text window. An example is definitely called for. If we type SET AA02 and the cursor is on DE12 we see that data from cell AA12 and from DE02 displayed.

If I were doing my grades (after all, I am a teacher), I might have student names in column AA. But when I'm entering data in cell AG12, column AA is nowhere to be seen. However, if I use SET AA12 the student name from column AA row 12 appears on the last text window line. Also shown is heading for column AG row 02.

[LIS]T Dn:filespec lists the spreadsheet to the disk so the information can be used with a word processor like Atariwriter. Use the cursor keys to define the block of information you would like listed on disk. If you define a bad block an error results. The information listed will be in exactly the same format as you see it on the screen.

LISTed files cannot be reloaded into the sheet. Doing so will probably cause the computer to lockup, and you will likely see a very unusual screen staring at you! Use the SAVE command to store the sheet on disk. Just in case you try to do some-thing clever, like LIST to the same filename that was used to SAVE the sheet, you're told if the file already exists. You then may overwrite it if you wish. I'd recommend using a LST extension on LISTED files.

START is used to abort input in either input mode, edit mode or command mode.

Special keys
These keys are used with the CONTROL key pressed simultaneously:

[-] (Arrow up) moves the cursor up one row.

[=] (Arrow down) moves cursor down one line.

[+] (Arrow left) moves one cell to the left.

[*] (Arrow right) moves one cell to the right.

The name of the current cell (e.g., AA01) occupied by the cursor is displayed in the center of the top screen line. To the right of it is a message showing the type of data in the cell. This is good to know since a number on the screen could be a stored number or it could be the result of a function.

These keys can be used in input mode to terminate input and send the cursor to the next cell. For example, if you are on cell AA01 and you type NAMES(CTRL - *), "NAMES" is entered in cell AA01 and the cursor will move to cell AB01. RETURN keep the cursor on the current cell. In edit mode these keys move you around the text window to give you full-screen editing.

[H] takes the cursor to AA01.

[D] deletes a block of cells. Follow the prompts. Specifying an improper block results in an error.

[C] copies a block of cells. See below.

[M] moves a block of cells. See below.

[T] forces text mode. This allows you to enter numbers, etc., as text. For example, 342-48-2333 as a number would result in just 342 being entered. In forced text mode it is entered as text.

[G] changes the global format of the column the cursor is on.

[L,C,R] keys control Left, Center and Right justification.

[D] toggles the "$" symbol.

[,] toggles commas on and off

[0-9] chooses the number of places displayed to the right of the decimal point.

[ESCAPE] makes the choice final and exits.

All new data entered in this column will take on the new format. Formatting information is stored with cell data when it is entered. So data already there has its own individual format. To change it use CTRL-E.

[F] formats an individual cell. Enter the data normally. Press CTRL-E The global format information is displayed in the text window. Make the changes you desire as above in G. ESCAPE reformats the cell and makes the changes on the screen.

[E] displays the current cell data in the text window and allows you to edit the cell much as you are used to doing in Atari BASIC.

[P] prints the sheet. Follow the prompts in defining a block to be printed. The information will be printed exactly as you see it on the screen. So check your column widths, etc. The printing is done by rows. If you have an 80-column printer and select columns whose widths add to more than 80 there will be some wraparound. Therefore you might want to print left and right halves of your sheet.

It's possible to send printer codes out by typing them into a cell. Since some of these codes might conflict with the way BCALC handles input you might need to type a space first and then the codes.

[O] toggles the recalculation on/off flag. Turn it off to type in data. Otherwise the whole sheet is recalculated every time you enter data, which takes some time to do.

[R] changes recalculation order from by rows to by columns or vice-versa.

[K] erases a cell from the sheet.

[CAPS] toggles between upper and lowercase letters. This key can be used in any input mode.

[A] recalculates the whole sheet im-mediately. Use CTRL-E and RETURN to recalculate a particular cell.

[ESC] aborts the delete, copy, move and print options.

Let's discuss copy and move. Copy makes an exact duplicate of the block of cells you chose. Move does the same thing but cell references are changed. For example:

     cell     contents
     AA01     12.35
     AA02     4
     AA03     =AA01+3

Cell AA03 has 15.35 as a result. Copying cells AA02 and AA03 to AM23 results in:
cell contents AM22 37 (this cell existed before.) AM23 4 AM24 =AA01+3
The result in AM24 is 15.35 (the same as before). If you move the same cells to AM23 the result is:
cell contents AM22 32 AM23 4 AM24 =AM22+3

Here AM24 will contain 35. Notice how the cell reference was changed in cell AM24.

After a copy or move some cells will he overwritten. None of the source cells are deleted in either except those that are overwritten. Clean up any cells not wanted using the delete option (CTRL-D)).

We'd suggest that before doing a copy or move you save the sheet. It's just prudent practice. We have tried to crash this program in every way we can think of, but there is always the unknown. Maybe a circuit gets a little overheated and causes a crash. I even got SynCalc to crash once. Fortunately, I was just fooling around with it. The old adage "Better SAVE than sorry" applies here.

Built-in functions
The built-in functions are typed in this format: @CNT(AA03, BX24) or @SQR(23.45) or @SQR(BD23). Paren-theses must be used and cells must be separated by a comma.

The following functions require one argument which could be a cell or a number:

Fn Operation
SQU Squaring
SQR Square root.
ABS Absolute value.
RND Round value.
EXP Exponentiation base 10.
LOG Logarithm base 10
INT Greatest integer

These functions require two arguments and they must be cells.

Fn Operation
CNT Count the number of cells in a block.
SUM Add the entries in a block.
AVE Find the average of all the entries in a block

CNT counts all the cells that have entries in a block whether they be text, numbers or functions. SUM however, adds up only the values in cells containing a numeric result. Text cells are treated as a zero. Empty cells result in the word "Error" displayed on the screen. AVE operates like SUM. However, AVE uses the CNT and SUM routines to get its value. So if you try to average cells containing RENT,400,500,600, the result will be (0(RENT) + 400 + 500 + 600)/4 = 375 an in-correct result.

Functions can be mixed in with any arithmetic expression as in:

     =AA01+(4*@AVE(CD24,CE32)^@RND(AG12))-(2+@SQR(AA02))

However, functions are not nestable, i.e., you can not type =@ABS(@EXP(AA01)).

Error messages
We used standard error numbers wherever we could to keep the program as small as possible. The most common errors are:

# Error
2 Out of memory. You have used all available memory. Save the sheet as it is.
138 Device does not respond or malfunctions. This probably will refer to the printer not being on.
139 Same as above.
141 Cursor out of range. Used in copy, move delete, print and list if the defined block is incorrect.
144 Disk error, probably write protected.
160 Drive number not known.
162 Disk full.
165 Bad filename You must specify the "Dn:" in the filespec.
167 File locked.
170 File not found.

Wrap-up
There's an easy way to duplicate a formula in a column or row: Use the move command. For example, cell AC02 might have the formula = SQU(AB02). If I would like that formula in cells AC03 to AC23, I would first move cell AC02 to AC03 Then move the block AC02, AC03 to AC04. Next move AC02, AC04 to AC05, etc., until done. Then column AC has the squares of the numbers in column AB.

In this manner special formulas - statistical, financial or others - can be built up. For example, one formula for standard deviation is:

     Square root of (mean of X^2-(mean of X)^2)

Assume that the column AB02, AB23 has some data in it:

     AA		AB		AC
     01		data
     02 . square of 0002
     03 . square of 0003
     . . .
     . . .
     23 . square of 0023
     24 mean of X Mean of X^2
     25 square of AB24 AC24-AB25
     26 square root of AC25
     27

First find the mean of AB02, AB23 by us-ing =@AVE(AB02,AB23) and enter this in cell AB24. In cell AB25 enter =@SQU(AB24). In column AC02,AC23 enter the formula =@SQU(ABrr) where rr goes from 02 to 23. Or use the move procedure described above. In cell AC24 enter = @AVE(AC02,AC23). In cell AC25 type = AC24 - AB25. Finally in cell AB26 enter = @SQR(AC25). Cell AB26 will contain the standard deviation.

Let's see what that new car will cost when financed at different rates and different principals.

Let r = rate as a decimal, p = principal, t = years of loan, and A = cost of loan. The formula for the cost of a loan (prin-cipal plus interest) is:

     A = (p*r*t*12)/(1-(1+r/12)^-12*t)

This formula can be entered into one cell; p, r and t can be numbers or cell references. If you would like to calculate a monthly payment just use p*r as the numerator. If cells AA01, AB01, AC01 contained p, r, and t respectively, then the above formula would look like this:

     =(AA01*AB01*12*AC01)/(1-(1+AB01/12)^-12*AC01)

We tried to put in features that would make it very easy for you to use this spreadsheet. The built-in functions provide you with the building blocks for almost any formula. This program took over five months to develop and test. We hope you will find it useful.


Barry Kolbe and his wife Linda live in the country with their two children Philip and Audra. Beware, Phil just got his driver's license and Audra can be found at the local shopping mall. The household uses timesharing since the 800 and Nintendo are hooked up to the same TV.

Bryan Schappel discovered computers, bought one, went to college, drank beer, got engaged, left school, and is taking a leave of absence from reality.

© 2008 Bryan P. Schappel • Valid XHTML Transitional • Valid CSS