The ECN No Name Newsletter is no longer being published. This is an archived issue.

Lotus 1-2-3 For Sun

Lotus 1-2-3 Manuals for Sun

          ECN #725     User's Guide     12.40
               Quick Reference
               Introducing  1-2-3
               Quick Start
               Configuration Guide
               Tutorial
               Task Summary

          ECN #726     User's Reference     26.70
               Using 1-2-3
               Commands
               Functions
               Macros
               Glossary
               Appendixes
               Index
               Release Notes

Introduction

Attention, one and all! ECN now has Lotus 1-2-3 for our Sun workstations!! "So what?" you may say, "I am an engineer. I don't use spreadsheets." WAIT JUST A MINUTE! If this is your attitude, or even if you are already a Lotus fan, I urge you to read this article to see what Lotus 1-2-3 has to offer on any of the Sun workstations on ECN. Lotus 1-2-3 for Sun is an exact implementation of Lotus 1-2-3 for PC's with enhancements for the Unix and network environment. If you are not familiar with spreadsheets already on ECN such as 1-2-3 or sc, here is a brief rundown. A spreadsheet is a computer environment which organizes data (numbers, labels, formulas, etc.) into rows and columns. It can be easily used at its lowest level to enter data in tabular form, but it has much greater power. The true utility of a spreadsheet is demonstrated when it is used to do calculations with data stored in cells. Depending on the particular spreadsheet, the calculations can range from addition of columns or rows of numbers to higher level math.

