======================================================================
  Microsoft(R) Product Support Services Application Note (Text File)
                   XE0210: CREATING AND USING TABLES
======================================================================
                                              Revision Date: 5/93
                                                          No Disk

The following information applies to Microsoft Excel, version See
Below.

 --------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY    |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an            |
| Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY      |
| KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO    |
| THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A     |
| PARTICULAR PURPOSE. The user assumes the entire risk as to the     |
| accuracy and the use of this Application Note. This Application    |
| Note may be copied and distributed subject to the following        |
| conditions: 1) All text must be copied without modification and    |
| all pages must be included; 2) If software is included, all files  |
| on the disk(s) must be copied without modification [the MS-DOS(R)  |
| utility DISKCOPY is appropriate for this purpose]; 3) All          |
| components of this Application Note must be distributed together;  |
| and 4) This Application Note may not be distributed for profit.    |
|                                                                    |
| Copyright 1989-1993 Microsoft Corporation. All Rights Reserved.    |
| Microsoft and MS-DOS are registered trademarks and Windows         |
| is a trademark of Microsoft Corporation.                           |
 --------------------------------------------------------------------

The information in this Application Note applies to:

 - Microsoft Excel versions 2.0, 2.01, 2.1, 2.1c, 2.1d, 3.0, 4.0, and
   4.0a for Windows
 - Microsoft Excel versions 1.0, 1.03, 1.04, 1.06, 1.5, 2.2, 3.0, and
   4.0 for the Macintosh(R)

This Application Note describes how to create one-input and two-input
data tables using the Table command on the Data menu. It also
discusses how you can use one-input and two-input tables to evaluate
database information obtained using database functions and comparison
or computed criteria.

                          OVERVIEW OF TABLES
                          ==================

When you create a formula, you may want to see the results of that
formula with various values. Rather than re-creating the formula each
time you want to test a new value, use the Table command on the Data
menu to create a table. With the Table command, you can test a formula
with different values without having to retype or copy the formula for
each value you want to test.

For example, the formula "=itemcost *8.1%" will calculate tax on an
item based on the item's cost (itemcost). If there are several items
that you want to calculate tax for, you could type the formula in for
each item, as shown in the example below.
 Table created manually (with formulas displayed):

   |  A          B
 --|---------------------
 1 | Item Cost  Tax
 2 | 15         =A2*8.1%
 3 | 17.5       =A3*8.1%
 4 | 22.35      =A4*8.1%
   
Table created manually (with values displayed):

      A         B
 -|-----------------
 1| Item Cost Tax
 2| $15.00    $1.22
 3| $17.50    $1.42
 4| $22.35    $1.81

To create the tables in the previous examples, you have to type or
copy the formula for each value you want to evaluate. However, if you
create a table with the Table command, you only have to type the
formula once.
   
Table created with Table command (with formulas displayed):

        A          B
 --|---------------------
 1 | Item Cost =C1*8.1%    <- The formula is typed in once.
 2 | 15        =TABLE(,C1) <- The Table command puts the results here.
 3 | 17.5      =TABLE(,C1) <-
 4 | 22.35     =TABLE(,C1) <-
   
C1 represents a variable. The values in cells A2:A4 are substituted
for C1 and the corresponding result is placed in cells B2:B4.

Table created with Table command (with values displayed):

      A         B
 -|-----------------
 1| Item Cost Tax
 2| $15.00    $1.22
 3| $17.50    $1.42
 4| $22.35    $1.81

The values displayed in cells A1 and B1 are number formats. To
duplicate these values, do the following:

1. Select cell A1.

2. From the Format menu, choose Number.

3. In the Code box (the Format box in versions 2.x), type "Item Cost"
   (without the quotation marks).

4. Choose OK.

5. Repeat the above procedure for cell B2, entering the format Tax.



CREATING A ONE-INPUT TABLE
==========================

A one-input table allows you to test for changes in a formula based on
one variable. One-input tables can be organized in two ways: column
input or row input.


Entering the Input Values in a Column
-------------------------------------

Column input tables are organized with the values listed in a vertical
array and the formulas listed horizontally.

