THROUGH THE LOOKING GLASS: Create a Road Warrior's Trip Computer Using 1-2-3

Even if you don't do a lot of driving, this spreadsheet will demonstrate many useful features of Lotus, including the @Datevalue() and @IF() functions, time formatting commands, the use of the logical "OR" operator, and the creation of Lotus macros to make it all run smoothly.

By Ed Keefe

I used to do a lot of driving, and programmed my HP-29C calculator to act as a trip computer, to tell me when I would arrive at a destination. I later converted the road-trip program to run on an HP 41CX calculator, and most recently to run on the HP Palmtop computers.

I decided to turn the program into a 1-2-3 spreadsheet for the Palmtops. This approach lets me have a record of times and distances at the end of a trip. I developed the worksheet so that it calculates two ETA's (estimated times of arrival), one based on average speed and one based on "Speed Over the Interval" to give a more accurate estimate with widely varying speeds.

First we'll look at how to construct the worksheet, then how to use it.

Constructing the Worksheet

Here's how to build the "trip computer" from scratch in Lotus 1-2- 3. [The complete spreadsheet is available in TRIP.ZIP [ON DISK].]

Start Lotus by pressing (123) on your Palmtop. Make sure the worksheet is empty, press (MENU) Worksheet Erase Yes, and set the spreadsheet to recalculate automatically (press (MENU) Worksheet Global Recalc Automatic).

Key in labels

Spreadsheet "labels" are treated as text, "formulas" are used to calculate. Key in the following labels and formulas in the designated cells, or perform the commands as indicated. (In describing how to build a spreadsheet the name of the cell is given followed by a colon, A1:, H2:, etc. followed by the contents that is to be typed into that cell, Odometer, +D2>0, etc.. Comments are enclosed in parenthesis ( ) and are not to be entered.)

A1: Odometer A2: in Miles

(Readers who use the metric system may substitute KM for Miles. The arithmetic will still work, only the units are changed.)

B1: CurTime

B2: HH:MM am

C1: Miles

C2: To Go

D1: Speed

D2: MPH

E1: HH:MM

E2: To Go

F1: E.T.A.

F2: CurSpd

G1: E.T.A.

G2: On AVG

H1: Speed

H2: +D2>0

(This formula becomes the criterion range for the @DAVG() function that will be entered later.)

Format spreadsheet

Right justify all the labels by keying in the following commands, (MENU) Range Label Right A1..H2 (ENTER).

A little formatting will make it easier to read the spreadsheet. Create a dashed line across the display to separate the column headers from the body of the spreadsheet. Move the cursor to cell A3 and type \- (backslash minus) (ENTER). Then key in (MENU) Copy A3..A3 to A3..G3 (ENTER).

Enter a row of periods in the unused first-row cells by moving the cursor to D4 and keying in \. (backslash dot) (ENTER). Then press (MENU) Copy D4..D4 to D4..F4 (ENTER).

Now set the titles so they always display at the top of the screen as you scroll down the spreadsheet. Move the cursor to cell A4 and press, (MENU) Worksheet Title Horizontal.

Adjust the column widths so the data fits in the cells and so the spreadsheet displays properly. Move the cursor to each column listed below and use the commands, (MENU) Worksheet Column Set, type the indicated column width number and press (ENTER). Repeat this sequence to set the width for each column in the list:

Column C [width 6]

Column D [width 6]

Column E [width 7]

Column F [width 9]

Column G [width 10]

Column I [width 1]

Column J [width 1] (HP 95LX only)

Column J [width 9] (HP 100LX only)

Column K [width 20]

Column L [width 9]

The spreadsheet should look like this:

Trip Spreadsheet:: Graphic

 Entering formulas

Take your time keying in these formulas. It's easy to make a mistake! First, enter the formula to calculate average speed for all the entries from cells D1 through D256 in cell G4:

@DAVG(D1..D256,0,H1..H2)

If you need to be able to add more than 256 speeds, increase 256 to 512 or whatever you need.

