NOS : Certificate in Computer Applications
| Home | Table of Contents |

Up: Online Course Material

Lotus 1-2-3 provides very powerful database functions under the data command. It permits you to sort, query or parse the data, construct frequency distributions and perform matrix operations. Another very powerful feature of data command is its ability to carry out regression and sensitivity analysis with only a few keystrokes. We shall now see how all this can be done in 1-2-3.

In this lesson, we will discuss the data command of 1-2-3. By the end of this lesson, you should be able to:

• carry out sensitivity and regression analysis on the data in the 1-2-3 created worksheet; and
• sort data, perform matrix functions or query data.

The data command of 1-2-3 permits the user to perform some data base functions on the worksheet created by 1-2-3. When you invoke /Data command, the following options are given to you:

Data

We shall now discuss the usage of each one of these options.

/Data Fill command is used to fill a range in the worksheet with numbers having a constant skip factor. This command finds its use in /Data Distribution and /Data Table commands, which we shall study in this lesson. /Data Fill command also finds its use in the preparation of values for the X-range for a graph or for numbering rows and columns.

When you invoke /DF, you are required to mark the range to be filled with numbers and the start, step value and stop for the numbers with which the marked range is to be filled. For example, if you want to fill a range with 5,10, 15, 20------- 65, then…. Start = 5, step = 5 and stop = 65.

/Data Table command can be used to create a table of different values a formula takes each time the value of one or two variables occurring in the formula are changed. This command is very useful when performing sensitivity analysis. Because the table constructed by this command typically answer your what-if queries. When you invoke /DT the following menu is presented.

We will explain the usage of the above three options with examples.

/DT1: Consider a situation, where a company accepts fixed deposits for the amounts Rs.5000, Rs.10,000 and Rs.20,000. The company wants to make a policy for the interest rate for these amounts. For this purpose the company wishes to calculate the interest payable at 12%, 12.5%, 13% 13.5% and 14% for each of these amounts.

Interest payable = rate of interest ´ amount deposited. To construct a table giving the various amounts of interest payable, the following steps have to be followed:

• Identify the range where the table is to be constructed say C2..F7

• Write the formulas in the top row of the table leaving the first cell blank. Therefore, cells D2, E2, F2 contain 5000*A1, 10000*A1 and 20000*A1 respectively, where A1 is the address of input cell which identifies the variable on which each of these formulas depends. You can format this range of cells (D2..F2) to display the formulas rather than values to increase the readability of the table (using /RFT).
• The different values that the variables represented by A1 takes (in our case, rates of interest 12%, 12.5%, …14%) are put in the first column of the table range, leaving the top cell of the column blank. Thus, these entries appear in the range, C3..C7. You may format this range as per cent (using /RFP).
• Worksheet appears like this.

• Invoke /DT1.
• Mark C2..F7 as the table range.
• Mark A1 as the input cell.

The worksheet display changes to:

/DT2: We shall now describe the steps involved in constructing a table where there is one formula dependent on two variables. A table of values, that the formula would take, has to be prepared when corresponding to each value in a set of values for variable 1, variable 2 takes a value from its set of values. The steps are:

• Identify the range where the table is to be constructed, say C2..F5
• Enter the formula in C2 referring to cell addresses of input cells 1 and 2. To increase the readability of the table, you may format C2 to display the formula rather than its value.
• Enter the first set of values in the first column of the table range just below the cell where you entered the formula i.e. in C3..C5. Lotus 1-2-3 will use these values one by one in place of the cell address of input cell 1 in the formula written in C2.
• Enter the second set of values in the first row of the table range to the right of the cell in which the formula was entered, i.e. in D2..F2. Accordingly 1-2-3 will use these values one by one in place of the cell address of input cell 2 in the formula.
• Invoke /DT2.
• Mark the table range as C2..F5
• Mark the input cells 1 and 2 to which you have made a reference in the formula.

Fill in the blanks.