To create a simple column input table, do the following:

1. In cells B3:B6, type 10, 13, 14, and 19.

    NOTE: These values are the variables that will be substituted into
    the formulas.

2. In cell C2, type the formula "=B1+2" (without the quotation marks).

    NOTE: In this formula, B1 is the column input cell. This column
    input cell represents the variable value in the formula; this cell
    must be located outside the table (it may or may not contain
    data). This table is set up in cells B2:E6, and since B1 is
    outside the table, it is a valid column input cell.

3. In cell D2, type the formula "=B1*2" (without the quotation marks).

4. In cell E2, type the formula "=INT(B1/2)" (without the quotation
   marks).

5. Select the range of cells B2:E6.

6. From the Data menu, choose Table.

7. In the Column Input Cell box, type "B1" (without the quotation
   marks<). Leave the Row Input Cell box blank, since this is a one-
   input table.

One-input table with input values in a column (with formulas
displayed):

   A  B      C            D           E
-|---------------------------------------------
1|    
2|      =B1+2        =B1*2         =INT(B1/2)
3|   10 =TABLE(,B1)  =TABLE(,B1)   =TABLE(,B1)
4|   13 =TABLE(,B1)  =TABLE(,B1)   =TABLE(,B1)
5|   14 =TABLE(,B1)  =TABLE(,B1)   =TABLE(,B1)
6|   19 =TABLE(,B1)  =TABLE(,B1)   =TABLE(,B1)

One-input table with input values in a column (with values displayed):

   A     B    C   D       E
 -|-----------------------------
 1|   
 2|         #+2   #*2   INT(#÷2)
 3|      10   12     20         5
 4|      13   15     26         6
 5|      14   16     28         7
 6|      19   21     38         9

The values displayed in cells C2, D2, and E2 are number formats. To
duplicate these values, do the following:

1. Select cell C2.

2. From the Format menu, choose Number.

3. In the Code box (the Format box in versions 2.x), type "#+2" (you
   must include the quotation marks).

4. Choose OK.

5. Repeat the above procedure with cells D2 and E2, entering the
   formats "#*2" and "INT(#/2)", respectively. As in step 3, you must
   enclose these entries in quotation marks.


Entering the Input Values in a Row
----------------------------------

Row input tables are organized with the variable values listed in a
horizontal array and the formulas listed vertically.

To create a simple row input table, do the following:

1. In cells C9:F9, type 19.95, 20.98, 13.50, and 10.

    NOTE: These values are the variables that will be substituted into
    the formulas.

2. In cell B10, type the formula "=A10*7.8%" (without the quotation
   marks).
    
    NOTE: In this formula, A10 is the row input cell. The row input
    cell represents the variable value in the formula and must be
    located in a cell outside the table; this cell may or may not
    contain data. This table is set up in cells B9:F11, and since A10
    is outside the table, it is a valid row input cell.

3. In cell B11, type the formula "=A10+A10*7.8%" (without the
   quotation marks).

4. Select cells B9:F11.

5. From the Data menu, choose Table.

6. In the Row Input Cell box, type "A10" (without the quotation
   marks). Leave the Column Input Cell box blank, since this is a one-
   input table.


One-input table with row input cell (with formulas displayed):

   A B              C             D             E             F
--|----------------------------------------------------------------------
  |    
9 |                 19.95         20.98         13.5          10
10|  =A10*7.8%      =TABLE(A10,)  =TABLE(A10,)  =TABLE(A10,)  =TABLE(A10,)
11|  =A10+A10*7.8%  =TABLE(A10,)  =TABLE(A10,)  =TABLE(A10,)  =TABLE(A10,)

One-input table with row input cell (with values displayed):

    A     B      C       D        E       F
 --|------------------------------------------
   |   
 9 |          $19.95  $20.98   $13.50   $10.00
 10|    Tax   $1.56   $1.64    $1.05    $0.78
 11|    Total $21.51  $22.62   $14.55   $10.78

The values displayed in cells B10 and B11 are number formats. To
duplicate these values, do the following:

1. Select cell B10.

2. From the Format menu, choose Number.

