Automating subtotals with data table commands in any spreadsheet
Control Excel's cell pointer during data entry
Q I'm using Excel to store lists of evaluation data that I need to summarise according to training teams. At first I thought Excel's subtotal feature would be perfect for this, but I'm finding it's a pain in the neck. Every month I have to insert the subtotals (they're actually averages), format them, print the report, then remove the subtotals before I add more data or sort the list for a different report.
Can I set up a subtotals report that I don't have to re-create
every month?
- Sandy Jernigan
A There are several solutions to this standard problem of summarising spreadsheet data in groups. You could use Excel's pivot tables, but the slickest method is an old trick that uses the Data Table feature, a part of every major spreadsheet since 1-2-3 release 1A, in conjunction with the database average function - daverage in Excel, and @davg in 1-2-3 and Quattro Pro.
A data table shows how the results of one or more formulas are affected by the changing values of an input cell. When the input cell is the criteria cell of a database function (that is, when it restricts the formula to certain records), each result summarises a different subset of the data. In Excel, the data table is an array formula that is recalculated dynamically as the data changes. In 1-2-3 and Quattro Pro, you must issue a command to recalculate the data table.
To build the Excel model, which has several refinements unique to Excel, follow the steps outlined here.
1. Enter your data in range A1:D12.
2. Enter the labels for the input cell (F1), and for the data table (F4:F7).
3. Use the Insert-Name-Define command to define the criteria range, F1:F2, and the database range, =offset($A$1,0,0, counta($A:$A),4). This offset formula makes the Database range dynamic so that it expands to include any new data appended to the bottom of the range.
4. In cell G4, enter =daverage(database, "Score 1",criteria), and in cell H4, enter =daverage(database,"Score 2",criteria). Assign cell G4 a custom format that replaces the formula result with the label 'Score 1': select G4, select Format-Cells, choose Custom, type "Score 1" (including the quotation marks), and click OK. Repeat these steps for cell H4.
5. Create the data table: Select range F4:H7, select Data-Table, specify F2 as the Column Input Cell, and click OK.
The formula in G4 calculates the average of the values in column C that match the criteria in F2. The formula in H4 does the same for column D. The data table (F4:H7) uses the values in F5:F7 as alternative inputs for F2; thus, the values in G5:G7 represent the Score 1 averages for the three teams, and the values in H5:H7 represent the Score 2 averages for those teams.
In Quattro Pro, the dynamic range name trick won't work, so assign the name database to range A1..D12. In cell G4, enter the formula @davg(database,2, criteria), where 2 represents the offset number of the Score 1 column, or column C. In cell H4, enter the formula @davg(database,3,criteria), where 3 is the offset number of the Score 2 column, or column D. To format a cell so that it displays the label 'Score 1' or 'Score 2', right-click the cell and select Block Properties. Select the Numeric Format tab, click User Defined, and in the Formats Defined box, enter T'Score 1' or T'Score 2', including the single quotes. To create the data table, select range F4:H7, and then select Tools-Numeric Tools-What-If. Specify F2 as the Input Cell, click Generate, then click Close.
In 1-2-3, assign the name database to A1..D12. In cell
G4, enter @davg(database, "Score 1",criteria).
In cell H4, enter @davg(database,"Score 2",criteria).
To create the data table, select F4..H7, then Range-Analyze-What-if
Table. Specify the number of variables as 1, specify F2 as
Input Cell 1, and click OK.
240_p.tif Figure 1: Combine a data table
with the daverage function to summarise grouped data. this Excel
model can be adapted for any spreadsheet.
Q My brother and I bought a computer together, and on his recommendation I switched from 1-2-3 to Excel 95. Whenever I press <Enter> to put something in a cell, the pointer moves down to the next cell. This is driving me nuts! My brother loves this feature, but I hate it. How do I turn it off?
- Tyrone Poussaint
A Good question! Another reader, Charles Pasek, has a solution: Select Tools-Options, click the Edit tab, and uncheck 'Move Selection after Enter'.
If you don't want to turn off the feature permanently, you can temporarily override it by pressing <Ctrl>+<Enter>. (Warning: if you have preselected a range, this keystroke copies your entry into all selected cells.)
Because you're running Windows 95, you and your brother can each have it your own way. From the Taskbar select Start-Settings-Control Panel, double-click the Passwords icon, then select the User Profiles tab. Select the button that begins Users can customise their preferences, and click OK. Now, each time you start Windows, you'll be prompted for a user ID and password. You and your brother can each create your own Windows 95 setup. And since Excel 95 is designed to run under Windows 95, you can each customise Excel by selecting Tools-Options. Whenever you start Excel, the preferences you specify become the defaults.