| The following formulas... | ...give the following Values |
 |
 |
basics : cell references and formulas : referring to cells in other rows : independent variables : cell formatting : cell styling : cell display : scripts and HTML : conclusion (objectsheet home)
When you set up a spreadsheet, you generally start with a set of known data. Often it's a sequence of numbers (for instance, months 1 through 48 for a loan amortization table) or an arbitrary range of numbers (like x over (-10, -9.5, -9,...+10) for calculating a formula such as y = x^2-1). You then add formulas to calculate the resulting values (like the rest of the amortization table or the y values in y=x^2-1).
One of the frequent tasks in creating a spreadsheet is setting up such initial sequences. In a spreadsheet, you typically use repeated formulas to do this. You place a starting value in the topmost cell, and then replicate a formula to fill in a column like so:
| The following formulas... | ...give the following Values |
|
|
This is the convenient way to do it the first time, but any changes become more tedious. For instance, to count by 2's instead of 1's, you edit the first formula, then re-copy it down the column. To add rows to the table, you have to replicate that formula as well as all other formulas that reference these. Setting up a range between two particular endpoints is more difficult; you would either iterate a set of formulas like the one above until you got what you wanted, or you might revert to sophisticated formulas to manage it automatically.
For starters, we look at an approach similar to the spreadsheet example above, where the formula uses the value in the row above to generate the next vaue.
Create a new objectsheet:
Note: for this and the rest of our tutorial, our new objectsheets come with the formula row already diplayed.
The objectsheet dispenses with the cell-to-cell referencing scheme of the traditional spreadsheet. In the objectsheet, we reference cells by first choosing a row (or "object"), then selecting a column (or "property of the object"). A more thorough treatment of other-row referencing can be found in the previous section.
Here, we use the built-in object prev. prev refers to "the row (or object) directly above" (note that prev does not refer to "the cell above", but "the row above"). Once we have a row reference, we pick off the value in the a column (or property) with ".a". the formula is thus simply "prev.a+1". Enter this as the "a" formula in your new objectsheet:
Of course there's no row above the top row, so this formula doesn't work by itself. Javascript evaluates "a" in row 0 to NaN, or "Not a Number" and propagates this result down the column. Just as the spreadsheet version needed a starting value (cell B2 in the example above), so do we here. We override the template formula for the top row, by entering an explicit value (of 1, for this example) into the cell. To do this, we simply type the number "1" in the appropriate cell. The result looks like this:
In that uppermost cell, the sheet responded by showing us the calculated value (trivially "1", in this case), followed by the characters ":=", and then what we originally typed ("1"). The background color also changes (to white) to indicate the cell is no longer dictated by a formula; it is a "manual entry" cell.
A traditional spreadsheet has a great many rows and columns; we only fill the ones we need. While that structure allows free-form creation of many tables and variables on the grid of a single spreadsheet, it has drawbacks. A spreadsheet can get screwed up simply by inserting a row or column that inadvertently splits up a table elsewhere. Formatting is also difficult to maintain when we move chunks of a spreadsheet around.
The objectsheet takes a different approach. Here, each sheet is a single table with a finite size. For additional tables, we create additional sheets. The referencing scheme allows cells across sheets to work as a whole. One benefit is that objectsheets in a file are computationally integrated but graphically independent. Formatting and organizing sheets will affect neither computations nor the formatting of other tables. Another important benefit is illustrated here; a table that knows how big it is makes a number of tasks much simpler that their spreadsheet counterparts.
Since objectsheets know their size, Template formulas can automatically fill all rows. To get more rows in the table, just add more rows to the sheet. You can do this by just clicking on the "expandor", the plus sign icon at the center-top of the page, and draging down. As you drag, the number of rows to be added shows in the gray status bar at the bottom of the sheet. When you're done dragging, the rows are automatically filled with the appropriate formula(s).
To count by twos instead of ones, just change the formula in one place;
No formula replication is neccesary, and you can see the calculated values along with the formulas that generated them, making it clear what's going on.
So that's one way to get starting values in a column. Another
approach is to use the 'index'
variable. This variable simply reflects the row number. In this
case, you don't need to override the top row because 'index' is defined for
all rows. The formula and result would look like this:
Interestingly, "modern" spreadsheet programs have a similar row index variable that you can use. But row associations depend on the absolute location of a table within the sheet. This location changes if you move the table or insert other content above it; this approach is therefore not stable in a spreadsheet. But row indexes in the objectsheet though are stable since there's one table per sheet, and you move the table as a single, coherent graphical object; absolute row numberings stay intact.
Yet another approach is available. This approach
is powerful when you want to range between two particular numbers.
The range()
function takes as arguments the start and end values for the
column and spans exactly that range from top to bottom.
Open up a new sheet:
and type "range(1,3)"
into the formula for a:
This function automatically fills the column
between the specified endpoints (1..3). With the range() function, as
you add more rows to the sheet, the endpoints are maintained.
Below, we simply added 3 more rows to the sheet. We've not
changed the formula at all.
In this case adding or subtracting rows increases or decreases the "sampling frequency" in the table. As you add formulas to the right and add to the complexity of the sheet, the formula for variable a remains the only thing you need to adjust if you want to change the "operating range" of your sheet.
The "bounded size" aspect of the objectsheet makes generating sequences of numbers (and other tasks) inherently simpler than the traditional spreadsheet.
Javascripters: As powerful as it
is, the range() method is a short, single-lined function (you can see
it by entering "=range" into an object cell). Other helper functions can
easily be added to the objectsheet core library.
next: cell formatting
rk, 27apr01