3. In the Code box (the Format box in versions 2.x), type "TAX"
   (without the quotation marks).

4. Choose OK.

5. Repeat the above procedure with cell B11, entering the format
   Total.



CREATING A TWO-INPUT TABLE
==========================

A two-input table allows you to test how changes in two variables
affect one formula. When you create a two-input table, you specify
input cells for the Row Input Cell box and for the Column Input Cell
box in the Tables dialog box.

To create a simple two-input table, do the following:

1. In cells B15:B19, type 1, 2, 3, 4, and 5.

2. In cells C14:G14, type 6, 7, 8, 9, and 10.

3. In cell B14, type the formula "=A14*2+A15" (without the quotation
   marks).

    NOTE: In this formula, A14 is the column input cell (which will
    substitute values 1, 2, 3, 4, and 5) and A15 is the row input cell
    (which will substitute values 6, 7, 8, 9, and 10). These input
    cells must be located outside the table; they may or may not
    contain data. Since this table is set up in cells B14:G19, and
    since A14 and A15 are outside the table, they are valid column and
    row input cells.

4. Select B14:G19.

5. From the Data menu, choose Table.

6. In the Row Input Cell box, type "A15" (without the quotation
   marks), and in the Column Input Cell box, type "A14" (without the
   quotation marks).


Two-input table (with formulas displayed):

   NOTE:  Due to character-based screen display limitations, the
   following 6-column table is  shown in two parts.

(Left 3 columns of a 6 column table)

        B                C                D
 - |-----------------------------------------------
 14| =A14*2+A15   6                7
 15| 1            =TABLE(A15,A14)  =TABLE(A15,A14)
 16| 2            =TABLE(A15,A14)  =TABLE(A15,A14)
 17| 3            =TABLE(A15,A14)  =TABLE(A15,A14)
 18| 4            =TABLE(A15,A14)  =TABLE(A15,A14)
 19| 5            =TABLE(A15,A14)  =TABLE(A15,A14)

(Right 3 columns of a 6 column table)

         E                F                G
 -------------------------------------------------
 8                9                10
 =TABLE(A15,A14)  =TABLE(A15,A14)   =TABLE(A15,A14)
 =TABLE(A15,A14)  =TABLE(A15,A14)   =TABLE(A15,A14)
 =TABLE(A15,A14)  =TABLE(A15,A14)   =TABLE(A15,A14)
 =TABLE(A15,A14)  =TABLE(A15,A14)   =TABLE(A15,A14)
 =TABLE(A15,A14)  =TABLE(A15,A14)   =TABLE(A15,A14)

Two-input table (with values displayed):

    B    C    D    E    F   G
 --|---------------------------
 14|      6    7    8    9   10
 15| 1    8    9   10   11   12
 16| 2   10   11   12   13   14
 17| 3   12   13   14   15   16
 18| 4   14   15   16   17   18
 19| 5   16   17   18   19   20

Note that the result in cell C15 is 1*2+6, which equals 8. The values
in cells B15:B19 are internally substituted into the column input cell
(A14), and the values in cells C14:G14 are internally substituted into
the row input cell (A15).

The blank value in cell B14 is a number format. To duplicate this
value, do the following:

1. Select cell B14.

2. From the Format menu, choose Number.

3. In the Code box (the Format box in versions 2.x), type "" (that is,
   two quotation marks).

4. Choose OK.
                                                                      
                                   
           USING TABLES TO ANALYZE INFORMATION IN A DATABASE
          ==================================================

You can use database functions in one-input and two-input tables to
analyze values obtained from a database using both comparison and
computed criteria.


WITH COMPARISON CRITERIA
========================

Comparison criteria is the type of criteria most commonly used to
extract or analyze information from a Microsoft Excel database. The
value you place under the column heading in your criteria range is
compared against the records in your database. If a record matches
that value, it is extracted or included in the group of records to be
analyzed using the database functions. For the following two examples,
you will need to create a sample database and a sample criteria range.

