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!
Bookmarks