1. /DT1 is used when there are _______ dependent formula(s) and ______ input variables while /DT2 is used when there are _______ dependent formula and _______ input variables.
2. When there is one dependent formula and two input variables, the dependent formula is written in ________ . The values taken by the first input variables are written in ________ and the values taken by the second input variable are written in ___________ .
3. The range specified (A1..A5) for /Data Fill was initially blank. Start value = 1, step value = 4, stop value = 10 were specified. The following would be the entries in the cells: A1: _______ , A2: _______ , A3: ________, A4: ________ , A5: ________ .

Top

Sorting of data is an important capability of Lotus 1-2-3. /Data Sort command allows the user to sort the data in a specified range of the worksheet up to maximum two levels in either ascending or descending order. You can sort in either alphabetical or numeric order. When you invoke /DS, you will see the following menu.

/Data Sort Data-Range is used to specify the range of data to be sorted.

/Data Sort Primary-Key is used to specify the primary key (i.e., the range of cells) on which the data is to be sorted.

/ Data Sort Secondary-Key is used to specify the secondary key (i.e., the range of cells to be sorted within a particular cell of primary key) for sorting.

/Data Sort Reset resets all the sorting parameters set by /Data Sort command.

/Data Sort Go is used to instruct 1-2-3 to start sorting the data as specified by /DSD and on the primary and secondary (optional) keys as specified by /DSP and /DSS respectively.

/Data Sort Quit quits the sort menu.

We will use the following worksheet as the basis to explain each one of these options:

Suppose, you want to present all the items in a sorted manner while making sure that the medicinal items occur under the head MEDICINES and cosmetic items under the head COSMETICS. While sorting the data, you would obviously want the data associated with each item (i.e., units sold, profit/unit, profit) to occur with the name of the item. In this example, therefore, the data-range to be sorted, primary key and secondary key will be A6..E13 and B6..B13 respectively. When you specify ranges for primary or secondary key, you are prompted to enter the order of sorting - A or D where A is for ascending and D for descending.

/Data Query is a very useful command of Lotus 1-2-3 whenever you are using 1-2-3 worksheet as database file. /DQ permits the user to find records meeting a specified criterion, copy them to another part of the worksheet and delete them from the current database. When you invoke /DQ, you will see the following menu.

Query

We will explain each one of these options using the sample worksheet given in Fig. 18.5.

/Data Query Input permits you to specify the range that has to be searched to answer the queries. In our example, you could specify the range as A6..E13.

/Data Query Criterion command is used to specify the range of cells where the criterion for the query is written. For specifying the criterion, you need to copy the field names (i.e., the description of the columns) to an unused part of the worksheet. You may copy only the field names on which you wish to build a criterion. Suppose that you copy the range A5..E5 to A20..E20 and that you wish to write the criterion for the UNITS SOLD>= 100. To set up the criterion under the cell in which UNITS SOLD is written (i.e., C20) write the formula +C6>=100. Therefore, this formula has to be written in the cell C21. The value of this formula, i.e., 1 (which represents true) appears in C21. You could format the criterion range to present the formulas rather than their values (by /RFT) to make the criterion more readable. Though the formula only specified cell C6, when you execute the query, all cells in the column C of input range are tested. A little later we shall learn how to construct more complicated queries. Therefore, when you invoke /DQC, you will specify the criterion range as A20..E21.

/Data Query Output is the output range where the answers to your queries shall be placed. Before specifying the range for output, you must copy the data fields to an unused part of the worksheet say to G1. If you don't copy all the data fields, to the output range, the data pertaining to the copied fields and satisfying the criterion would appear in the output range. When you invoke /DQO while specifying the output range, include the field names in the range and include enough number of rows and columns to accommodate the data meeting the criterion, say G1..K8.

/Data Query Find finds and highlights the first record that meets the specified criterion in the input range. You can use the down arrow key to highlight the next record which meets the criterion and so on.

/Data Query Extract command copies all the records that meet the criterion, i.e., the output range. Here data in the records with row numbers 6,7,8 and 13 are copied to the range G2..K5.