To create a sample database, type the following information in cells
A1:C25 of a new worksheet. Then select cells A1:C25 and choose Set
Database from the Data menu.

         A             B            C
 --|--------------------------------------
 1 | Type of Soda     Month      Consumed
 2 | Pepup          January     946
 3 | Diet Pepup     January     762
 4 | Colo           January     224
 5 | Diet Colo      January     1
 6 | Splash         January     715
 7 | Diet Splash    January     506
 8 | Lime-Up        January     354
 9 | Diet Lime-Up   January     542
 10| Pepup          February    910
 11| Diet Pepup     February    894
 12| Colo           February    926
 13| Diet Colo      February    471
 14| Splash         February    493
 15| Diet Splash    February    276
 16| Lime-Up        February    45
 17| Diet Lime-Up   February    301
 18| Pepup          March       840
 19| Diet Pepup     March       442
 20| Colo           March       409
 21| Diet Colo      March       205
 22| Splash         March       109
 23| Diet Splash    March       263
 24| Lime-Up        March       603
 25| Diet Lime-Up   March       555

To create a sample criteria range, type the following data in cells
E1:G1 of the worksheet. Select E1:G2, and from the Data menu, choose
Set Criteria.

          E             F          G
 --|-------------------------------------
 1 | Type of Soda     Month     Consumed
 2 |


In a One-Input Table
--------------------

To find the cost of soda consumed per type in the entire period,
create a one-input table using the data from the database:

1. In cells E5:E12, type the different kinds of soda (because this
   variable data is entered in a column, this will be a column input
   table).

    NOTE: You can copy the types from the database and paste them into
    the cells.

2. In cell F4, type the formula:

 =DSUM(Database,"Consumed",Criteria)*0.45

    NOTE: This formula will add all the consumed sodas in the database
    that match the specified criteria and multiply the result by 45
    cents (the cost per can).

3. Select cells E4:F12.

4. From the Data menu, choose Table.

5. In the Column Input Cell box, type "E2" (without the quotation
   marks).

    NOTE: E2 is the cell in the criteria range where you would type
    the name of a specific type of soda. Since you want to substitute
    different types of soda to calculate the expense for each type, we
    leave cell E2 blank in the actual criteria. The table will
    automatically (internally) substitute each soda type that we have
    listed in our table (E4:E12) into cell E2 and calculate the
    formula based on that criterion.

One-input table with data from database (with formulas displayed):

         E               F
 --|---------------------------------------------------------
 4 | First Quarter  =DSUM(Database,"Consumed",Criteria)*0.45
 5 | Pepup          =TABLE(,E2)
 6 | Diet Pepup     =TABLE(,E2)
 7 | Colo           =TABLE(,E2)
 8 | Diet Colo      =TABLE(,E2)
 9 | Splash         =TABLE(,E2)
 10| Diet Splash    =TABLE(,E2)
 11| Lime-Up        =TABLE(,E2)
 12| Diet Lime-Up   =TABLE(,E2)
 
One-input table with data from database (with values displayed):

          E                   F
 --|-----------------------------------------
 4 | First Quarter   Money Spent on Beverages
 5 | Pepup           $1,213.20
 6 | Diet Pepup      $944.10
 7 | Colo            $701.55
 8 | Diet Colo       $304.65
 9 | Splash          $592.65
 10| Diet Splash     $470.25
 11| Lime-Up         $450.90
 12| Diet Lime-Up    $629.10

The value displayed in cell F4 is a number format. To duplicate this
value, do the following:

1. Select cell F4.

2. From the Format menu, choose Number.

3. In the Code box (the Format box in versions 2.x), type "MONEY SPENT
   ON BEVERAGES" (without the quotation marks).

4. Choose OK.


In a Two-Input Table
--------------------

For the following example, use the sample database and criteria that
you previously created.

To find the cost of soda consumed per type per month, create a two-input table, as follows:

1. In cells E15:E22, type the different types of soda. (This
   represents the column input.)
    
    NOTE: You can copy the types from the database and paste them into
    the cells.

2. Type "January" (without the quotation marks) in cell F14,
   "February" (without the quotation marks) in cell G14, and "MARCH"
   (without the quotation marks) in cell H14.

