XL: AppNote XE0210: Creating and Using Tables

 Last reviewed: February 2, 1998
Article ID: Q99181

The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel for Windows, versions 2.0, 3.0, 4.0, 5.0 and 5.0a
  • Microsoft Excel for the Macintosh, versions 1.0, 2.2, 3.0, 4.0, 5.0 and 5.0a
  • Microsoft Excel for Windows 95, versions 7.0 and 7.0a
  • Microsoft Excel 97 for Windows

SUMMARY

The Application Note "Creating and Using Tables" (XE0210) is now available. 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.

You can obtain this Application Note from the following sources:

  • Microsoft's World Wide Web Site on the Internet
  • The Internet (Microsoft anonymous ftp server)
  • Microsoft Download Service (MSDL)
  • Microsoft Technical Support

For complete information, see the "To Obtain This Application Note" section at the end of this article.

MORE INFORMATION

The full text is contained in this article. 

  Microsoft(R) Product Support Services Application Note (Text File)
                   XE0210: CREATING AND USING TABLES

                                              Revision Date: 4/97
                                                          No Disk
The information in this Application Note applies to:
  • Microsoft Excel for Windows, versions 2.0, 3.0, 4.0, 5.0 and 5.0a
  • Microsoft Excel for the Macintosh, versions 1.0, 2.2, 3.0, 4.0, 5.0 and 5.0a
  • Microsoft Excel for Windows 95, versions 7.0 and 7.0a
  • Microsoft Excel 97 for Windows

This Application Note describes how to create one-input and two-input data tables by 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 recreating the formula each time that 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 on page 2.

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 these 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 the Table command (with formulas displayed): 

      |     A     |     B
   ---|-----------|------------
    1 | Item Cost | =C1*8.1%    <--- The formula is typed 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 the 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.

In Microsoft Excel 4.0 and earlier

  1. Select cell A1.

  2. On the Format menu, click Number.

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

  4. Click OK.

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

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cell A1.

  2. On the Format menu, click Cells.

  3. Switch to the Number tab.

  4. Under Category, select Custom.

  5. In the Code (or Type) box, type "Item Cost" (with the quotation marks).

  6. Click OK.

  7. Repeat the above procedure for cell B2, entering the format "Tax" (with
the 
   quotation marks).

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.

    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 because B1 is outside the table, it is a valid column input cell.

  3. In cell D2, type the formula =B1*2.

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

  5. Select the range of cells B2:E6.

  6. On the Data menu, click Table.

  7. In the Column Input Cell box, type B1. Leave the Row Input Cell box blank, because this is a oneinput 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:

In Microsoft Excel 4.0 and earlier

  1. Select cell C2.

  2. On the Format menu, click Number.

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

  4. Click OK.

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

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cell C2.

  2. On the Format menu, click Cells.

  3. Switch to the Number tab.

  4. Under Category, select Custom.

  5. In the Code (or Type) box , type "#+2" (with the quotation marks).

  6. Click OK.

  7. Repeat the above procedure with cells D2 and E2, entering the formats "#*2" and "INT(#/2)", respectively. 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%.

    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 because A10 is outside the table, it is a valid row input cell.

  3. In cell B11, type the formula =A10+A10*7.8%.

  4. Select cells B9:F11.

  5. On the Data menu, click Table.

  6. In the Row Input Cell box, type A10. Leave the Column Input Cell box blank, because 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.

In Microsoft Excel 4.0 and earlier

  1. Select cell B10.

  2. On the Format menu, click Number.

  3. In the Code box (the Format box in versions 2.x), type Tax.

  4. Click OK.

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

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cell B10.

  2. On the Format menu, click Cells.

  3. Switch to the Number tab.

  4. Under Category, select Custom.

  5. In the Code (or Type) box, type "Tax" (with the quotation marks).

  6. Click OK.

  7. Repeat the above procedure with cell B11, entering the format "Total" (with the quotation marks).

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.

    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. Because this table is set up in cells B14:G19, and because A14 and A15 are outside the table, they are valid column and row input cells.

  4. Select B14:G19.

  5. On the Data menu, click Table.

  6. In the Row Input Cell box, type A15, and in the Column Input Cell box, type A14.

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
   ---|----------------|---------------|---------------
    14|              8 |              9|             10
    15| =TABLE(A15,A14)|=TABLE(A15,A14)|=TABLE(A15,A14)
    16| =TABLE(A15,A14)|=TABLE(A15,A14)|=TABLE(A15,A14)
    17| =TABLE(A15,A14)|=TABLE(A15,A14)|=TABLE(A15,A14)
    18| =TABLE(A15,A14)|=TABLE(A15,A14)|=TABLE(A15,A14)
    19| =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.

In Microsoft Excel 4.0 and earlier

  1. Select cell B14.

  2. On the Format menu, click Number.

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

  4. Click OK.

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cell B14.

  2. On the Format menu, click Cells.

  3. Switch to the Number tab.

  4. Under Category, select Custom.

  5. In the Code (or Type) box, type "" (that is, two quotation marks).

  6. Click 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 do the following:

In Microsoft Excel 4.0 and earlier

  1. Select cells A1:C25.

  2. On the Data menu, click Set Database.

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cells A1:C25.

  2. On the Insert menu, point to Name, and then click Define. 3. Type Database, and then click OK. 

          |       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. Then use the correct procedure below. 

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

In Microsoft Excel 4.0 and earlier

  1. Select cells E1:G2.

  2. On the Data menu, click Set Criteria.

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cells E1:G2.

  2. On the Insert menu, point to Name, and then click Define.

  3. Type Criteria, and then click OK.

