• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

evenly distribute numbers across a range

Afarag

Member
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

71454

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

Attachments

Peter Bartholomew

Well-Known Member
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
Code:
= 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)
 
Last edited:

Afarag

Member
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

Well-Known Member
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.
 

Peter Bartholomew

Well-Known Member
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
Code:
= 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.

71458
 
Last edited:

vletm

Excel Ninja
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'.
 

Attachments

Last edited:

Peter Bartholomew

Well-Known Member
Now as I have finally understood the problem!
Code:
= 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

Well-Known Member
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.
 
Top