Next, enter the following formulas in the indicated cells. Move the cursor to the appropriate cell and carefully key in the formula. Be extra careful entering the "absolute cell references" (cell references with dollar signs in front of them like $C$4 and $A$4 in the first formula, $G$4 in the last two formulas).

C5: @IF(A5=""#OR#B5="","",$C$4+

$A$4-A5)

D5: @IF(C5="","",(A5-A4)/24/

(@TIMEVALUE(B5)-@TIMEVALUE(B4)))

E5: @IF(D5="","",(C5/D5)/24)

F5: @IF(E5="","",@TIMEVALUE(B5)+E5)

G5: @IF(C5="","",@TIMEVALUE(B5)+

C5/$G$4/24)

H5: @IF(D5="","",$G$4)

[Note: You can enter an absolute cell reference into a formula in a different, more certain manner. Using H5 above as an example, move to cell H5 and enter the formula up to the absolute cell reference (@IF(D5="","",). Then use the arrow keys to move to the cell referenced (i.e. G4 in the example). Press (F4) and the absolute reference will appear in the formula. (Don't be confused by the fact that it looks like the formula has been entered into G4 -- it hasn't.)

Key in the next character in the formula (a close parenthesis in this case) and the cursor will jump back to the cell into which you were entering the formula. Key in the rest of the formula (if any) and press (ENTER).]

Each of the formulas above use the @IF() function to test if a cell is blank. If it is, then the cell that contains the formula will also be blank. This avoids a lot of "ERR" messages that make the display confusing and difficult to read.

The only constant value entered into these formulas is 24. It is used to convert a value returned by the @TIMEVALUE() function to the number of hours that value represents. For example, part of the formula in cell D5 is @TIMEVALUE(B5). That part of the formula takes a time label entered into cell D5 (e.g. 5:42 am) and converts it to a number representing the number of seconds since midnight divided by the total number of seconds in a day (in this example, 20520/86400=0.2375). If you multiply 0.2375 by 24, you get the number of hours since midnight (5.7 hrs). We use this number to compute miles per hour (or kilometers per hour).

Further Formatting for Readability

Set the number of places displayed after a decimal point with the Fix command and the way time is displayed with the Date 7 and Date 9 commands. Perform the commands for the following ranges.

Fix 1 format -- sets the range to display one place beyond the decimal point. Example: Press (MENU) Range Format Fixed 1 (ENTER) A4..A256 (ENTER).

Date 7 format -- sets the range to display time in the HH:MM am/pm format. Example: Press (MENU) Range Format Date Time 2 (ENTER) B4..B256 (ENTER).

Date 9 format -- sets the range to display time in the Short International format (HH:MM in the 24-hour clock). Example: Press (MENU) Range Format Date Time 4 (ENTER) E4..E256 (ENTER).

Format the ranges below as indicated: (A4, B4, and E5 have been formatted in the examples above)

A4..A256 : Fix 1

B4..B256 : Date 7

C4..C256 : Fix 1

D5..D256 : Fix 1

E5..E256 : Date 9

F5..G256 : Date 7 (Two columns formatted at once)

G4..G4 : Fix 1

H5..H256 : Fix 1

L3..L10 : Fix 1

Testing the Worksheet

Once you have all the formulas entered and the range formats set, test the formulas by entering the following values in the cells indicated (don't enter comments in parentheses):

A4: 0 (assumes you have a trip odometer that you can set to zero)

B4: "5:42 am (an early start!)

C4: 400 (miles to go)

A5: 20

B5: "6:04 am (To automatically enter the time during an actual trip you can use the (Fn)-(TIME) keys on the 100LX, or create a System macro on the HP 95LX, see sidebar.)

After making these entries you should see the following screen:

Worksheet with Test Values:  Graphic

 [Miles To Go = 380.0; Speed = 54.5; HH:MM To Go = 06:58; E.T.A. = 01:02 PM (Current and Average Speed); Average speed (cell G4) = 54.5.]

If the answers are not correct, check the formulas to make sure they have been entered correctly. If the answers are correct, you can complete the spreadsheet by replicating the formulas to the rest of the cells in column C by pressing (MENU) Copy C5..H5 to C5..C256 (ENTER).

Protect your worksheet from accidental keystrokes by performing the following three sets of operations:

Press (MENU) Worksheet Global Protection Enable to protect the entire worksheet.

Press (MENU) Range Unprotect A4..C4 (ENTER) and press (MENU) Range Unprotect A5..B256 (ENTER) to unprotect ranges into which you will have to enter data.

Save the worksheet with a suitable file name such as TRIP.WK1 by pressing (MENU) File Save TRIP (ENTER).

Using the Worksheet

Whenever you want to use the TRIP worksheet, activate Lotus (123) and press (MENU) File Retrieve TRIP (ENTER).

If columns A and B have any data in them, press (MENU) Range Erase A4..B256 (ENTER) to erase it. The rest of the worksheet should go blank.

Start a new trip by entering the initial settings in the cells indicated:

Enter this in this cell

Current odometer reading A4

Use 0 if your car has a trip odometer that can be set to zero, or enter the value on the odometer (e.g. 123456).

Starting time B4

There are several different ways to do this (assume the time we're entering is 6:00 am):

On the 100LX, put the cursor in cell B4 and either press the Time key, (Fn)-(TIME) (ENTER), or key in "6:00 am and press (ENTER).

On the 95LX, put the cursor in cell B4, key in "6:00 am and press (ENTER). You can also create the system macro described in the Side Bar to automate the entering of the time on the 95LX.

Miles to go C4

As the trip progresses...

You continue to enter odometer readings and times at intervals during the rest of the trip. Enter readings whenever you stop for fuel, lunch, or whenever you remember. You could set your Palmtop to beep at you every 15 minutes to remind you to enter readings.

Enter the second odometer reading and time in cells A5,B5; the third in A6, B6, and so on up to A256,B256.

The rest of the spreadsheet will compute itself automatically. You can enter or change data in the A and B columns if needed. The other parts of the worksheet are protected.

Two Different ETA's

This spreadsheet calculates two ETAs. The first assumes that you'll maintain a constant speed for the rest of the trip. The spreadsheet uses the speed of the current interval for that constant.

Since this may not be the case, the second ETA is figured on the Average Speed (average of all the intervals). These ETAs may be different depending on whether your speed is uniform or varying. For example, if your trip is almost entirely on interstate highways, both ETAs should be close together. On the other hand, if your trip takes you through towns and over country roads, where your speed will vary, the ETAs may differ greatly during the trip. However, they should come together toward the end of the trip.

You can enter data in the current spreadsheet up to row 256. If you need more rows than this, just copy the formulas in cells C256..G256 down the spreadsheet for as many additional rows as you think you'll need.

Easier Viewing Macro

Trying to read many columns of numbers lined up close together gets to be a hassle. The ALT-V macro, described in the Side Bar, displays the data in one row in an easier-to-read list format.

TRIP List Formatted Display: Graphic

 Put the cursor on the row of information that you want to view (anywhere below row 4) and press (ALT)-V. If the cursor is in column A, you'll hear a beep. That's normal. Press any key to quit vertical view and return to the spreadsheet view.

Known Limitation

The worksheet gives erroneous results for one particular case, namely when the current time (the label in column B) overlaps midnight (12:00 AM).

For example, suppose your worksheet shows the following:

Error Shown: Graphic

 The workaround for this is to insert a dummy time entry in the worksheet so that it looks like this:

Workaround for the Error:  Graphic

 This seems to keep the spreadsheet functioning properly. It also saves having to complicate the formulas with tests for the current date.

If you're using this application on the road, drive safely. Keep your eyes on the road. Better yet, let someone else do the driving while you play with the spreadsheet.

I'm not a sports car driver. So I'll leave it up to those enthusiasts to tell us whether a spreadsheet such as this one would be useful for sports car rallies.

Disclaimers and Cautions

This 1-2-3 application is offered "as is" with no claims as to its usefulness or accuracy.

Until next time, happy porting.

Lotus and System Macros for TRIP