Distribute amount in a variable range of columns

Hi all,

I am trying to distribute an amount across a set of adjacent columns, based on a 'capped' numbers. For instance, if the amount to distribute is 105 and the 'capped' number for that row is 50, then I'd like to results to be distributed as: 50, 50, 5 (remainder). Not split evenly, and across a varying number of columns.

I got it working with the following formula: =MAX(0;IFERROR(MIN($C7;$C7+B7;$B7-SUM($C7:C7));0);0)

However, the trick is that the amount should start to be distributed in a given column (which can change), based on the result of a PivotTable.

The logic:
1. G106 contains the number to be distributed (e.g. 305)
2. H106 contains the 'capped' numbers (e.g. 50)
3. Columns J106:V106 will receive the distribution of the number in (1)
4. Start the calculation on a given week, based on a PivotTable.

Attention: The PivotTable can indicate that week 2 contains 105 and week 3 contains 200 (amounting to 305, number in G106); therefore, the calculation must first distribute 105 as from week 2 and then start the distribution of the amount from week 3 (200), based on the capped amount of 50; The result would then be (week 1 to 8): 0, 50, 50, 50, 50, 50, 50, 5

Maybe best is to create the VBA code and assign it to my own formula?

Thank you!

Upload a sample. To upload Go Advanced - Manage Attachments -

Perhaps this will help.
On Sheet 1 I attempted to construct a table of source data (columns A:C) based on the information in rows 11:16 of Sheet 2.
Column D is then populated using:

Formula:

Please Login or Register  to view this content.

The pivot table on Sheet 1 then displays the data in the fashion shown at the top of Sheet 2.
Let us know if you have any questions.