Data spreading methods

IBM® Planning Analytics for Microsoft Excel provides a variety of data spreading methods that you can use to distribute numeric data to cells in an exploration. For example, you can use data spreading to evenly distribute a value across a range of cells or to increment all values in a range of cells by a desired percentage.

The methods that are available in the Spreading dialog box depend on the cells that you select for data spreading. For example, some methods, such as Equal Across Leaves, are applicable to consolidated cells only.

Proportional

The proportional spread method distributes a specified value among cells proportional to existing cell values.

For example, consider a view in which the values for Argentina in the months January, February, and March are 10, 30, and 60, respectively.

The sum of these values is 100, with the value in January accounting for 10% of the sum, the value in February accounting for 30%, and the value in March accounting for 60%.

When you proportionally spread the value 300 across these cells and select the Replace update action, the result is as follows.

January contains the value 30, which is 10% of 300
February contains the value 90, which is 30% of 300
March contains the value 180, which is 60% of 300

These values are proportionally equivalent to the cube values that existed before you apply data spreading.

Repeat

The repeat method repeats a specified value across cells in a view.

The value you enter repeats across the range of cells that you selected. When you apply the repeat spreading method to a single consolidated cell, the value being spread is distributed proportionally to all leaves of the consolidated cell.

Repeat Across Leaves

The repeat leaves method copies a specified value to the leaves of a consolidation. When you apply this method, you can copy the value to all leaves of the consolidation or only to those leaves that already contain non-zero values.

For example, assume that there are several leaves of Year, Argentina with values.

If you use the repeat leaves method to copy the value 400 to the leaves of Year, Argentina currently populated with non-zero values, the value 400 is copied to all leaves that contained non-zero values.

If you initiate the repeat leaves method from a cell identified by more than one consolidated member, the specified value is copied to all leaves associated with the cell.

Table 1. Options for the repeat leaves data-spreading method
Option	Description
Populated Leaf Cells	The specified value is copied only to leaf cells currently containing non-zero values.
All Leaf Cells	The specified value is copied to all leaf cells regardless of current values.

You can apply the Repeat Across Leaves method only to consolidated cells.

Straight

The straight line method populates cube cells by linear interpolation between two specified endpoints. It requires both a start value and an end value.

For example, with the start value of 100 and the end value of 200, the option populates the intervening cells with values at equal intervals between the two endpoints.

You can apply straight line spreading only across single rows or columns, not across rectangular ranges.

% change

The percent change method multiplies the current cell values by a specified percentage. The product of that multiplication can then replace, be added to, or be subtracted from the existing cell values.

When you apply the percent change method and specify a % Change value of 10, the system multiplies each cell value by 10% (or .10). If you select the Add update action, the product of multiplication is added to the existing cell values. The result is that each cell value is increased by 10%. The percentage change is applied across the range of cells that you selected.

Equal

The equal spread method distributes a specified value equally across the cells in a view.

For example, consider a view where a range of 12 cells is selected.

When you equally spread the value 60 to these cells and select the Add update action, the value is equally spread across the range and added to the existing cell values. The result is that each cell value is increased by 5 (60/12=5).

The value you entered spreads equally across the range of cells that you selected. When you apply the equal spread method to a single consolidated cell, the value being spread is distributed proportionally to all leaves of the consolidated cell.

Equal Across Leaves

The equal spread across leaves method distributes a specified value equally across all leaves of a consolidated cell. When you apply this method, you can choose to distribute the value to all leaves of the consolidation or only to those leaves that already contain non-zero values.

If you initiate the equal spread across leaves method from a cell identified by more than one consolidated member, the specified value is distributed to all leaves associated with the cell.

Table 2. Options for the equal spread leaves method
Value	Description
Populated Leaf Cells	The specified value is copied only to leaf cells currently containing non-zero values.
All Leaf Cells	The specified value is copied to all leaf cells regardless of current values.

You can apply the Equal Across Leaves method only to consolidated cells.

Growth %

The growth % method accepts an initial value and a growth percentage. By using the initial value as a starting point, this method sequentially increments all values in a range by the specified growth percentage.

You can apply growth % spreading across single rows or columns, not across rectangular ranges.

Clear

The clear method clears values from cells in a view. You can apply this method to either leaf cells or consolidated cells. When you apply the clear method to a consolidated cell, all leaves of the consolidation are set to zero.