|Everything HP200LX: Knowledge, Products, Service|
The 1-2-3 Column: Creating 1-2-3 Databases
Carl shows how to use 1-2-3 to create sophisticated databases that automatically calculate totals and subtotals.
The HP Palmtop comes with a built-in Database program. Unfortunately, it and the programs based on it, cannot do calculations based on entered data. You would need such capability if you want to create an electronic check book register on your Palmtop. You cannot do that with the database program. It turns out that 1-2-3 is the most useful built-in program for this type of database activity.
A 1-2-3 database is a worksheet table where each column is a field, and each row is a record. At my office, we constantly work with small databases to prepare financial statements and tax returns. The data we work with is usually stored in one of a variety of different accounting programs, spreadsheets, or database programs. We download it into a spreadsheet format and go from there.
Usually these databases are small, between 100 and 2,500 records too small to need a full-featured database program and too large to do the work manually. As it turns out, they're just right for 1-2-3.
Example: Sales Department
As an example, let's use a sales department database that lists sales during a three-month quarter. We'll use the 1-2-3 database to calculate employee sales and determine total sales by month. (The finished 1-2-3 database is displayed on the next page. Column B is the field for the employee name, column C for the customer, column D for the month # the sale took place in, and so on.)
Let's say I downloaded columns B through F from a corporate database. I want to calculate total sales in dollars so all I'll need to do is enter the unit prices for the items sold. I could manually enter this data, but there's a more efficient way to do this using 1-2-3's Lookup feature.
I keep Unit price data in a Part Price List in a separate part of the spreadsheet (cells B19 through C22). It looks like this:
17 B C
19 Part UnitPrice
20 P01 1,000
21 P02 5,000
22 P03 7,500
I type the following into cell G4: @VLOOKUP(E4,$B$20..$C$22,1). I then copy the contents of G4 to the range G5 to G13. The Unit prices shown above are displayed as a result of this Lookup feature. It effectively "joins" the two separate tables. (See sidebar, page 56 for more on the Lookup feature.)
Now calculate the total sales price in cells H4 to H13 by multiplying Quant (column F) by UnitPr (column G). Do this for H4 and then copy the formula through H13. Finally, total the Sales column (H) and you should get 52,000.
Formula to Rapidly Calculate Subtotals
The subtotals formula only works with a database that has already been sorted. Fortunately, the database above has been, by month. The subtotals formula is actually a formula within a formula.
Enter the following If formula in cell I4 (on one line):
This address has both relative addresses (D4 and D5) and absolute addresses ($H$3, $H4, etc.) Absolute addresses begin with $. They do not change if they are copied to another cell.
Copy cell I4 to I5 through I13, and total column I. You should see the subtotals of each month reflected on the last row of that month's sales.
Let's look at how this If formula works to produce the subtotals.
The syntax for the @if formula is: @IF(condition,true-result,false-result). 1-2-3 evaluates the condition (does D4=D5?). If the statement is true, the formula returns the true-result (0). If D4=D5 is not true, then the formula returns the false-result, which is the SUM formula (@SUM($H$3..$H4) -@SUM($I$3..$I3).
Look at the true-result above. In the subtotals formula in cell I4, the condition 1-2-3 was testing for is whether the month in cell D4 was equal to the month in cell D5. As long as the months are equal, 1-2-3 yields a true-result answer and the @if formula returns a 0. This is because we don't want 1-2-3 to give us a subtotal at that point. (Note: in this circumstance, cells D4 and D5 are numeric so we used the condition D4=D5. If you wanted to use it on text instead of numbers, substitute it with formula @EXACT(D4,D5)).
Now look at cell I7. The conditional formula checks to see if D7 is equal to D8. In this circumstance, D7 is not equal to cell D8. This means that we want to subtotal the sales for month 1 at this point. The same thing occurs at cells I10 and I13. Look at the formulas in these three cells (each formula is on one line):
I7: @IF(D7=D8,0,@SUM ($H$3..$H7)-@SUM($I$3..$I6))
I10: @IF(D10=D11,0,@SUM ($H$3..$H10)-@SUM($I$3..$I9))
I13: @IF(D13=D14,0,@SUM ($H$3..$H13)-@SUM($I$3..$I12))
The first @sum formula calculates the cumulative total of sales through the row the formula is on. The second @sum formula calculates the cumulative total of amounts through the line just above the line that the formula is on. The difference between those two numbers represents change. In this case, the change reflects monthly subtotals.
The use of the subtotals formula is a good example of how you approach a task differently on the computer. It would be too much work to manually add up a bunch of amounts and subtract previous totals. However, with an electronic spreadsheet this method is much quicker. The key to this is that it is faster to create one useful formula and copy it down rather than create many @sum formulas.
Copyright © 2010 Thaddeus Computing Inc