|Everything HP200LX: Knowledge, Products, Service|
HOW TO USE Database Part II
This article discusses how to use 1-2-3 as a sophisticated database program that can produce reports with amounts calculated from database fields using criteria you specify. This article builds on the database techniques presented in the November/December 1995 issue
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:
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:
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.
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:
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 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:
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
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.
Your results should appear show at the top of this page.
Sales spreadsheet Report Range displaying sales figures based on part numbers.
Try experimenting on your own
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.
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.
[123DB.WK1 is the completed spreadsheet example used in this article .]
Copyright © 2010 Thaddeus Computing Inc