/Data Query Unique is the same as /DQE but does not copy duplicate records.

/Data Query Delete, when invoked, puts a safety check by giving options Cancel Delete. As the name suggests, this command deletes the records meeting the criterion.

/Data Query Reset resets the input, criterion and output range of the data query.

/Data Query Quit quits the query menu.

We will now discuss the criterion building in a little more detail. Suppose, you want to extract records for which UNITS SOLD>=100 and PRICE>0.25. Then, in addition to writing +C6>=100 in C21, enter the formula +D6>0.25 in D21. Lotus 1-2-3 treats two or more conditions written in the same row with AND between them and the ones in different rows as having OR between them.

It should be noted that

• The field names must not continue in more than one row.
• There should be no blank line between the field names and the data in input range.
• The field names in the criterion and output range must be the same as the ones in the input range.

Top

1. Consider the worksheet in Fig. 18.5 and write the criterion for medicines for which either the UNIT SOLD is more than 100 or PROFIT/UNIT is more than 1.00. 

Top

/Data Distribution command is used to create a frequency distribution of the specified data range. Here you are also required to specify a range of cells in which for constructing the frequency distribution table is located.

For those who are not familiar with frequency distributions, we would briefly explain the term. Suppose you have a number of values. Then frequency distribution tells you precisely how many values fall in each of the specified intervals. Consider the worksheet in Fig 18.5. Suppose that for the number of units sold (i.e., for the range C6..C13 ), we want to know how many items fall between the range 0 to 50, 50 to 100, 100 to 150, 150 to 200, 200 to 250 and above 250. In other words we want to know how many such items are there, for which the number of units sold is less than 50, between 50 and 100 and so on. Let us specify the intervals for frequency distribution in column G. Position the cursor at G1 and invoke /Data Fill. Specify start = 50, step = 50, stop 250. G column appers like this:

Now, invoke /Data Distribution and specify data range as C6..C13, and interval range as G1..G6. Columns G and H of the worksheet will appear like this:

Thus the number of items for which the units sold were less than equal to 50 is 4, more than 50 and less than equal to 100 is 2, more than 100 and less than equal to 150 is nil, more than 150 and less than equal to 200 is nil, more than 200 and less than equal to 250 is 1 and more than 250 is also 1.

You must have noticed that the frequencies appear in the cells immediately to the right of the interval range.

