T
timhl
Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel
I've set up Data Tables in Excel 2007 lots of times with a database formula, like DSUM or DCOUNT, where the column input cell is a field value in the criteria range. This is a quick way to count or sum the instances of specific values in the database.
Trying this for the first time in Excel 2008 for Mac, the same procedure doesn't evaluate the formulas correctly.
To try this, create a small table, 3 columns: col1, col2, col3. Set col1 values to Mon, Tue, Wed, Thu, Fri. Set col2 values to 1, 2, 3, 4 and 5. Set col3 values to 1, 1, 2, 3, 3. Now select the table from "col1" to "3" in the lower right corner, and give it a name: Insert->Name->Define, "mydatabase".
Now create a criteria range 3 rows above the table (it shouldn't be below the table). Insert 3 rows if necessary, then put the same column names across, col1, col2 and col3. Now highlight these and the empty cells directly below them (highlight 2 rows total), and give it a name: Insert->Name->Define, "mycriteria".
To the right of the table, enter a couple database formulas:
=DSUM(mydatabase,"col3",mycriteria). The value of this one is 10. The empty criteria cells select all rows resulting in the sum of 1, 1, 2, 3, and 3.
=DCOUNT(mydatabase,"col3",mycriteria). The value of this one is 5. Again, the empty criteria cells select all rows resulting in the count of all 5 rows.
Now put a 3 in the cell directly below "col3" in the criteria range. The DSUM value changes to 6 because two rows in mydatabase have "3" in col3 (Thu and Fri), and these are added up. The DCOUNT value changes to 2, because this is the number of matched rows.
Now make a small data table. Right below the DCOUNT cell, put 1, 2, and 3 going down the column. Then highlight the DCOUNT formula and 1, 2, and 3, and one additional column to the right. Open the Data menu, select Table..., click in the "Row input cell" field, then click on the criteria cell that has the "3" you entered (col3), and click OK.
The results in Excel 2008/Mac are:
1 1
2 2
3 3
Shouldn't they be:
1 2
2 1
3 2
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel
I've set up Data Tables in Excel 2007 lots of times with a database formula, like DSUM or DCOUNT, where the column input cell is a field value in the criteria range. This is a quick way to count or sum the instances of specific values in the database.
Trying this for the first time in Excel 2008 for Mac, the same procedure doesn't evaluate the formulas correctly.
To try this, create a small table, 3 columns: col1, col2, col3. Set col1 values to Mon, Tue, Wed, Thu, Fri. Set col2 values to 1, 2, 3, 4 and 5. Set col3 values to 1, 1, 2, 3, 3. Now select the table from "col1" to "3" in the lower right corner, and give it a name: Insert->Name->Define, "mydatabase".
Now create a criteria range 3 rows above the table (it shouldn't be below the table). Insert 3 rows if necessary, then put the same column names across, col1, col2 and col3. Now highlight these and the empty cells directly below them (highlight 2 rows total), and give it a name: Insert->Name->Define, "mycriteria".
To the right of the table, enter a couple database formulas:
=DSUM(mydatabase,"col3",mycriteria). The value of this one is 10. The empty criteria cells select all rows resulting in the sum of 1, 1, 2, 3, and 3.
=DCOUNT(mydatabase,"col3",mycriteria). The value of this one is 5. Again, the empty criteria cells select all rows resulting in the count of all 5 rows.
Now put a 3 in the cell directly below "col3" in the criteria range. The DSUM value changes to 6 because two rows in mydatabase have "3" in col3 (Thu and Fri), and these are added up. The DCOUNT value changes to 2, because this is the number of matched rows.
Now make a small data table. Right below the DCOUNT cell, put 1, 2, and 3 going down the column. Then highlight the DCOUNT formula and 1, 2, and 3, and one additional column to the right. Open the Data menu, select Table..., click in the "Row input cell" field, then click on the criteria cell that has the "3" you entered (col3), and click OK.
The results in Excel 2008/Mac are:
1 1
2 2
3 3
Shouldn't they be:
1 2
2 1
3 2