 Loading input.gsc-input { border-color: #BCCDF0; } input.gsc-search-button { border-color: #666666; background-color: #CECECE; }
 Everything HP200LX: Knowledge, Products, Service
 Archives 9 years, 100's of pages of HP Palmtop Paper, jammed with tips, reviews and how-to's Links Most useful, up-to-date Web sites devoted to the HP 200LX Downloads Direct link into 1000's of pieces of Palmtop software Tips from Our Techs 2008 Catalog (PDF, 2.6 MB) Contact Us/About Us
 Weekly Palmtop Paper Newsletter (Palmtop tips, latest happenings, and HP Palmtop Paper Store information)
HOW TO USE Database Part II

# HOW TO USE Database Part II

### By Carl Merkle

The November/December 1995 issue reviewed some basics of how to use 1-2-3 as a sophisticated database program. Specifically, we created a sales database listing the sales person, customer, month, part number, quantity, unit price and totals (see screen graphic, this page). The reason 1-2-3's database capability is important is that the built-in DataBase program, while useful for capturing data and printing lists, does not provide the ability to create reports with calculated fields and calculated group subtotals. Thus 1-2-3 can be used to enhance the Palmtop's built-in DataBase program.

The November/December issue demonstrated how easy it is to create a 1-2-3 database and calculate group subtotals within a database using a subtotals formula that we created. We also developed an example showing how to link two database tables together using the @vlookup formula, thus obtaining capabilities similar to those found in "relational databases." The techniques employed, while useful, turn out not to be the most efficient and flexible way to create reports containing only results of the subtotals formulas.

In this issue we'll take the database linking and subtotals concept a step further and discuss how to create a database lookup table in 1-2-3 with tabulated results using 1-2-3's @DSUM formula. Then we'll discuss how to use 1-2-3's @vlookup formula to grab results from the lookup table for inclusion in a report section elsewhere in the spreadsheet. In this context we'll discuss an error trapping technique you may want to consider when using 1-2-3's lookup formula.

Sales Database with monthly sales amounts calculated using subtotals formula (from November/December 1995 issue).

Introduction to 1-2-3's database summing formula

We'll use the 1-2-3 database spreadsheet displayed below as a starting place for this tutorial. This was created based on the instructions given on pages 54-56 of the November/December 1995 issue. (Note that there were errors in the spreadsheet displayed in that issue which have been corrected.)

This time we will use another approach to construct a subtotals by month formula. To do this, we'll use 1-2-3's @DSUM formula, a powerful Database Statistical Function that lets you add up amounts in a field (column) of a database only if certain records (rows) in the database meet certain criteria that you specify. The syntax for the formula is (on a single line):

@DSUM(Database_Range, Field_Column_No, Criterion_Range)

Below is an explanation of the above formula:

Database_Range -- The range of our worksheet containing data. In our example above the database range is in cells B3 through I13. Notice that this range includes eight fields (columns B through I) and also includes the field names (Empl, Cust, Mo, etc.).

Field_Column_No -- We want to be able to create subtotals of the data in the Sales field, which has a field column number of 6. (The first field in the Database Range is Empl and has a field column number of 0. The second, Cust, has a field column number of 1, third, Mo, has a field column number of 2, etc.).

Criterion_Range -- This specifies where @DSUM will look for the selection criteria (i.e., which amounts in the Sales column 1-2-3 should add). The top row of a criterion range must contain one or more of the field names from the database. The second and subsequent rows of the criterion range hold the criteria themselves. For our example, we'll construct a criterion range in cells D16 through D17. We'll enter the label Mo in cell D16 and the number 1 in cell D17.

To complete this example enter the following formula in cell H17:

@DSUM(B3..I13,6,D16..D17)

This formula says the following: For the range of cells between B3 and I13, add up all the data in cells in the Sales field (field column number 6), but only include the data if the number associated with it in the Month column is 1 (i.e., sales figures from the first month).

The result of the formula should be \$1,137,500. If the number 2 is entered into D17 the result should change to \$812,500.

The only problem with using this method is that to create multiple subtotals you need multiple criterion ranges, each with a different month. These are a pain to create. However, there are at least two ways around this that I am aware of. One involves the use of the data table command. The other uses a macro to simulate the data table command. The data table method is presented in this article. A future article will cover the macro method.

Calculating multiple subtotals using the Data Table command

1-2-3's Data Table command lets you build a kind of what-if table in the worksheet. In this example, we want to vary the month and determine the sales subtotals (i.e., what if the month was 1, what if the month was 2, etc.). The first thing we'll do is to create a data table to vary the month number as an input into the @DSUM formula. In cell G18 enter 1, in G19 enter 2, and in G20 enter 3, in G21 enter 5.

We'll calculate the subtotals of each months sales using the Data Table feature as follows:

1. 1. Press (MENU) Data Table 1. This command activates the Data Table function and tells it that you will have one variable in the formula.
2. 2. Enter table range as G17..H21 and press (ENTER). This specifies the range of cells in which the data table and formula are found.
3. 3. Enter input cell 1 as D17 and press (ENTER). This specifies the criteria needed by the @DSUM formula used in the data table.

4.

Your results should be as shown.

Sales spreadsheet displaying subtotals in the bottom left, using the @DSUM formula in the data table (G17 through H21)

The use of the Data Table command was discussed in the January/February 1996 issue. What it just did was replace the amount in D17 with each number in cells G18 through G21. As it changed each number, it calculated the result of the @DSUM formula in cell H17 and placed the result of each calculation in cells H18 through H21. As you can see 1-2-3 returned summary results in the table yielding answers that we expected (i.e., they match the monthly subtotals in column I of the Database Range).

You can easily change the selection criteria upon which Sales are being subtotaled by changing the field name in the criterion range and the record labels in cells G18 through G21. For example, let's say you wanted to look at a sales subtotal for a particular part numbers.

1. 1. Go to cell D16 and type in Part.
2. 2. Enter P01 in cell G18, P02 in G19, P03 in G20, and P05 in G21.
3. 3. Re-issue the Data Table command (points 1 to 3 above) or, simply press (F8) (TABLE).

4.

Then, add a description for each part number in column I. (We'll use these later in custom reports.) Enter Monitors, Workstations, Servers and Disk Drives in cells I18, I19, I20 and I21, respectively. Your results should appear as shown at the bottom of this page.

Sales subtotals report based on Part Number instead of Month.

Wrap up this portion of the tutorial by giving the sales subtotals lookup table (the range from G18 to I21) a short name like LTB as an abbreviation for Lookup Table. To do this issue the command (MENU) Range Name Create. When it prompts you to Enter Name: type LTB and hit (ENTER). When it prompts you to Enter range: you can either highlight the cells in the range from G18 to I21 or type in G18..I21 and hit (ENTER).

Displaying the results in a report

If you use the @DSUM approach on larger databases and with longer lists, you may occasionally want to subtotal the subtotals, or have them rearranged in a way that is easer to read. For example, in my profession I frequently need to take a client's general ledger database and summarize all the amounts in it into key financial statement categories. I then present the subtotal categories in an easy-to-read financial statement.

To create worksheets with great reporting flexibility, I use three areas in my worksheets:

1. 1. The database range (B3 through I13 in the above example),
2. 2. The lookup table which serves as an intermediate calculation table (G18 through I21 in the example above),
3. 3. A report range which looks up amounts and descriptions from the subtotals range using the @VLOOKUP formula discussed in the November/December 1995 article.

4.

(For instructional purposes this tutorial presents the database at the top of the worksheet with the report range at the bottom. However, a better habit is to have the report range at the top of the spreadsheet followed by the lookup table with the intermediate calculated results, which in turn is followed by the database at the bottom of the active spreadsheet area.)

The @vlookup feature lets you keep a constant value (like the unit price of an item), or a longer description of the item, or the subtotals like those just calculated in a separate part of the spreadsheet. You can then use the @VLOOKUP formula in a report located elsewhere to access amounts and descriptions from the Lookup Table. In the above example, I could use a @VLOOKUP formula to display the subtotals calculated with the Data Table command in another part of the spreadsheet. (In the above example we're working on, we will create a report range later at C24 through H32.)

Undesired results with @VLOOKUP

When creating any report from results and data contained in a lookup table you need to be very careful when using the @vlookup formula because a @vlookup formula has features which can produce undesired results. These undesired results are mainly produced in the report range when you ask 1-2-3 to lookup a number or text that does not exist in the index column of the lookup table. In the example we have been working on, we created the first data table which had month numbers. If we had tried to lookup the results for month 4 (which doesn't exist in the lookup table), we would have gotten an answer of 1,512,500. To understand how these undesired results can be produced, let's review again how the @vlookup formula works. The syntax of the @vlookup formula is (on a single line):

@vlookup(x,lookup_table_range, column_offset)

@VLOOKUP compares the variable x above to each cell in the first (left-most) column of the lookup table range. This column is called the index column. When 1-2-3 locates a cell in that column that contains x, it moves across that row the number of columns specified by the column-offset and returns the contents of that cell as the answer. If @VLOOKUP can't find a cell that contains x exactly, it will find and return the value closest to, but not larger than x. Thus the result 1-2-3 returns is located in the lookup table at the intersection of the row specified by x and the column specified by column_offset.

X can be either a value (number) or label (text). In addition, it can be the address or name of a cell that contains a value, label, or a formula that returns a value or text. If the index column of the lookup table contains values, then x must be values. If the index column of the lookup table contains text, then x must be text. If values are used in the index column, they must be in ascending order. If text is used in the index column they can be in any order. Entries in the index column of the lookup table should be unique - no duplicates.

For values, if x is smaller than the first value in a range, @VLOOKUP returns ERR (error). If x is larger than the last value in the index column of the lookup table, @LOOKUP stops at the last cell in the column specified by column-offset and returns the contents of that cell as the answer. If the value being looked up is not in the lookup table, 1-2-3 returns the results of the lesser of two lookup table index rows (i.e., if x is 24 and the index column of the lookup table contains 20 and 25 on adjacent rows, 1-2-3 will select the information on the row specified by the 20).

For text, if x does not exactly match the contents of a cell in the first column of range, @VLOOKUP returns ERR.

The problem I try to overcome when working with the @vlookup formula for report writing purposes is the fact that if x is a number, @vlookup can return a result even when x is not present in the index column of the lookup table. In addition, if x is text, @vlookup returns ERR when x is not present in the in the index column, and a number of ERRs in a report is confusing. To overcome this problem, I often use a conditional version of the lookup formula.

There are two forms of the conditional lookup formula one for values and one for text. The conditional lookup formulas work as follows:

1. 1. Conditional lookup with values -- This @vlookup formula first tests to see if the value being looked up exists in the index column of the lookup table (a column-offset of 0). If the value exists, 1-2-3 is instructed to perform a lookup using the value and the appropriate column-offset. If the value does not exist in the index column of the lookup table 1-2-3 is instructed to return a zero.
2. 2. Conditional lookup with text -- This @vlookup formula first tests to see if the text being looked up will return an error (meaning the text is not in the index column of the lookup table). If it is an error, 1-2-3 is instructed to return a zero. If it is not an error 1-2-3 is instructed to perform the lookup.

3.

Composing a conditional lookup formula

Let's create a report range in C24 through H32 and compose a conditional lookup formula for that range. Key in the following labels and formulas in the report range:

Cell Enter this

C24 REPORT RANGE

C26 Part

C27 P03

C28 P02

C29 P01

C30 P04

D26 Description

H26 Sales

H31 \-

H32 @SUM(H31..H26)

D25 2

H25 1

The entries in cells D25 and H25 will tell 1-2-3 which column in the lookup table contains data to be presented. The entries in cells C27 to C30 tell 1-2-3 which rows in the lookup table contain the data to be presented. Thus 1-2-3 has the appropriate column and row coordinates necessary to prepare a report. Because column or row coordinates can easily be changed in your report range, you can easily modify the report as you wish.

Since the lookup table (G18 to I21) has label entries in the index column (i.e., P01, P02 etc are text rather than numbers) our report range also uses labels in cells C27 through C30. Because labels are used the conditional lookup formula should test for errors. The following formula should be entered in cell H27 and copied down to H30. Also copy the formula in H27 to D27 through D30.

@IF(@ISERR(@VLOOKUP(\$C27,\$LTB,1)),0,@VLOOKUP(\$C27, \$LTB,H\$25))

Sales spreadsheet Report Range displaying sales figures based on part numbers.

You might want to try creating a monthly subtotals report based on the month number (value) instead of the part number (label). To do this, you'll first have to recalculate the subtotals of each months sales using the first Data Table example shown on page 48 of this article. Change the label in D16 from Part to Mo, change G18 through G21 to 1,2,3 and 5, and re-run the Data Table command as described on page 48.

Then in the REPORT RANGE you'll replace the Part (C26) with Mo and replace the part numbers with month numbers (C27 = 3, C28 = 2, C29 = 1, C30 = 4). Then change the text-based conditional formula in H27 to a value-based conditional lookup formula, and copy it down to H30 and across from D27 through D30.

H27: @IF(\$c27=@VLOOKUP (\$C27,\$LTB,0),@VLOOKUP (\$C27,\$LTB,H\$25),0)

Report Range displaying subtotals report based on months instead of part numbers. "Mo" replaces Part in C26. Month numbers replace part numbers.

This formula first checks for the existence of the value being looked up (C27) in the index column of the lookup table (the range we named LTB) by checking for equality (i.e., is the value in C27 equal to value found in the index column). If this is true the next lookup formula is performed in which 1-2-3 is told the appropriate column-offset to use. If it is not true (i.e., the value in C27 does not match a value in the index column of the lookup table) 1-2-3 is instructed to return a zero.

Conclusion

I have found the techniques presented in this and the November/ December 95 column to be valuable in my line of work in that they let me rapidly create useful analysis reports in 1-2-3 from databases. I usually obtain the databases in some other format and import them into 1-2-3. (A future article will be devoted to importing databases from other programs and exporting 1-2-3 data to other programs.) By experimenting with the techniques presented in this and the November/December 1995 column, you will find that your Palmtop PC can be used as a powerful front end reporting tool for databases that you others create in a traditional database program and import into 1-2-3. Unfortunately, since 1-2-3 is a memory hog and conducts all its work in RAM, the size of databases you can work with is limited both by available RAM and the 8,192 row limitation of 1-2-3.

In the next issue, we will explore a an extremely useful 1-2-3 add-in program called @BASE, which has recently been modified to run on the Palmtop. @BASE lets you work in 1-2-3 on huge databases that are in the dBase III format (most database programs can save their data files in this format). The size of database you can work on from within 1-2-3 using @BASE is limited only by the amount of space you have available on your flash card. Thus, with a 20 MB flash card, you could conceivably work with a monstrous dBase III file approaching 20 megabytes in size.  