Matrix, as most of us would know, is a set of numbers arranged in a group of rows and columns. Two matrices can be multiplied provided the number of columns of the first matrix is the same as the number of rows of the second. Also a matrix can be inverted provided it is a square matrix. Also, if one were to multiply a matrix with its inverse, the result would be an identity matrix (i.e., a matrix that has 1 along the principal diagonal and 0's elsewhere). You can multiply two matrices or find the inverse of a matrix using /Data Matrix command. When you invoke /DM, you will see the following menu.

/Data Matrix Invert command requires as a parameter a square matrix to be inverted. A square matrix is one where number of rows is equal to the number of columns. You also need to specify the output range for the resulting inverted matrix. In fact specifying the upper left corner of the output range would be sufficed.

/Data Matrix Multiply is used to multiply two matrices. You are required to specify a data range as the first matrix and another data range as the second matrix for multiplication. Also, you have to mark the output range. The largest matrix that can be used for any of these two operations can have 90 rows ´ 90 columns.

Top

1.  Consider the following worksheet.

1. Write the steps involved in storing the invert of matrix A1..B2 at D1..E2.
2. Write down the steps for multiplying the above two matrices so as to obtain an identity matrix at A4..B5.

In the business world, Regression analysis is one of the most powerful and widely used analytic methods. It analyses relationship between two sets of data based on a hypothesis.

For example, how your investment on the advertisements affects the sales of your company. You may like to work out the relationship between the two to forecast for your company. Consider the following data:

Fig. 18.7: Sample Worksheet - 2

We shall use this sample worksheet to explain the various options available under /Data Regression command. When you invoke /DR the following options appear:

/DRX is used to mark the X-Range, i.e., the independent variable. In our case, we will mark the range B2..B9.

/DRY is used to mark the Y-Range, i.e., range of dependent variable. A2..A9 in this case.

/DRO is used to specify the output range. Move the cursor to A11 and press ENTER

/DRI gives the following two options: Compute Zero

Compute: This option computes the intercept. (see Output in Fig. 18.9)

Zero: The option keeps the intercept at the origin. (see Output in Fig. 18.10)

/DRR may be used to reset the ranges and the intercept marked by /DR.

/DRG instructs 1-2-3 to perform regression analysis and presents the result at the range specified.

/DRQ quits the regression menu.

With the above-mentioned X-Range, Y-Range, Output-Range and Compute Intercept the following results are displayed when /DRG is invoked.

When we give the X, Y and Output ranges and Zero intercept, the following results are displayed when /DRG is invoked.

Remember that you have to give the Go command to display the result.

When you import a text file using /File Import command, each row is one single label and hence cannot be manipulated by any of the 1-2-3 commands. /Data Parse command can be used to break the information in each row into 1-2-3 command useable columns. Thus a parsed file contains all the information of the imported text file but broken into columns that can be manipulated by the commands available in 1-2-3.

When you invoke /DP you will see the following menu:

We will explain the use of each of these options using imported text file in Fig. 18.12..

Format-Line: When you invoke /DPF you will see the options: Create and Edit.

Create: This option creates a format line at the current cell.

Edit: This option edits the format line at the current cell.

A format line is parse spilt indicating the labels, values, etc. In the worksheet under consideration, position the cursor at A10 and invoke /DPFC. The resultant worksheet is displayed in Fig. 18.13.

Lotus 1-2-3 has made a guess on the kind of information available in the columns and hence how the file should be parsed. In the format line:

L  indicates the beginning of label block

V indicates the beginning of value block

> Indicates that continuation of block marked by L, V, D, T or S

* Indicates that blank space is currently not defined but could become a part of the information in the next cell

Input-Column: Having created the format line, you are to specify the data to be parsed. Refer to worksheet in Fig. 18.13. The input data would be A10..F15. Make sure that the format line is included in the input-column.

Output-Range: It is the range where the parsed output should appear. Position the cursor at A10 and press ENTER.

Reset: May be invoked if you want to reset or cancel all the parameters (i.e., format line input-column and output-range).

Go: Parse the specified range.

Quit: Return to READY mode.

After creating the format line and specifying input-column and output-range, if you select Go, you will notice that the information in the rows 10, 11, 12, 13, & 14 is no more single label but labels and values spilt in 1-2-3 readable form.

Top

In this lesson, we learnt the database functions available in 1-2-3. The main points observed were:

/Data Table command of 1-2-3 enables us to carry out sensitivity analysis for the data in the worksheet.

Data in the worksheet can be sorted on two keys: primary and secondary.

Data meeting specified criteria can be formed, extracted or deleted from the database.

Matrix operations and regression analysis can be carried out with only few keystrokes.

18.5 TERMINAL QUESTIONS

1. What is the usefulness of Sort and Query commands in Lotus 1- 2-3?
2. Why do you use Parse command in lotus?
3. What is the difference between data matrix invert command and data matrix multiply commands?

1. i. one or more

one

one

two

2. the upper left corner of the table range

the first column of the table range below the dependent formula
the first row of the table range to the right of the dependent formula

3. A1: 1

A2: 5

A3: 9

A4:

A5:

The cells A4 and A5 will remain blank as stop value = 10.

1 ITEM UNITS SOLD PROFIT/ UNIT

+C6 >100 +D6>1.00

IN-TEXT QUESTION 18.3

1.

• Invoke /DMI.
• Specify A1..B2 as input range and D1..E2 as output range. The inverted matrix appears in the specified output range.

2.

• Invoke /DMM
• Specify A1..B2 as the first input data range, D1..E2 as the second input range and A4..B5 as the output range. The resultant multiplied matrix (an identity matrix) appears in the specified output range.

Top