3. In cell E14, type the formula:

 =DSUM(Database,"Consumed",Criteria)*0.45
    
    NOTE: This formula will add all the consumed sodas in the database
    based on the criteria and multiply the total by 45 cents (cost per
    can).

4. Select cells E14:H22.

5. From the Data menu, choose Table.

6. In the Row Input Cell box, type "F2" (without the quotation marks).
   In the Column Input Cell box, type "E2" (without the quotation
   marks).
    
    NOTE: F2 is the cell in the criteria range where you would type
    the name of a specific month. Since you want to calculate the
    expenses for each type of soda for each month and do not want to
    limit your expense analysis to one particular month, leave F2
    blank in the defined criteria range. The table will automatically
    (internally) substitute each month that you have listed in the
    table (F14:H14) into cell F2 and calculate the formula based on
    that month. E2 is the cell in the criteria range where you would
    type the name of a specific type of soda. Since you want to
    calculate the expense for each type of soda , leave E2 blank in
    the actual criteria. If, for example, you wanted to calculate the
    expense for your diet sodas, you would place the word diet in cell
    E2. The table will automatically (internally) substitute each soda
    type that you have listed in the table (E15:E22) into cell E2 and
    calculate the formula based on that type.


Two-input table with data from database (with formulas displayed):

   NOTE:  Due to character-based screen display limitations, the
   following 4-column table is  shown in two parts.

(Left  column of a 4 column table)

      E
 --|--------------------------------------------
 14|  =DSUM(Database,"Consumed",Criteria)*0.45
 15|  Pepup
 16|  Diet Pepup
 17|  Colo
 18|  Diet Colo
 19|  Splash
 20|  Diet Splash
 21|  Lime-Up
 22|  Diet Lime-Up
 
(Right 3 columns of a 4 column table)

       F                    G                H
 -----------------------------------------------------
 January              February       March
 =TABLE(F2,E2)        =TABLE(F2,E2)  =TABLE(F2,E2)
 =TABLE(F2,E2)        =TABLE(F2,E2)  =TABLE(F2,E2)
 =TABLE(F2,E2)        =TABLE(F2,E2)  =TABLE(F2,E2)
 =TABLE(F2,E2)        =TABLE(F2,E2)  =TABLE(F2,E2)
 =TABLE(F2,E2)        =TABLE(F2,E2)  =TABLE(F2,E2)
 =TABLE(F2,E2)        =TABLE(F2,E2)  =TABLE(F2,E2)
 =TABLE(F2,E2)        =TABLE(F2,E2)  =TABLE(F2,E2)
 =TABLE(F2,E2)        =TABLE(F2,E2)  =TABLE(F2,E2)

 Two-input table with data from database (with values displayed):

          E               F           G           H
 --|--------------------------------------------------
 14|  Cost per Month  January     February     March
 15|  Pepup           $425.70     $409.50      $378.00
 16|  Diet Pepup      $342.90     $402.30      $198.90
 17|  Colo            $100.80     $416.70      $184.05
 18|  Diet Colo       $0.45       $211.95      $92.25
 19|  Splash          $321.75     $221.85      $49.05
 20|  Diet Splash     $227.70     $124.20      $118.35
 21|  Lime-Up         $159.30     $20.25       $271.35
 22|  Diet Lime-Up    $243.90     $135.45      $249.75

The value displayed in cell E14 is a number format. To duplicate this
value, do the following:

1. Select cell E14.

2. From the Format menu, choose Number.

3. In the Code box (the Format box in versions 2.x), type "Cost Per
   Month" (without the quotation marks).

4. Choose OK.



WITH COMPUTED CRITERIA
======================

You can also use computed criteria in one-input and two-input tables
to obtain and analyze values from a database. Computed criteria uses a
formula to extract or obtain values for analysis.

When you use computed criteria, be aware of the following:

 - The field name of the computed criteria must be a label other than
   a field name used in the database (or it can be left blank). In our
   example, we have left cell H1 blank; it could contain the word
   "month"or "formula" or any other text string as long as it is not
   the name of a field in your database.

 - In the formula that uses the computed criteria, you must use a
   relative reference to the first record in the field of the database
   that you want to reference. In the following example, the formula
   contains a relative reference to cell B2 in the formula
   =MONTH(B2)=MONTH($H$3).

 - In most cases, any other references in the computed criteria must
   be absolute. In the following example, the formula contains an
   absolute reference to cell H3 in the formula
   =MONTH(B2)=MONTH($H$3).