In a One-Input Table

To find the cost of soda consumed per type 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. On the Data menu, click Table.

  5. In the Column Input Cell box, type E2.

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

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.

In Microsoft Excel 4.0 and earlier

  1. Select cell F4.

  2. On the Format menu, click Number.

  3. In the Code box (the Format box in versions 2.x), type Money Spent on Beverages.

  4. Click OK.

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cell F4.

  2. On the Format menu, click Cells.

  3. Switch to the Number tab.

  4. Under Category, select Custom.

  5. In the Code (or Type) box, type "Money Spent on Beverages" (with the quotation marks).

  6. Click OK.

In a Two-Input Table

For the following example, use the sample database and criteria that you created on page 2.

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 in cell F14, February in cell G14, and March 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. On the Data menu, click Table.

  6. In the Row Input Cell box, type F2. In the Column Input Cell box, type E2.

    NOTE: F2 is the cell in the criteria range where you would type the name of a specific month. Because 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. Because 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
   ---|---------------|--------------|-------------
    14| January       |February      |March
    15| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    16| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    17| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    18| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    19| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    20| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    21| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    22| =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.

In Microsoft Excel 4.0 and earlier

  1. Select cell E14.

  2. On the Format menu, click Number.

  3. In the Code box (the Format box in versions 2.x), type Cost per Month.

  4. Click OK.

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cell E14.

  2. On the Format menu, click Cells.

  3. Switch to the Number tab.

  4. Under Category, select Custom.

  5. In the Code (or Type) box, type "Cost per Month" (with the quotation marks).

  6. Click OK.

With Computed Criteria

You can also use computed criteria in one-input and twoinput 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 the example, cell H1 is left 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. Then use the correct procedure below. 

      |    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

In Microsoft Excel 4.0 and earlier

  1. Select cells A1:C15.

  2. On the Data menu, click Set Database.

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cells A1:C15.

  2. On the Insert menu, point to Name, and then click Define. 3. Type Database, and then click OK.

Then use the correct procedure below to set a Criteria.

In Microsoft Excel 4.0 and earlier

  1. Select cells E1:H2.

  2. On the Data menu, click Set Criteria.

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cells E1:H2.

  2. On the Insert menu, point to Name, and then click Define. 3. Type Criteria, and then click OK. 

          |     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, 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 clicking Number on 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. On the Data menu, click Table.

  7. In the Column Input Cell box, type H3.

    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:E10) 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
   ---|-----------------------------|-------------------------------------
    5 | =DCOUNT(Database,,Criteria) |=DMAX(Database,"Amount Sold",Criteria)
    6 | =TABLE(,H3)                 |=TABLE(,H3)
    7 | =TABLE(,H3)                 |=TABLE(,H3)
    8 | =TABLE(,H3)                 |=TABLE(,H3)
    9 | =TABLE(,H3)                 |=TABLE(,H3)
    10| =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.

In Microsoft Excel 4.0 and earlier

  1. Select cell F5.

  2. On the Format menu, click Number.

  3. In the Code box (the Format box in versions 2.x), type Total Amount.

  4. Click OK.

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

In Microsoft Excel 5.0, 7.0, and 97

  1. Select cell F5.

  2. On the Format menu, click Cells.

  3. Switch to the Number tab.

  4. Under Category, select Custom.

  5. In the Code (or Type) box, type "Total Amount" (with the quotation marks).

  6. Click OK.

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

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 clicking Number on 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. On the Data menu, click Table.

  6. In the Row Input Cell box, type E2, and in the Column Input Cell box, type H3.

    NOTE: E2 is the cell in the criteria range where you would type a specific product number. Because 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
   ---|---------------|--------------|-------------
    12|           9870|          9875|         9880
    13| =TABLE(E2,H3) |=TABLE(E2,H3) |=TABLE(E2,H3)
    14| =TABLE(E2,H3) |=TABLE(E2,H3) |=TABLE(E2,H3)
    15| =TABLE(E2,H3) |=TABLE(E2,H3) |=TABLE(E2,H3)
    16| =TABLE(E2,H3) |=TABLE(E2,H3) |=TABLE(E2,H3)
    17| =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

TO OBTAIN THIS APPLICATION NOTE

The following files are available for download from the Microsoft Software Library: 

 ~ Xe0210.exe (size: 59639 bytes)
For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from Online Services
- Application Notes are available by modem from the Microsoft Download 
   Service (MSDL), which you can reach by calling (425) 936-6735. This
   service is available 24 hours a day, 7 days a week. The highest download
   speed available is 9600 bits per second (bps). For more information
   about using the MSDL, call (800) 936-4100 and follow the prompts. To
   obtain Xe0210, download Xe0210.exe. Xe0210.exe is a compressed, self-
   extracting file. After you download Xe0210, run it to extract the file
   it contains.
  • If you are unable to access the source listed above, you can have this Application Note mailed or faxed to you by calling Microsoft Product Support Services Monday through Friday, 6:00 A.M. to 6:00 P.M. Pacific time at (425) 635-7070 (Windows) or (425) 635-7080 (Macintosh). If you are outside the United States, contact the Microsoft subsidiary for your area. To locate your subsidiary, see the Microsoft World Wide Offices Web site at: 

          http://www.microsoft.com/worldwide/default.htm

    • Additional query words:
    Keywords : kbappnote
    Version : WINDOWS:1.0,2.0,3.0,4.0,5.0,7.0,97; MACINTOSH:1.0,2.0,3.0,4.0,5.0,98
    Platform : MACINTOSH WINDOWS
    Issue type : kbinfo

    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: February 2, 1998
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.