XL: AppNote XE0210: Creating and Using TablesID: Q99181
|
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.
This document is also available in Microsoft Word format. This file is
available for download from the Microsoft Software Library:
~ XE0210.exe
For more information about downloading files from the Microsoft Software
Library, please see the following article in the Microsoft Knowledge Base:
Q119591 : How to Obtain Microsoft Support Files from Online Services
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 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 Last Reviewed: April 5, 1999