For more ideas on what spreadsheets can do, READ ON, check out the Lotus manuals (ECN #725 User Guide and ECN #726 Reference Guide) from your site specialist, check the man pages for sc on ECN or consult any of several books in Potter on spreadsheets, ie. Mathematical Applications Of Electronic Spreadsheets by D. Arganbright or An Engineer's Guide To Spreadsheets, Word Processors & Data Base Managers by J.M. Moore and W.E. Hammer. The remainder of this article is divided into sections concerning features, startup, roadmap and an example.

Features

Lotus 1-2-3 has many more features than a general spreadsheet. There are too many to mention, but the following features are those which should be most useful. Consult the manuals for more information.

• 3D--Lotus allows you to organize each file into columns, rows, and sheets for a higher level of organization.
• File linking--Lotus allows you to use cells of other Lotus files for calculations, thus enabling an even higher level of organization. This is best used by loading the files into memory during the same Lotus session.
• Presentation quality graphics--Lotus is set up to create line, bar, pie, area and other graph types with simple commands. These graphs can be printed on laser printers and should be able to be included in troff documents for use with the psfig preprocessor (E-mail howsmon for details). The graphs are fairly flexible but do not expect complete control over the appearance
• ASCII file loading--ascii files of data can be read into a Lotus spreadsheet for further processing.
• ASCII file writing--ascii files can be written to allow more sophisticated utilities/programs/tools to use the data created by Lotus.
• Command automation--Lotus has a sophisticated macro capability to help you automate almost any type of routine command sequences.
• Mathematical functions--Lotus has a set of mathematical functions which allow you to do higher levels of calculations easily. See ECN #726 User's Reference, Function Section for a list of the functions.
• Multiple sessions--Lotus allows you to run multiple sessions of Lotus at the same time so you can do several things simultaneously. However, these sessions are not linked in memory, so links between files in the separate sessions must be handled with care.
• Database functions--Lotus allows you to perform rather sophisticated database functions for sorting and selecting in a simple environment.

Startup

Before you begin to use Lotus 1-2-3, you must first include the line setenv PRINTER xxx in a .login or

Next you need to start Sunview or Suntools since that is the windowing system under which Lotus 1-2-3 runs.

Then, in a shell tool window, run the setup123 program by simply typing "setup123". This program asks a series of questions about your particular Sun environ- ment and creates a .l123set file in your home directory. This is an example of a .l123set file.

      1001  Lotus 1-2-3 Version "1.0"
      1002  Base Directory "/usr/123"
      1003  Printer Interface "lpr {file} #{site}"
      3000  Printer "Apple LaserWriter Times/Helvetica"
              "310 l13pps00.dvr ps00alw0.pdb"
      1004  Graphics Driver "Sunview Graphics Display"
              "sunview l_lrf.bun"
      1011  Display Char. Set "US ASCII" "l_ascii.bun"
      1005  Country Driver "USA English Numbers First"
              "l13cUSF3.cbd"
      1012  File Character Set "US ASCII" "l_ascii.bun"
      1006  Resource Language  "USA English" "USA-English"
      1007  Help Language  "USA English" "USA-English"
      1008  File Name Mode "UNIX file name mode" "0"
      1010  Keyboard "sun"

When using Lotus it is assumed that you know all the Lotus names for the function and numeric keys on the Sun keyboard; see a discussion of the Lotus utility keyedit in the ECN #725 Lotus 1-2-3 User's Guide, Configuration Guide section for other keyboards. A partial list of keys is available through on-line help.

Finally type "123" or "123 & in a shell tool window to start Lotus. There are some optional command line parameters which advanced users may use, but most of the items should be setup by the setup123 program. See the Getting Started pages in the Introducing Lotus 1-2-3 for Sun section of the User Guide for more information.

Roadmap

For the rest of this article, when it is relevant, reference to Lotus function keys will appear in bold. For more information please refer to "online help" or the "keyedit print function".

Now that you have Lotus up and running, you need to know some basics about entering labels, numbers and formulas and using commands.

Moving Around and Entering Data

To organize your data better you will want to be able to enter data in a variety of locations. The active cell is denoted by the highlighted cell (cell pointer) and the coordinates of the active cell are displayed in the upper left hand corner of the Lotus screen. You can move to any cell in the active file(s) you are working on by using the keys for DOWN, LEFT, PG UP, HOME, GOTO, etc. Once you are in the cell of choice, you enter the data or formula by typing the characters and pressing return. Note that Lotus assumes the following conventions: (i) labels (text) start with a character or the format characters ', ^ or ", (ii) numbers or values begin with a digit or + and (iii) formula names begin with the @ character. Remember that the main purpose of spreadsheets is to do calculations, so you can include coordinates of cells as variables in other cells; see the example for details.

Commands

Take time to look at Command Section of the ECN #726 Lotus 1-2-3 Reference Guide which shows the Lotus screen, lists the first level Lotus menu commands, and shows the relevant command trees for each first level command. Lotus menu commands are invoked either by first typing "/" and then the first letter of the command, or by moving the highlight to the command via the arrow keys and pressing "return". The exact steps depend on the specific command, so consult the following example, the tutorial or the specific sections of the manuals for details.

Example

The following is a fairly simple but instructive example of a Lotus 1-2-3 session. The Tutorial Section in the ECN #725 Lotus 1-2-3 for Sun User's Guide provides an exhaustive set of examples and an excellent array of Lotus screens to assist in identifying different screen areas. Before diving right in, I would like to again direct your attention to the Reference Guide mentioned in the previous section for lists of first level commands and the relevant command trees for each first level command. The remainder of this example is discussed in terms of the problem statement, a discussion of the tasks in the solution and the actual Lotus commands for the tasks in the solution.

Problem Statement

Let us suppose that you have some experimental data that has a sinusoidal tendency with an unknown amplitude and phase which was collected as a function of a known parameter. Your X-Y data is currently stored in the file datafile. For the purpose of this example, the data was generated by the following C program.

#include 
#include 
#define PI 3.141592654

main()
{
  double  x_value, y_value;
  double  amplitude, phase;
  double amp_noise_sgn, amp_noise_mag;
  double pha_noise_sgn, pha_noise_mag;
  double net_amp, net_pha;
  int     i;
  long random();

  amplitude = 100.0;
  phase = PI/2.0;
  for (i=0;i<500;i++) {
    amp_noise_sgn = ((random()&01) == 0) ? 1 : -1;
    pha_noise_sgn = ((random()&01) == 0) ? 1 : -1;
    amp_noise_mag = ((double) (random()&1023))/1023.0;
    pha_noise_mag = ((double) (random()&1023))/1023.0;
    net_amp = amplitude*
             (1.0 + amp_noise_sgn*amp_noise_mag/5.0);
    net_pha = phase*
             (1.0 + pha_noise_sgn*pha_noise_mag/2.0);
    x_value = ((double) i)/200.0;
    y_value = net_amp*sin(2.0*PI*x_value + net_pha);
    printf("%lf%lf0,x_value,y_value);
    }
}
The program was run by cc program.c -lm;a.out > datafile.

Suppose you would like to an idea of how sensitive guesses of amplitude and phase are for a nonlinear regression program with which you will fit the data. You would like to get an idea of goodness of fit both quantitatively and visually; thus you chose Lotus for the task.

Problem Solution--Discussion

Here is a step by step discussion of what you will do:

1. read the data in
2. add some labels for the data and items to be calculated or used
3. generate a theoretical function using relative and absolute referencing
4. calculate the sum of squares of the differences
5. calculate the offset residuals so they can be shown on the same plot as the data
6. plot the raw, theoretical and residual data
7. print the plot and a worksheet range to an encoded file and print this file on a laser printer

Problem Solution--Lotus Tasks

Now is the time for you and Lotus to solve the problem.

1. move the cursor to a3: DOWN DOWN (to allow room for labels)
2. read in the data: / F I N datafile ENTER
3. add labels: GOTO a1 ENTER ^xdata RIGHT ^ydata RIGHT ^residual RIGHT ^R**2 RIGHT RIGHT ^Amplitude RIGHT ^Phase DOWN DOWN ^ROffset LEFT ^RSumSqu ENTER
4. generate theoretical function:

          a.  enter the function:   GOTO c3 ENTER
              +$g$2*@sin(a3*2*@pi+$h$2) ENTER
              (Items g2 and h2 refer to the guessed amplitudes
              and phases and a3 refers to the xvalue.
              The $'s force absolute referencing to these cells
              during the copy command used later.)
   
          b.  load amplitude and phase guesses:  GOTO g2 ENTER
              80 RIGHT @pi ENTER
   
          c.  see how many data values there are: GOTO a3 ENTER
              END DOWN (note that data are in rows 3-502)
   
          d.  move back:  HOME GOTO c3 ENTER
   
          e.  copy the formula for column c:  / C ENTER (to accept
              the range Lotus selected) c4..c502 ENTER
              (Move around in this column.  Note that the formula
              has changed as you wanted.)
   

5. calculate the residual sum of squares:

         a.  calculate the residuals:  GOTO d3 ENTER
             +c3-b3 ENTER
   
         b.  copy the formula:  / C ENTER d4..d502 ENTER
   
         c.  square the residuals:  GOTO e3 ENTER
             +d3*d3 ENTER
   
         d.  copy the formula:  / C ENTER e4..e502 ENTER
   
         e.  calculate the sum of squares: GOTO g4 ENTER
             @sum(e3..e502) ENTER
   
         f.  reformat the cell for easier viewing: / R F S 2
             ENTER ENTER
   

6. calculate an offset residual column: GOTO f3 ENTER +d3-$h$4 ENTER / C ENTER f4..f502 ENTER
7. plot the data:

        a.  turn the graph on so that you can see the results
            of your efforts updated:  GOTO a3 ENTER / W W G
   
        b.  set the graph type to XY:  / G T X
   
        c.  set the x axis range:  X . END DOWN ENTER
   
        d.  set the raw y axis range:  A RIGHT . END DOWN ENTER
   
        e.  set the theoretical y axis range:
            B RIGHT RIGHT . END DOWN ENTER
   
        f.  set the residual y axis range:
            C RIGHT RIGHT RIGHT RIGHT RIGHT . END
            DOWN ENTER ESC ESC
   
        g.  move the residuals down: GOTO h4 ENTER 300 ENTER
   
        h.  get rid of the symbols for all the lines:
             / G O F G L Q Q Q
   
       Now move to the amplitude and phase cells by yourself
       and play with the numbers to see how Lotus recalculates
       the columns and updates the graph.  End with an amplitude
       of about 100 and a phase of about 1.57.
   

8. print out the graph and the parameter guesses to an encoded file and then to the laser printer: / P E plot.enc ENTER I C G (long delay) R g1..h4 ENTER G Q Now type lpr -P(your laser printer) plot.enc in a shelltool window to get a hardcopy like the following.

                                 graph
   

9. save your work and quit: / F S filename ENTER / Q Y

This example demonstrates some aspects of Lotus. The program has much more power for you to use. Enjoy yourself and make use of the tools provided!

HTML