Condense Range

The Condense Range function condenses a table of data based on a column and a Group Method.

Settings

Group

The cell that defines the column within the Apply To range used to condense the data in the other columns.

Group Method

The method by which to group the data in the Group column. The Group column can contain text, numbers, or timestamps. Text can be grouped with or without case sensitivity.

For numbers, grouping can be done on the Cell Value or the Cell Text (case is not considered here).

Cell Value
Grouping is based on the underlying value in the cell, to the accuracy of the cell value
Cell Text
Grouping is based on the cell value displayed (and formatted).

For example, if the Group column has the values 2.11 and 2.12 and formatted for 1 decimal place, Cell Value would treat these as different whereas the Cell Text would treat them the same.

For timestamps, the Group Method can be Second, Minute, Hour, Day, Month or Year in multiples of the Interval.

Condense To

This defines how to condense the data in the columns in the range outside the Group column. The following options are available:

First Value
The value corresponding to the first row of the group.
Last Value
The value corresponding to the last row of the group.
Average
The average of all the rows of the group.
Maximum
The maximum value of all the rows in the group.
Minimum
The minimum value of all the rows in the group.
Total
The total of all the rows of the group.
Count
The count of all the non-blank rows of the group.

Interval

This setting can be a fixed number, a variable, or a single cell reference. If Interval does not evaluate to a number, it is set to 0.

The value of Group Method and Interval influence how the grouping is performed.

Cell Value

Interval = 0
Rows are condensed for each unique value in the Group column.
In the above example, the Temperature is averaged over each unique State.
Interval > 0
The rows are condensed in groups that are determined by the value of the first row of the Group column plus a multiple of the interval.
In the above example the Interval=1 which results in the Temperature averaged over every State., leaving blanks in the result e.g., State=2 does not exist in the raw data.

Cell Text

Interval (not used)

Rows are condensed for each unique displayed value in the Group column.

Time Based

Interval = 0
Rows are condensed for each unique value of the selected time element in the Group column. For example, suppose a Group Method of Minute and an Interval of 0.
In the above example, the raw data is condensed to averages for each unique minute in the DateTime column.
Interval > 0
The rows are condensed in groups that are determined by the value of the first row of the Group column plus a multiple of the Interval of the Group Method selection
In the following the Group Method is Minute and the Interval=2.

In the above example, the raw data is condensed to averages for each 2 Minute interval in the DateTime column. In the absence of data in the group, an empty record is displayed e.g., 11:07:00.

Example

Consider the following table of data:

To calculate the total sales for each person, use the following settings:

Apply To
Cell	$B$3:$C$3
Direction	Down
End	All cells are empty

Setting	Value
Group	$B$3
Group Method	Cell Text (Case Insensitive)
Condense To	Total
Interval	0

In this section:

Condense Range

Example

Search results