Creating subtotals

OpenOffice.org 3 Calc Guide

Chapter 9 Data Analysis

< Previous Section

Next Section >

• Consolidating data
• Creating subtotals
• Using scenarios
• Multiple operations
• Goal seek
• Solver

< Previous Page

Next Page >

SUBTOTAL is a function listed under the Mathematical category when you use the Function Wizard (Insert > Function). Because of its usefulness, the function has a graphical interface accessible from Data > Subtotals.

As the name suggests, SUBTOTAL totals data arranged in a array—that is, a group of cells with labels for columns and/or rows. Using the Subtotals dialog, you can select arrays, then choose a statistical function to apply to them. For efficiency, you can choose up to three groups of arrays to which to apply a function. When you click OK, Calc adds subtotals and grand totals to the selected arrays, using the Result and Result2 cell styles for them.

To insert subtotal values into a sheet:

1. Ensure that the columns have labels.
2. Select the range of cells that you want to calculate subtotals for, and then choose Data > Subtotals.
3. In the Subtotals dialog, in the Group by box, select the column that you want to add the subtotals to. If the contents of the selected column change, the subtotals are automatically recalculated.
4. In the Calculate subtotals for box, select the columns containing the values that you want to subtotal.
5. In the Use function box, select the function that you want to use to calculate the subtotals.
6. Click OK.

Setting up subtotals

If you use more than one group, then you can also arrange the subtotals according to choices made on the dialog’s Options page, including ascending and descending order or using one of the predefined custom sorts defined in Tools > Options > OpenOffice.org Calc > Sort Lists.

Choosing options for subtotals