reptile7's JavaScript blog
Sunday, February 09, 2020
Approaching Excel, Part 5
Blog Entry #406

Today's post will conclude our College Tuition Chooser discussion by tying up some loose ends and presenting a demo.

Last words on browser support and validation

As noted in the third and fourth parts of this series:
Netscape's JavaScript support began with Navigator 2.0. The chooser can be run by Netscape 2.x upon making some simple changes to the computeForm( ) function and by Netscape 3+ as is, the chooser's if (agentInfo == "Netscape 4.0")-gated JavaScript notwithstanding.

Internet Explorer's JavaScript (more precisely, JScript) support began with IE 3.0. I reported earlier that IE 4.5 handles the chooser's for-Netscape 4.0 code without any problems but I didn't say anything about IE 3 as I didn't have it on my computer at the time. I've recently downloaded IE 3.01 from MacFixer's Vintage Mac Software Library and installed it in the SheepShaver environment. As it happens, IE 3.01 - at least on the Mac side - won't run the chooser because it doesn't respond to change and click events at all - this is not altogether surprising given that onEvent element attributes were not part of HTML 3.2, which became a W3C Recommendation a few months before IE 3.01 for the Mac was released - conversely, Quirksmode's Early event handlers page intimates that IE 3 for Windows users should have been good to go. Anyway...

I previously recommended the use of a regular expression to sort out the chooser's character validation issues. Alternatively and far more simply, we can block non-numberizable cost inputs with the isNaN( ) function:

if (isNaN(input.value)) {
    window.alert("Your input can contain digits and a properly placed decimal point, or be blank; anything else doesn't cut it.");
    window.setTimeout(function ( ) { input.focus( ); ); }, 100); return; }

• Re the above alert( ) message, a blank input is OK - its isNaN( ) return is false - as isNaN( ) maps the empty string to 0.
• A $1,000 input is not OK as its $ and comma characters prevent its conversion to a number; however, you may prefer that the user keep those characters out of the input in the first place.
• Additional code would be needed to flag a number with a fractional part running past the hundredths place or a negative number.

Regardless of what we do or don't do validation-wise, it would be a good idea to preface the chooser with some metatext that specifies what the inputs can and can't be, and I'll do that for the demo.

Average it

In my former life as an instructor of organic chemistry*, I used spreadsheets to calculate exam averages as part of my regular record-keeping activities, and it occurred to me that adding an averaging capability to the chooser would be a useful thing to do.
*Some of my teaching materials from back in the day can still be downloaded as .pdf files here.

We can output averages for the various cost types to the cells of a new, fifth column on the right side of the chooser.

<tr><th>Name of Institution</th> ... <th>Average</th></tr>
<tr><th>Tuition</th> ... <td><input type="text" name="d1" size="15"></td></tr>
<tr><th>Fees</th> ... <td><input type="text" name="d2" size="15"></td></tr>
<tr><th>Total Cost of Attendance</th> ... <td><input type="text" name="d9" size="15"></td></tr>

The getAverage( ) function below will add up and average one row of costs à la a standard spreadsheet:

function getAverage(input) {
    var columnID = new Array("a", "b", "c");
    var rowID =;
    var nonblank = 0;
    var runningfieldtotal = 0;
    for (var i = 0; i < columnID.length; i++) {
        if (input.form.elements[columnID[i] + rowID].value) {
            runningfieldtotal += Number(input.form.elements[columnID[i] + rowID].value); } }
    var rowaverage = runningfieldtotal / nonblank;
    input.form.elements["d" + rowID].value = rowaverage.toFixed(2); }

function calc(input) {
    if (validateData(input)) {
        getAverage(input); } }

I didn't do any play-by-play for the modified computeForm( ) functions in the previous post, so let me do so in this case.

We set up a columnID Array of the a and b and c column identifiers.

We use the name property of the Text object and the charAt( ) method of the String object to identify (rowID) the row that holds the onchanged input.

We'll count the number of nonblank fields in the row and store that number in a nonblank variable. The division operation that computes the average should exclude blank fields: 10000 and 8000 and a blank should give a 9000 average and not a 6000 average.

We for-iterate across the columnID columns: if a columnID[i] + rowID field is nonblank, then the nonblank count is increased by one and the field's value is Number( )-ized and the resulting number is added to a runningfieldtotal.

When the loop has run its course, the runningfieldtotal is divided by the nonblank count to give the rowaverage.

The rowaverage is toFixed(2) to the hundredths place** whether it reaches that place or not, e.g., a 9000 average becomes 9000.00; the formatted average is displayed in the fifth column's d + rowID field.
**If you would rather the average be a whole number, then you can Math.round( ) the rowaverage instead.

Like the computeForm( )getTotal( ) function, the getAverage( ) function is called from the calc( ) function after the input.value has passed validation.

Vertical tabbing, take one

With some spreadsheet applications, e.g., the AppleWorks spreadsheet module I used way back when, pressing the Enter/Return key effects a vertical tabulation from the current cell to the cell directly below it. I like this feature, so I'm going to add it to the chooser, as follows:

<form name="costsForm" onsubmit="return false;"> ...chooser form element content... </form>
<script type="text/javascript">
function tabdown(e) {
    var columnID, rowID, thisKey;
    if (e) {
        columnID =;
        rowID =;
        thisKey = e.key ? e.key : e.which; }
    else if (window.event) {
        columnID =;
        rowID =;
        thisKey = window.event.keyCode; }
    else {
        window.alert("Sorry, your browser does not support the event object.");
        return; }
    if (thisKey == "Enter" || thisKey == 13) // If the user presses the Enter/Return key:
        document.forms["costsForm"].elements[columnID + (Number(rowID) + 1)].focus( ); }
