# 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

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

#### Attachments

• 28 KB Views: 6

#### 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).

#### 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.

#### vletm

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

#### Afarag

##### Member
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

Last edited:

#### 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.

Last edited:

#### Excel Wizard

##### Member

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

#### Attachments

• 80.4 KB Views: 5

#### Afarag

##### Member

=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

Hui

#### 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

• 28 KB Views: 1
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.