evenly distribute numbers across a range

[Afarag]

I need help in building an evenly distribution model, as shown below I want to distribute the summation Total over the Days but keep giving every day only 300 and if the total cell is greater than 300 will fill only 300 then move the rest to the next day and vs if the cell is less than 300 will move to the next cell to complete it up to 300, etc...
how could I apply this model using a formula or a VBA code. I couldn't use Solver tool for this model as I have too many variable cells on my real model



in the attached sheet >> Data worksheet, I could apply the same model

 

[Peter Bartholomew]

This can be achieved directly using a (somewhat complicated) formula. The starting point is to use the cumulative demand (both up to and including the current batch) and compare the values with the cumulative capacity (the product of days and 300 units). A formula I use to determine the overlap between two number ranges [open, close] and [threshold, limit] is

= IF(close<limit, close, limit)
- IF(close<threshold, close, threshold)
- IF(open<limit, open, limit)
+ IF(open<threshold, open, threshold)

(Two of the terms always combine to give zero but I tend to find it is more trouble to test to find which than it is to carry out the subtraction)

 

[Afarag]

This can be achieved directly using a (somewhat complicated) formula. The starting point is to create cumulative demand (both up to and including the current batch) and compare the values with the cumulative capacity (the product of days and 300 units).

could you please help me with this formula Peter

 

[Peter Bartholomew]

See above. I had the file open for editing and missed your reply. Each line of the formula is the minimum of two quantities and, if you are not using array formulas, the MIN function could be used.

 

[vletm]

Afarag
Could You give verified expected results based Your sample data... someway like below?

 

[Afarag]

Afarag
Could You give verified expected results based Your sample data... someway like below?
View attachment 71456

Hi vletm, I need to sum Transaction_count till getting 46499 in each day like the first example that sum the values till getting 350 for each column,

so the expected result will be like below

 

[Peter Bartholomew]

Now I haven't the remotest idea what is going on. I had assumed the Transaction_count was a cumulative total since it forms a strictly increasing sequence and the numbers are far greater than the 300 that I read to be the daily capacity!

I use Excel 365 so I am able to present a calculation as a sequence of semantically intelligible steps

= LET(
  minUpper, IF(close#<limit#, close#, limit#),
  maxLower, IF(open#>threshold#, open#, threshold#),
  overlap, minUpper - maxLower,
  IF( overlap>0, overlap, 0 ) )

which doesn't help at all if I have misunderstood the problem.

 

[Excel Wizard]

Please try at D2

=MAX(0,MIN($C2-IF(COLUMNS($D2:D2)=1,0,SUM(C2:$D2)),$C$45-SUM(D$1:D1)))

 

[Afarag]

Please try at D2

=MAX(0,MIN($C2-IF(COLUMNS($D2:D2)=1,0,SUM(C2:$D2)),$C$45-SUM(D$1:D1)))

thanks a lot, it works like a charm

 

[vletm]

Afarag
Here, one sample with which You can give 'any value' to do that without ... formulas and no matter of Your table size.
Press [ Do It ] ...
This sample shows solution ... like 'animation'.

 

[Peter Bartholomew]

Now as I have finally understood the problem!

= LET(
  threshold, SEQUENCE(1,90,0,capacity),
  limit,     threshold + capacity,
  open,      ACCUMULATE(transactions,,,0),
  close,     open + transactions,
  minUpper,  IF(close<limit, close, limit),
  maxLower,  IF(open>threshold, open, threshold),
  overlap,   minUpper - maxLower,
  IF( overlap>0, overlap, "" )
  )

The formula returns the stated results. It is placed in cell D2 and spills to populate the entire table.
It would be straightforward to make the number of columns adjust dynamically as the transactions increase.

I guess I should now look at @Excel Wizard's solution to try to identify the terms and then the approach.

 

[Peter Bartholomew]

I performed a few timing runs over the range both using the traditional approach and LET/ACCUMULATE.
The average over 3 calculation runs were 30.92 and 3.38 milliseconds respectively. I suspect the names in LET have greater overheads but the manner in which the accumulation is performed tips the result in favour of the DA. Not that 30ms is a problem.