for (var i = 0; i < document.forms["costsForm"].elements.length - 2; i++)
    document.forms["costsForm"].elements[i].onkeydown = tabdown;

The above code accommodates the Netscape event model and the Internet Explorer event model. Yes, I know that Microsoft support for the Netscape event model dates to IE 9 and that the window.event object now has a legacy status, but so what? Suppose that 0.01% of the world's online population are browsing with IE 5.5-8: that's 100 people per million users, and if I can bring them into the loop with a modicum of effort, then why shouldn't I do so?

A tabdown( ) function that listens for keydown events is iteratively registered on all of the chooser form's controls except the and buttons.

When a keydown event occurs in a chooser field, the tabdown( ) function uses the target or srcElement property of the keydown event object to access the field and uses name.charAt( ) operations to get the field's columnID and rowID coordinates. Subsequently, tabdown( ) uses the key, which, or keyCode property of the keydown event object to determine which key was depressed.

The srcElement, which, and keyCode properties are deprecated but are nonetheless supported by the current versions of all of the major browsers, including Firefox if they are yoked to an e-type event object.

If the depressed key was the Enter/Return key, then e.key returns Enter whereas e.which and window.event.keyCode return 13, and if that's what we've got, then focus( ) is sent to the columnID column's Number(rowID) + 1 field.

The i count includes the Name of Institution fields, which can get in on the vertical tabbing action if their title1, title2, and title3 names are changed to a0, b0, and c0, respectively.

The OpenOffice spreadsheet module's Enter/Return behavior is a bit more complicated.
One or more horizontal tabulations followed by an Enter/Return keypress gives a newline-like transfer of focus (A1 B1 C1 A2).
In the absence of prior horizontal tabbing, OpenOffice behaves like AppleWorks does (A1 A2).
Can we reproduce this behavior JavaScriptically? Maybe, I'd have to give it some thought...

There's more to the spreadsheet tabulation topic that we could discuss - you may know that pressing Shift + Tab effects a leftward tab and that pressing Shift + Enter/Return effects an upward tab - oh, and what about the ➡ and ⬇ and ⬅ and ⬆ arrow keys? - but let's move on.

A new interface?

As detailed previously, a standard spreadsheet requires the user to put some sort of formula in an output cell. Once that formula is in place, the spreadsheet's I/O action parallels that of the chooser, i.e., onchange-ing a formula input value returns automatically a new formula output value.

The chooser puts its addition formulas in the computeForm( ) function; the user doesn't see or handle these formulas at all, which initially rubbed me the wrong way - I felt that this lessened the user's 'conscious agency' in adding up the cost values, shall we say. I gave some thought to getting rid of all those onchange="calc(this);" attributes and instead using and buttons to call functions that would calculate respectively all of the column totals and all of the row averages at once - clicking a push button is the normal JavaScript way of setting something in motion, after all - but ultimately decided that it would actually be kind of annoying to have to click those buttons over and over again.

Perhaps we could make the chooser arithmetic a little less obscure by setting the defaultValues of the Total Cost of Attendance and Average fields to standard formulas - =SUM(A1:A8), =AVERAGE(A1:C1), etc. - or would this just clutter up the display? What do you think?


The demo of this section is a work in progress, but that's true of all creative efforts, isn't it? The demo incorporates the bulk of the new coding I've put forward in the last several entries - check the page source for all the details from start to finish.

Addition is carried out by the One column at a time computeForm( )getTotal( ) functionality. Averaging is carried out by the getAverage( ) function given above; I've appended a getAverage(input.form.elements[columnID + 9]); call to the getTotal( ) function body so that the Total Cost of Attendance row is averaged along with the input row in question. Validation is carried out by a new validateData( ) function containing the Segue to regular expressions statements. The Back to the beginning resetting facility has been deployed mostly as is - there are two minor edits thereto.

Per the limitations of Blogger, all styles are effected JavaScriptically. Moreover, the Total Cost of Attendance and Average fields have been made readonly, per my preference.

var costsForm = document.forms["costsForm"];
for (var i = 0; i < costsForm.elements.length - 2; i++) {
    costsForm.elements[i].style.textAlign = i < 3 ? "center" : "right";
    if (i % 4 == 2 || i > 34) costsForm.elements[i].readOnly = true; }

Lastly, the streamlined calc( ) coordinator is bound to the cost input fields JavaScriptically so as to tidy up the HTML a bit more.

for (var i = 3; i < costsForm.elements.length - 6; i++) {
    if (i % 4 == 2) continue;
    costsForm.elements[i].onchange = function ( ) { calc(this); } }

OK, here we go. The following spreadsheet can be used to sum and average costs automatically for three institutions of learning.
Your cost inputs may contain
digits running from the hundred-thousands place to the hundredths place,
a starting $,
a comma between a thousands place digit and a hundreds place digit, and
a decimal point between the ones place digit and a tenths place digit;
nonconforming inputs will be intercepted.

Institutional Cost of Attendance Worksheet
Name of Institution Average
Total Cost of Attendance

Powered by Blogger

Actually, reptile7's JavaScript blog is powered by Café La Llave. ;-)