home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Monster Media 1993 #2
/
Image.iso
/
spread
/
xe0210.zip
/
XE0210.TXT
Wrap
Text File
|
1993-05-18
|
31KB
|
866 lines
======================================================================
Microsoft(R) Product Support Services Application Note (Text File)
XE0210: CREATING AND USING TABLES
======================================================================
Revision Date: 5/93
No Disk
The following information applies to Microsoft Excel, version See
Below.
--------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an |
| Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY |
| KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO |
| THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A |
| PARTICULAR PURPOSE. The user assumes the entire risk as to the |
| accuracy and the use of this Application Note. This Application |
| Note may be copied and distributed subject to the following |
| conditions: 1) All text must be copied without modification and |
| all pages must be included; 2) If software is included, all files |
| on the disk(s) must be copied without modification [the MS-DOS(R) |
| utility DISKCOPY is appropriate for this purpose]; 3) All |
| components of this Application Note must be distributed together; |
| and 4) This Application Note may not be distributed for profit. |
| |
| Copyright 1989-1993 Microsoft Corporation. All Rights Reserved. |
| Microsoft and MS-DOS are registered trademarks and Windows |
| is a trademark of Microsoft Corporation. |
--------------------------------------------------------------------
The information in this Application Note applies to:
- Microsoft Excel versions 2.0, 2.01, 2.1, 2.1c, 2.1d, 3.0, 4.0, and
4.0a for Windows
- Microsoft Excel versions 1.0, 1.03, 1.04, 1.06, 1.5, 2.2, 3.0, and
4.0 for the Macintosh(R)
This Application Note describes how to create one-input and two-input
data tables using the Table command on the Data menu. It also
discusses how you can use one-input and two-input tables to evaluate
database information obtained using database functions and comparison
or computed criteria.
OVERVIEW OF TABLES
==================
When you create a formula, you may want to see the results of that
formula with various values. Rather than re-creating the formula each
time you want to test a new value, use the Table command on the Data
menu to create a table. With the Table command, you can test a formula
with different values without having to retype or copy the formula for
each value you want to test.
For example, the formula "=itemcost *8.1%" will calculate tax on an
item based on the item's cost (itemcost). If there are several items
that you want to calculate tax for, you could type the formula in for
each item, as shown in the example below.
Table created manually (with formulas displayed):
| A B
--|---------------------
1 | Item Cost Tax
2 | 15 =A2*8.1%
3 | 17.5 =A3*8.1%
4 | 22.35 =A4*8.1%
Table created manually (with values displayed):
A B
-|-----------------
1| Item Cost Tax
2| $15.00 $1.22
3| $17.50 $1.42
4| $22.35 $1.81
To create the tables in the previous examples, you have to type or
copy the formula for each value you want to evaluate. However, if you
create a table with the Table command, you only have to type the
formula once.
Table created with Table command (with formulas displayed):
A B
--|---------------------
1 | Item Cost =C1*8.1% <- The formula is typed in once.
2 | 15 =TABLE(,C1) <- The Table command puts the results here.
3 | 17.5 =TABLE(,C1) <-
4 | 22.35 =TABLE(,C1) <-
C1 represents a variable. The values in cells A2:A4 are substituted
for C1 and the corresponding result is placed in cells B2:B4.
Table created with Table command (with values displayed):
A B
-|-----------------
1| Item Cost Tax
2| $15.00 $1.22
3| $17.50 $1.42
4| $22.35 $1.81
The values displayed in cells A1 and B1 are number formats. To
duplicate these values, do the following:
1. Select cell A1.
2. From the Format menu, choose Number.
3. In the Code box (the Format box in versions 2.x), type "Item Cost"
(without the quotation marks).
4. Choose OK.
5. Repeat the above procedure for cell B2, entering the format Tax.
CREATING A ONE-INPUT TABLE
==========================
A one-input table allows you to test for changes in a formula based on
one variable. One-input tables can be organized in two ways: column
input or row input.
Entering the Input Values in a Column
-------------------------------------
Column input tables are organized with the values listed in a vertical
array and the formulas listed horizontally.
To create a simple column input table, do the following:
1. In cells B3:B6, type 10, 13, 14, and 19.
NOTE: These values are the variables that will be substituted into
the formulas.
2. In cell C2, type the formula "=B1+2" (without the quotation marks).
NOTE: In this formula, B1 is the column input cell. This column
input cell represents the variable value in the formula; this cell
must be located outside the table (it may or may not contain
data). This table is set up in cells B2:E6, and since B1 is
outside the table, it is a valid column input cell.
3. In cell D2, type the formula "=B1*2" (without the quotation marks).
4. In cell E2, type the formula "=INT(B1/2)" (without the quotation
marks).
5. Select the range of cells B2:E6.
6. From the Data menu, choose Table.
7. In the Column Input Cell box, type "B1" (without the quotation
marks<). Leave the Row Input Cell box blank, since this is a one-
input table.
One-input table with input values in a column (with formulas
displayed):
A B C D E
-|---------------------------------------------
1|
2| =B1+2 =B1*2 =INT(B1/2)
3| 10 =TABLE(,B1) =TABLE(,B1) =TABLE(,B1)
4| 13 =TABLE(,B1) =TABLE(,B1) =TABLE(,B1)
5| 14 =TABLE(,B1) =TABLE(,B1) =TABLE(,B1)
6| 19 =TABLE(,B1) =TABLE(,B1) =TABLE(,B1)
One-input table with input values in a column (with values displayed):
A B C D E
-|-----------------------------
1|
2| #+2 #*2 INT(#≈2)
3| 10 12 20 5
4| 13 15 26 6
5| 14 16 28 7
6| 19 21 38 9
The values displayed in cells C2, D2, and E2 are number formats. To
duplicate these values, do the following:
1. Select cell C2.
2. From the Format menu, choose Number.
3. In the Code box (the Format box in versions 2.x), type "#+2" (you
must include the quotation marks).
4. Choose OK.
5. Repeat the above procedure with cells D2 and E2, entering the
formats "#*2" and "INT(#/2)", respectively. As in step 3, you must
enclose these entries in quotation marks.
Entering the Input Values in a Row
----------------------------------
Row input tables are organized with the variable values listed in a
horizontal array and the formulas listed vertically.
To create a simple row input table, do the following:
1. In cells C9:F9, type 19.95, 20.98, 13.50, and 10.
NOTE: These values are the variables that will be substituted into
the formulas.
2. In cell B10, type the formula "=A10*7.8%" (without the quotation
marks).
NOTE: In this formula, A10 is the row input cell. The row input
cell represents the variable value in the formula and must be
located in a cell outside the table; this cell may or may not
contain data. This table is set up in cells B9:F11, and since A10
is outside the table, it is a valid row input cell.
3. In cell B11, type the formula "=A10+A10*7.8%" (without the
quotation marks).
4. Select cells B9:F11.
5. From the Data menu, choose Table.
6. In the Row Input Cell box, type "A10" (without the quotation
marks). Leave the Column Input Cell box blank, since this is a one-
input table.
One-input table with row input cell (with formulas displayed):
A B C D E F
--|----------------------------------------------------------------------
|
9 | 19.95 20.98 13.5 10
10| =A10*7.8% =TABLE(A10,) =TABLE(A10,) =TABLE(A10,) =TABLE(A10,)
11| =A10+A10*7.8% =TABLE(A10,) =TABLE(A10,) =TABLE(A10,) =TABLE(A10,)
One-input table with row input cell (with values displayed):
A B C D E F
--|------------------------------------------
|
9 | $19.95 $20.98 $13.50 $10.00
10| Tax $1.56 $1.64 $1.05 $0.78
11| Total $21.51 $22.62 $14.55 $10.78
The values displayed in cells B10 and B11 are number formats. To
duplicate these values, do the following:
1. Select cell B10.
2. From the Format menu, choose Number.
3. In the Code box (the Format box in versions 2.x), type "TAX"
(without the quotation marks).
4. Choose OK.
5. Repeat the above procedure with cell B11, entering the format
Total.
CREATING A TWO-INPUT TABLE
==========================
A two-input table allows you to test how changes in two variables
affect one formula. When you create a two-input table, you specify
input cells for the Row Input Cell box and for the Column Input Cell
box in the Tables dialog box.
To create a simple two-input table, do the following:
1. In cells B15:B19, type 1, 2, 3, 4, and 5.
2. In cells C14:G14, type 6, 7, 8, 9, and 10.
3. In cell B14, type the formula "=A14*2+A15" (without the quotation
marks).
NOTE: In this formula, A14 is the column input cell (which will
substitute values 1, 2, 3, 4, and 5) and A15 is the row input cell
(which will substitute values 6, 7, 8, 9, and 10). These input
cells must be located outside the table; they may or may not
contain data. Since this table is set up in cells B14:G19, and
since A14 and A15 are outside the table, they are valid column and
row input cells.
4. Select B14:G19.
5. From the Data menu, choose Table.
6. In the Row Input Cell box, type "A15" (without the quotation
marks), and in the Column Input Cell box, type "A14" (without the
quotation marks).
Two-input table (with formulas displayed):
NOTE: Due to character-based screen display limitations, the
following 6-column table is shown in two parts.
(Left 3 columns of a 6 column table)
B C D
- |-----------------------------------------------
14| =A14*2+A15 6 7
15| 1 =TABLE(A15,A14) =TABLE(A15,A14)
16| 2 =TABLE(A15,A14) =TABLE(A15,A14)
17| 3 =TABLE(A15,A14) =TABLE(A15,A14)
18| 4 =TABLE(A15,A14) =TABLE(A15,A14)
19| 5 =TABLE(A15,A14) =TABLE(A15,A14)
(Right 3 columns of a 6 column table)
E F G
-------------------------------------------------
8 9 10
=TABLE(A15,A14) =TABLE(A15,A14) =TABLE(A15,A14)
=TABLE(A15,A14) =TABLE(A15,A14) =TABLE(A15,A14)
=TABLE(A15,A14) =TABLE(A15,A14) =TABLE(A15,A14)
=TABLE(A15,A14) =TABLE(A15,A14) =TABLE(A15,A14)
=TABLE(A15,A14) =TABLE(A15,A14) =TABLE(A15,A14)
Two-input table (with values displayed):
B C D E F G
--|---------------------------
14| 6 7 8 9 10
15| 1 8 9 10 11 12
16| 2 10 11 12 13 14
17| 3 12 13 14 15 16
18| 4 14 15 16 17 18
19| 5 16 17 18 19 20
Note that the result in cell C15 is 1*2+6, which equals 8. The values
in cells B15:B19 are internally substituted into the column input cell
(A14), and the values in cells C14:G14 are internally substituted into
the row input cell (A15).
The blank value in cell B14 is a number format. To duplicate this
value, do the following:
1. Select cell B14.
2. From the Format menu, choose Number.
3. In the Code box (the Format box in versions 2.x), type "" (that is,
two quotation marks).
4. Choose OK.
USING TABLES TO ANALYZE INFORMATION IN A DATABASE
==================================================
You can use database functions in one-input and two-input tables to
analyze values obtained from a database using both comparison and
computed criteria.
WITH COMPARISON CRITERIA
========================
Comparison criteria is the type of criteria most commonly used to
extract or analyze information from a Microsoft Excel database. The
value you place under the column heading in your criteria range is
compared against the records in your database. If a record matches
that value, it is extracted or included in the group of records to be
analyzed using the database functions. For the following two examples,
you will need to create a sample database and a sample criteria range.
To create a sample database, type the following information in cells
A1:C25 of a new worksheet. Then select cells A1:C25 and choose Set
Database from the Data menu.
A B C
--|--------------------------------------
1 | Type of Soda Month Consumed
2 | Pepup January 946
3 | Diet Pepup January 762
4 | Colo January 224
5 | Diet Colo January 1
6 | Splash January 715
7 | Diet Splash January 506
8 | Lime-Up January 354
9 | Diet Lime-Up January 542
10| Pepup February 910
11| Diet Pepup February 894
12| Colo February 926
13| Diet Colo February 471
14| Splash February 493
15| Diet Splash February 276
16| Lime-Up February 45
17| Diet Lime-Up February 301
18| Pepup March 840
19| Diet Pepup March 442
20| Colo March 409
21| Diet Colo March 205
22| Splash March 109
23| Diet Splash March 263
24| Lime-Up March 603
25| Diet Lime-Up March 555
To create a sample criteria range, type the following data in cells
E1:G1 of the worksheet. Select E1:G2, and from the Data menu, choose
Set Criteria.
E F G
--|-------------------------------------
1 | Type of Soda Month Consumed
2 |
In a One-Input Table
--------------------
To find the cost of soda consumed per type in the entire period,
create a one-input table using the data from the database:
1. In cells E5:E12, type the different kinds of soda (because this
variable data is entered in a column, this will be a column input
table).
NOTE: You can copy the types from the database and paste them into
the cells.
2. In cell F4, type the formula:
=DSUM(Database,"Consumed",Criteria)*0.45
NOTE: This formula will add all the consumed sodas in the database
that match the specified criteria and multiply the result by 45
cents (the cost per can).
3. Select cells E4:F12.
4. From the Data menu, choose Table.
5. In the Column Input Cell box, type "E2" (without the quotation
marks).
NOTE: E2 is the cell in the criteria range where you would type
the name of a specific type of soda. Since you want to substitute
different types of soda to calculate the expense for each type, we
leave cell E2 blank in the actual criteria. The table will
automatically (internally) substitute each soda type that we have
listed in our table (E4:E12) into cell E2 and calculate the
formula based on that criterion.
One-input table with data from database (with formulas displayed):
E F
--|---------------------------------------------------------
4 | First Quarter =DSUM(Database,"Consumed",Criteria)*0.45
5 | Pepup =TABLE(,E2)
6 | Diet Pepup =TABLE(,E2)
7 | Colo =TABLE(,E2)
8 | Diet Colo =TABLE(,E2)
9 | Splash =TABLE(,E2)
10| Diet Splash =TABLE(,E2)
11| Lime-Up =TABLE(,E2)
12| Diet Lime-Up =TABLE(,E2)
One-input table with data from database (with values displayed):
E F
--|-----------------------------------------
4 | First Quarter Money Spent on Beverages
5 | Pepup $1,213.20
6 | Diet Pepup $944.10
7 | Colo $701.55
8 | Diet Colo $304.65
9 | Splash $592.65
10| Diet Splash $470.25
11| Lime-Up $450.90
12| Diet Lime-Up $629.10
The value displayed in cell F4 is a number format. To duplicate this
value, do the following:
1. Select cell F4.
2. From the Format menu, choose Number.
3. In the Code box (the Format box in versions 2.x), type "MONEY SPENT
ON BEVERAGES" (without the quotation marks).
4. Choose OK.
In a Two-Input Table
--------------------
For the following example, use the sample database and criteria that
you previously created.
To find the cost of soda consumed per type per month, create a two-input table, as follows:
1. In cells E15:E22, type the different types of soda. (This
represents the column input.)
NOTE: You can copy the types from the database and paste them into
the cells.
2. Type "January" (without the quotation marks) in cell F14,
"February" (without the quotation marks) in cell G14, and "MARCH"
(without the quotation marks) in cell H14.
3. In cell E14, type the formula:
=DSUM(Database,"Consumed",Criteria)*0.45
NOTE: This formula will add all the consumed sodas in the database
based on the criteria and multiply the total by 45 cents (cost per
can).
4. Select cells E14:H22.
5. From the Data menu, choose Table.
6. In the Row Input Cell box, type "F2" (without the quotation marks).
In the Column Input Cell box, type "E2" (without the quotation
marks).
NOTE: F2 is the cell in the criteria range where you would type
the name of a specific month. Since you want to calculate the
expenses for each type of soda for each month and do not want to
limit your expense analysis to one particular month, leave F2
blank in the defined criteria range. The table will automatically
(internally) substitute each month that you have listed in the
table (F14:H14) into cell F2 and calculate the formula based on
that month. E2 is the cell in the criteria range where you would
type the name of a specific type of soda. Since you want to
calculate the expense for each type of soda , leave E2 blank in
the actual criteria. If, for example, you wanted to calculate the
expense for your diet sodas, you would place the word diet in cell
E2. The table will automatically (internally) substitute each soda
type that you have listed in the table (E15:E22) into cell E2 and
calculate the formula based on that type.
Two-input table with data from database (with formulas displayed):
NOTE: Due to character-based screen display limitations, the
following 4-column table is shown in two parts.
(Left column of a 4 column table)
E
--|--------------------------------------------
14| =DSUM(Database,"Consumed",Criteria)*0.45
15| Pepup
16| Diet Pepup
17| Colo
18| Diet Colo
19| Splash
20| Diet Splash
21| Lime-Up
22| Diet Lime-Up
(Right 3 columns of a 4 column table)
F G H
-----------------------------------------------------
January February March
=TABLE(F2,E2) =TABLE(F2,E2) =TABLE(F2,E2)
=TABLE(F2,E2) =TABLE(F2,E2) =TABLE(F2,E2)
=TABLE(F2,E2) =TABLE(F2,E2) =TABLE(F2,E2)
=TABLE(F2,E2) =TABLE(F2,E2) =TABLE(F2,E2)
=TABLE(F2,E2) =TABLE(F2,E2) =TABLE(F2,E2)
=TABLE(F2,E2) =TABLE(F2,E2) =TABLE(F2,E2)
=TABLE(F2,E2) =TABLE(F2,E2) =TABLE(F2,E2)
=TABLE(F2,E2) =TABLE(F2,E2) =TABLE(F2,E2)
Two-input table with data from database (with values displayed):
E F G H
--|--------------------------------------------------
14| Cost per Month January February March
15| Pepup $425.70 $409.50 $378.00
16| Diet Pepup $342.90 $402.30 $198.90
17| Colo $100.80 $416.70 $184.05
18| Diet Colo $0.45 $211.95 $92.25
19| Splash $321.75 $221.85 $49.05
20| Diet Splash $227.70 $124.20 $118.35
21| Lime-Up $159.30 $20.25 $271.35
22| Diet Lime-Up $243.90 $135.45 $249.75
The value displayed in cell E14 is a number format. To duplicate this
value, do the following:
1. Select cell E14.
2. From the Format menu, choose Number.
3. In the Code box (the Format box in versions 2.x), type "Cost Per
Month" (without the quotation marks).
4. Choose OK.
WITH COMPUTED CRITERIA
======================
You can also use computed criteria in one-input and two-input tables
to obtain and analyze values from a database. Computed criteria uses a
formula to extract or obtain values for analysis.
When you use computed criteria, be aware of the following:
- The field name of the computed criteria must be a label other than
a field name used in the database (or it can be left blank). In our
example, we have left cell H1 blank; it could contain the word
"month"or "formula" or any other text string as long as it is not
the name of a field in your database.
- In the formula that uses the computed criteria, you must use a
relative reference to the first record in the field of the database
that you want to reference. In the following example, the formula
contains a relative reference to cell B2 in the formula
=MONTH(B2)=MONTH($H$3).
- In most cases, any other references in the computed criteria must
be absolute. In the following example, the formula contains an
absolute reference to cell H3 in the formula
=MONTH(B2)=MONTH($H$3).
For the following examples, you will need to create a sample database
and a sample criteria range.
To create a sample database, type the following information in cells
A1:C15 of a new worksheet. Select cells A1:C15, and choose Set
Database from the Data menu.
A B C
--|---------------------------------
1 | Product # DATE AMOUNT SOLD
2 | 9865 1/2/90 91
3 | 9870 1/12/90 94
4 | 9875 1/22/90 76
5 | 9880 2/1/90 22
6 | 9865 2/11/90 82
7 | 9870 2/21/90 71
8 | 9870 3/3/90 50
9 | 9865 3/13/90 35
10| 9880 3/23/90 54
11| 9875 4/2/90 80
12| 9865 4/12/90 33
13| 9880 4/22/90 83
14| 9875 5/2/90 62
15| 9870 5/12/90 15
To create a sample criteria range, type the following data in cells
E1:H2 of the worksheet. Select cells E1:H2, and from the Data menu,
choose Set Criteria. Select cells E1:H2, and choose Set Criteria from
the Data menu.
E F G H
-|------------------------------------------------------
1| PRODUCT # DATE AMOUNT SOLD
2| =MONTH(B2)=MONTH($H$3)
The formula =MONTH(B2)=MONTH($H$3) will return a value of either TRUE
or FALSE, which will be displayed in H2:
E F G H
-|-------------------------------------
1| PRODUCT # DATE AMOUNT SOLD
2| TRUE
In a One-Input Table
--------------------
If you want to find how many items were sold each month, how many days
a sale was made, and the maximum number of items sold on one day in
each month, you can create a one-input table from this data, as
follows:
1. Type 1/1/90 in cell E6, 2/1/90 in cell E7, 3/1/90 in cell E8,
4/1/90 in cell E9, and 5/1/90 in cell E10.
NOTE: If you want only the name of the month to be displayed in
the table (as in the following example), change the number format
of cells E6:E10, by choosing Number from the Format menu and
typing mmmm in the Code box (the Format box in versions 2.x).
With this format, E6 will be displayed as January, E7 will be
displayed as February, and so on.
2. In cell F5, type the formula:
=DSUM(Database,"Amount Sold",Criteria)
3. In cell G5, type the formula:
=DCOUNT(Database,,Criteria)
4. In cell H5, type the formula:
=DMAX(Database,"Amount Sold",Criteria)
5. Select cells E5:H10.
6. From the Data menu, choose Table.
7. In the Column Input Cell box, type "H3" (without the quotation
marks).
NOTE: Cell H2 contains the formula =MONTH(B2)=MONTH($H$3). This
formula checks to see if the month in the first record of the Date
field (B2) equals the month of cell H3 (cell H3 is the column
input cell). The table will automatically (internally) substitute
each month listed in the table (E5:E 10) into cell H3 and
calculate the formulas based on that month.
One-input table with computed criteria (with formulas displayed):
NOTE: Due to character-based screen display limitations, the
following 4-column table is shown in two parts.
(Left 2 columns of a 4 column table)
E F
--|-----------------------------------------------
5 | =DSUM(Database,"Amount Sold",Criteria)
6 | 31412 =TABLE(,H3)
7 | 31443 =TABLE(,H3)
8 | 31471 =TABLE(,H3)
9 | 31502 =TABLE(,H3)
10| 31532 =TABLE(,H3)
(Right 2 columns of a 4 column table)
G H
-------------------------------------------------------------------
=DCOUNT(Database,,Criteria) =DMAX(Database,"Amount Sold",Criteria)
=TABLE(,H3) =TABLE(,H3)
=TABLE(,H3) =TABLE(,H3)
=TABLE(,H3) =TABLE(,H3)
=TABLE(,H3) =TABLE(,H3)
=TABLE(,H3) =TABLE(,H3)
One-input table with computed criteria (with values displayed):
E F G H
--|------------------------------------------------------
5 | Total Amount # of Entries Max Entry
6 | January 261 3 94
7 | February 175 3 82
8 | March 139 3 54
9 | April 196 3 83
10| May 77 2 62
The values displayed in cells F5:H5 are number formats. To duplicate
these values, do the following:
1. Select cell F5.
2. From the Format menu, choose Number.
3. In the Code box (the Format box in versions 2.x), type "TOTAL
AMOUNT" (without the quotation marks).
4. Choose OK.
5. Repeat with cells G5 and H5, entering the formats "# of Entries"
and Max Entry, respectively. (Note that you must include the
quotation marks with the first entry.)
In a Two-Input Table
--------------------
If you want to find how many items were sold each month for each
product number, you can create a two-input table from this data, as
follows:
1. Type 1/1/90 in cell E13, 2/1/90 in cell E14, 3/1/90 in cell E15,
4/1/90 in cell E16, and 5/1/90 in cell E17.
NOTE: If you want only the name of the month to be displayed in
the table (as in the following example), change the number format
of cells E13:E17, by choosing Number from the Format menu and
typing mmmm in the Code box (the Format box in versions 2.x). With
this format, E13 will be displayed as January, E14 will be
displayed as February, and so on.
2. Type the product number 9865 in cell F12, 9870 in cell G12, 9875 in
cell H12, and 9880 in cell I12.
3. In cell E12, type the formula:
=DSUM(Database,"Amount Sold",Criteria)
4. Select cells E12:I17.
5. From the Data menu, choose Table.
6. In the Row Input Cell box, type "E2" (without the quotation marks),
and in the Column Input Cell box, type" H3" (without the quotation
marks).
NOTE: E2 is the cell in the criteria range where you would type a
specific product number. Since you want the total number of each
product sold broken down by each month, leave E2 blank in the
defined criteria range. The table will automatically (internally)
substitute each product number listed in the table (F12:I12) into
cell E2 and calculate the formula based on that product. Cell H2
contains the formula =MONTH(B2)=MONTH($H$3). This formula checks
to see if the month in the first record of the Date field (B2)
equals the month of cell H3, which is the column input cell.
Remember, the table will automatically (internally) substitute
each month listed in the table (E13:E17) into cell H3 and
calculate the formulas based on that month.
Two-input table with computed criteria (with formulas displayed):
NOTE: Due to character-based screen display limitations, the
following 5-column table is shown in two parts.
(Left 2 columns of a 5 column table)
E F
--|-------------------------------------------------------
12| =DSUM(Database,"Amount Sold",Criteria) 9865
13| 31412 =TABLE(E2,H3)
14| 31443 =TABLE(E2,H3)
15| 31471 =TABLE(E2,H3)
16| 31502 =TABLE(E2,H3)
17| 31532 =TABLE(E2,H3)
(Right 3 columns of a 5 column table)
G H I
----------------------------------------------
9870 9875 9880
=TABLE(E2,H3) =TABLE(E2,H3) =TABLE(E2,H3)
=TABLE(E2,H3) =TABLE(E2,H3) =TABLE(E2,H3)
=TABLE(E2,H3) =TABLE(E2,H3) =TABLE(E2,H3)
=TABLE(E2,H3) =TABLE(E2,H3) =TABLE(E2,H3)
=TABLE(E2,H3) =TABLE(E2,H3) =TABLE(E2,H3)
Two-input table with computed criteria (with values displayed):
E F G H I
--|------------------------------------
12| 9865 9870 9875 9880
13| January 91 94 76 0
14| February 82 71 0 22
15| March 35 50 0 54
16| April 33 0 80 83
17| May 0 15 62 0
