Microsoft KB Archive/99181

= AppNote XE0210: Creating and Using Tables =

Article ID: 99181

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q99181



SUMMARY
The Application Note &quot;Creating and Using Tables&quot; (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.

This document is also available in Microsoft Word format. The following file is available for download from the Microsoft Download Center:

Xe0210.exe

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.



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 &quot;Item Cost&quot; (with the quotation marks).

6. Click OK.

7. Repeat the above procedure for cell B2, entering the format &quot;Tax&quot; (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 &quot;#+2&quot; (with the  quotation marks).

4. Click OK.

5. Repeat the above procedure with cells D2 and E2, entering the formats &quot;#*2&quot; and &quot;INT(#/2)&quot;, 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 &quot;#+2&quot; (with the quotation marks).

6. Click OK.

7. Repeat the above procedure with cells D2 and E2, entering the formats &quot;#*2&quot; and &quot;INT(#/2)&quot;, 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 &quot;Tax&quot; (with the quotation marks).

6. Click OK.

7. Repeat the above procedure with cell B11, entering the format &quot;Total&quot; (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 &quot;&quot; (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 &quot;&quot; (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,&quot;Consumed&quot;,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,&quot;Consumed&quot;,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 &quot;Money Spent on Beverages&quot; (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,&quot;Consumed&quot;,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 &quot;diet&quot; 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,&quot;Consumed&quot;,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 &quot;Cost per Month&quot; (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 &quot;month&quot; or  &quot;formula&quot; 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,&quot;Amount Sold&quot;,Criteria)

3. In cell G5, type the formula:

=DCOUNT(Database,,Criteria)

4. In cell H5, type the formula:

=DMAX(Database,&quot;Amount Sold&quot;,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,&quot;Amount Sold&quot;,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,&quot;Amount Sold&quot;,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 &quot;# of Entries&quot; 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 &quot;Total Amount&quot; (with the quotation  marks).

6. Click OK.

7. Repeat with cells G5 and H5, entering the formats &quot;# of Entries&quot; and &quot;Max Entry&quot;, 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,&quot;Amount Sold&quot;,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,&quot;Amount Sold&quot;,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

Additional query words: xe0210 exe XL

Keywords: kbinfo kbdownload kbappnote KB99181

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.