Spreading: client-side writeback in formula reports

Spreading allocates values, which are written to aggregated cells, to their base cells. The method of allocation depends on whether the cells already contain values, whether one or more base cells is locked and whether one or more base cells has a constraint.

If the base cells contain values, the ratio of each value to the value of the aggregated cell is calculated. Values written to the aggregated cell are allocated to the base cells in the same ratios.

If the cells are empty, the amount written to the aggregated cell is allocated equally between the base cells.

If the base cells contain values, and one or more base cells is locked, the ratio of each unlocked value to the value of the aggregated cell is calculated. The value of the locked cell is subtracted from the amount to be allocated. The remainder is allocated to the unlocked cells in the ratios calculated.

If the cells are empty, and one or more base cells is locked, the amount written to the aggregated cell is allocated equally to the unlocked base cells.

Note: If an aggregated cell contains a division formula (e.g. X=Y/Z) and Y and Z are unlocked, then Z will be treated as if it were locked.

Constraints can be defined on individual cells or ranges of cells. Only values between specified upper and/or lower limits can be spread to the cell on which the constraint is defined. Once a constraint has been reached, the cell acts as if it were locked and any remaining values are allocated to cells which are not subject to the constraint.

Example: Base cells contain values

Three base cells contain 1000, 600 and 400. The aggregated cell contains 2000, the sum of the base cell values.

The ratios of each base cell value to the aggregated cell value are:

1000/(1000,600,400) = 1000/2000 = 0.5
600/(1000,600,400) = 600/2000 = 0.3
400/(1000,600,400) = 400/2000 = 0.2

If the value of the aggregated cell is changed from 2000 to 3000, then 3000 is allocated to the base cells in the same ratios. That is:

3000 x 0.5 = 1500
3000 x 0.3 = 900
3000 x 0.2 = 600

Example: Base cells are empty

If the base cells are empty, then so is the aggregated cell. In this case, a value which is written to the aggregated cell is allocated equally to the base cells. So, if 3000 is written to the aggregated cell, 1000 is allocated to the three empty base cells.

Example: A base cell which contains a value is locked

Three base cells contain 1000, 600 and 400. The aggregated cell contains 2000, the sum of the base cell values.

The base cell which contains 400 is locked.

The ratios of each unlocked base cell value to the aggregated cell value are:

1000/(1000,600) = 1000/1600 = 0.625
600/(1000,600) = 600/1600 = 0.375

If the value of the aggregated cell is changed from 2000 to 3000, then the value of the locked cell is subtracted. The remainder is allocated to the unlocked base cells in the same ratios. That is:

2600 x 0.625 = 1625
3000 x 0.3 = 975

Example: All base cells are empty and one is locked

Example: A base cell has a constraint

Three base cells contain 1000, 600 and 400. The aggregated cell contains 2000, the sum of the base cell values.

The cell which contains 400 has a constraint with an upper limit of 500 (but no lower limit).

The ratios of each base cell value to the aggregated cell value are:

1000/(1000,600,400) = 1000/2000 = 0.5
600/(1000,600,400) = 600/2000 = 0.3
400/(1000,600,400) = 400/2000 = 0.2

If the value of the aggregated cell is changed from 2000 to 3000, then 3000 would normally be allocated to the base cells in the same ratios. That is:

3000 x 0.5 = 1500
3000 x 0.3 = 900
3000 x 0.2 = 600

But, 600 exceeds the constraint by 100. So, 500 is allocated to the third cell. The third cell then acts as if it were locked. and a further calculation reallocates the remaining 100.

The ratios of the unconstrained base cell values to the aggregated cell value are calculated as:

1000/(1000,600) = 1000/1600 = 0.625
600/(1000,600) = 600/1600 = 0.375

The balance of 100 is allocated to the unconstrained cells in these ratios, and added to the amount which has already been spread to the cells:

1500 + (100 excess x .625 new ratio) = 1562.50 = 1,563

900 + (100 excess * .375 new ratio) = 937.50 = 938