For the following examples, you will need to create a sample database
and a sample criteria range.

To create a sample database, type the following information in cells
A1:C15 of a new worksheet. Select cells A1:C15, and choose Set
Database from the Data menu.

      A           B           C
 --|---------------------------------
 1 | Product #  DATE       AMOUNT SOLD
 2 | 9865       1/2/90     91
 3 | 9870       1/12/90    94
 4 | 9875       1/22/90    76
 5 | 9880       2/1/90     22
 6 | 9865       2/11/90    82
 7 | 9870       2/21/90    71
 8 | 9870       3/3/90     50
 9 | 9865       3/13/90    35
 10| 9880       3/23/90    54
 11| 9875       4/2/90     80
 12| 9865       4/12/90    33
 13| 9880       4/22/90    83
 14| 9875       5/2/90     62
 15| 9870       5/12/90    15

To create a sample criteria range, type the following data in cells
E1:H2 of the worksheet. Select cells E1:H2, and from the Data menu,
choose Set Criteria. Select cells E1:H2, and choose Set Criteria from
the Data menu.

        E       F         G                   H
 -|------------------------------------------------------
 1| PRODUCT #  DATE   AMOUNT SOLD
 2|                                =MONTH(B2)=MONTH($H$3)

The formula =MONTH(B2)=MONTH($H$3) will return a value of either TRUE
or FALSE, which will be displayed in H2:

        E       F         G         H
 -|-------------------------------------
 1| PRODUCT #  DATE   AMOUNT SOLD
 2|                                TRUE



In a One-Input Table
--------------------

If you want to find how many items were sold each month, how many days
a sale was made, and the maximum number of items sold on one day in
each month, you can create a one-input table from this data, as
follows:

1. Type 1/1/90 in cell E6, 2/1/90 in cell E7, 3/1/90 in cell E8,
   4/1/90 in cell E9, and 5/1/90 in cell E10.
    
    NOTE: If you want only the name of the month to be displayed in
    the table (as in the following example), change the number format
    of cells E6:E10, by choosing Number from the Format menu and
    typing  mmmm in the Code box (the Format box in versions 2.x).
    With this format, E6 will be displayed as January, E7 will be
    displayed as February, and so on.

2. In cell F5, type the formula:

 =DSUM(Database,"Amount Sold",Criteria)

3. In cell G5, type the formula:

 =DCOUNT(Database,,Criteria)

4. In cell H5, type the formula:

 =DMAX(Database,"Amount Sold",Criteria)

5. Select cells E5:H10.

6. From the Data menu, choose Table.

7. In the Column Input Cell box, type "H3" (without the quotation
   marks).
    
    NOTE: Cell H2 contains the formula =MONTH(B2)=MONTH($H$3). This
    formula checks to see if the month in the first record of the Date
    field (B2) equals the month of cell H3 (cell H3 is the column
    input cell). The table will automatically (internally) substitute
    each month listed in the table (E5:E 10) into cell H3 and
    calculate the formulas based on that month.


One-input table with computed criteria (with formulas displayed):

   NOTE:  Due to character-based screen display limitations, the
   following 4-column table is  shown in two parts.

(Left 2 columns of a 4 column table)

    E       F
 --|-----------------------------------------------
 5 |         =DSUM(Database,"Amount Sold",Criteria)
 6 | 31412   =TABLE(,H3)
 7 | 31443   =TABLE(,H3)
 8 | 31471   =TABLE(,H3)
 9 | 31502   =TABLE(,H3)
 10| 31532   =TABLE(,H3)

(Right 2 columns of a 4 column table)

 G                            H
 -------------------------------------------------------------------
 =DCOUNT(Database,,Criteria)  =DMAX(Database,"Amount Sold",Criteria)
 =TABLE(,H3)                  =TABLE(,H3)
 =TABLE(,H3)                  =TABLE(,H3)
 =TABLE(,H3)                  =TABLE(,H3)
 =TABLE(,H3)                  =TABLE(,H3)
 =TABLE(,H3)                  =TABLE(,H3)

One-input table with computed criteria (with values displayed):

        E           F                 G            H
 --|------------------------------------------------------
 5 |            Total Amount      # of Entries   Max Entry
 6 | January    261               3              94
 7 | February   175               3              82
 8 | March      139               3              54
 9 | April      196               3              83
 10| May        77                2              62


 The values displayed in cells F5:H5 are number formats. To duplicate
these values, do the following:

1. Select cell F5.

2. From the Format menu, choose Number.

3. In the Code box (the Format box in versions 2.x), type "TOTAL
   AMOUNT" (without the quotation marks).

4. Choose OK.

5. Repeat with cells G5 and H5, entering the formats "# of Entries"
   and Max Entry, respectively. (Note that you must include the
   quotation marks with the first entry.)


In a Two-Input Table
--------------------

If you want to find how many items were sold each month for each
product number, you can create a two-input table from this data, as
follows:

1. Type 1/1/90 in cell E13, 2/1/90 in cell E14, 3/1/90 in cell E15,
   4/1/90 in cell E16, and 5/1/90 in cell E17.
    
    NOTE: If you want only the name of the month to be displayed in
    the table (as in the following example), change the number format
    of cells E13:E17, by choosing Number from the Format menu and
    typing mmmm in the Code box (the Format box in versions 2.x). With
    this format, E13 will be displayed as January, E14 will be
    displayed as February, and so on.

2. Type the product number 9865 in cell F12, 9870 in cell G12, 9875 in
   cell H12, and 9880 in cell I12.

3. In cell E12, type the formula:

 =DSUM(Database,"Amount Sold",Criteria)

4. Select cells E12:I17.

5. From the Data menu, choose Table.

6. In the Row Input Cell box, type "E2" (without the quotation marks),
   and in the Column Input Cell box, type" H3" (without the quotation
   marks).
    
    NOTE: E2 is the cell in the criteria range where you would type a
    specific product number. Since you want the total number of each
    product sold broken down by each month, leave E2 blank in the
    defined criteria range. The table will automatically (internally)
    substitute each product number listed in the table (F12:I12) into
    cell E2 and calculate the formula based on that product. Cell H2
    contains the formula =MONTH(B2)=MONTH($H$3). This formula checks
    to see if the month in the first record of the Date field (B2)
    equals the month of cell H3, which is the column input cell.
    Remember, the table will automatically (internally) substitute
    each month listed in the table (E13:E17) into cell H3 and
    calculate the formulas based on that month.


Two-input table with computed criteria (with formulas displayed):

   NOTE:  Due to character-based screen display limitations, the
   following 5-column table is  shown in two parts.

(Left 2 columns of a 5 column table)

    E                                       F
--|-------------------------------------------------------
12|  =DSUM(Database,"Amount Sold",Criteria)  9865
13|  31412                                   =TABLE(E2,H3)
14|  31443                                   =TABLE(E2,H3)
15|  31471                                   =TABLE(E2,H3)
16|  31502                                   =TABLE(E2,H3)
17|  31532                                   =TABLE(E2,H3)

(Right 3 columns of a 5 column table)

G                H               I
----------------------------------------------
9870             9875            9880
=TABLE(E2,H3)    =TABLE(E2,H3)   =TABLE(E2,H3)
=TABLE(E2,H3)    =TABLE(E2,H3)   =TABLE(E2,H3)
=TABLE(E2,H3)    =TABLE(E2,H3)   =TABLE(E2,H3)
=TABLE(E2,H3)    =TABLE(E2,H3)   =TABLE(E2,H3)
=TABLE(E2,H3)    =TABLE(E2,H3)   =TABLE(E2,H3)


Two-input table with computed criteria (with values displayed):

     E        F     G      H       I
--|------------------------------------
12|          9865   9870   9875    9880
13| January  91     94     76      0
14| February 82     71     0       22
15| March    35     50     0       54
16| April    33     0      80      83
17| May      0      15     62      0