Rising Tide Distribution Formula

[JohnStevensInc]

I need help with a formula that will help with rising tide. Each worker in column A has a max daily inventory allotment as indicated in column B. Column C represents what they have been alloted that day. Column D represents the percentage that they have already been alloted that day.

20 additional units are received. What formula should I enter in column E that will distribute the units as evenly as possible across all workers (Rising tide = Jack gets so many units until he is even with Sarah's percentage, then Jack and Sarah get distributed evenly until they are equal to Frank's percentage and so on until the inventory is exhausted. THANKS FOR ANY HELP YOU CAN GIVE ME!

A B C D E
1 Jack 10 0 0%
2 Sally 150 50 33%
3 Frank 85 20 24%
4 John 150 100 67%
5 Sarah 110 10 9%

 

[shg]

Interested in a Solver solution?

 

[JohnStevensInc]

What does that mean?

 

[shg]

Data > Solver -- a tool for optimizing values in linear programming and related problems.

 

[JohnStevensInc]

I just added it. I have no idea how to use it.

 

[shg]

       --A-- -B-- -----C----- -----D------ --E-- -------------------------------F-------------------------------
   1                     Add:           20       D1: Input                                                      
   2                                                                                                            
   3   Name  Max    Initial   Distribution % Max                                                                
   4   Jack   10           0            3    30% E4 and down: =(C4+D4)/B4                                       
   5   Sarah 110          10           16    24%                                                                
   6   Sally 150          50            0    33%                                                                
   7   Frank  85          20            1    25%                                                                
   8   John  150         100            0    67%                                                                
   9                                                                                                            
  10              Left overs:           0        D10: =D1-SUM(D4:D8)                                            
  11                                 StDev  4.6% E11: =STDEV(E4:INDEX(E:E, LOOKUP(9E+307, 1/D1:D8, ROW(D1:D8))))

D1 is the quantity to add

Data > Solver

Set Target Cell: E11 Equal to: Min By Changing Cells D4:D8

Subject to the constraints:

D10 = 0
D4:D8 = integer
D4:D8 >= 0

Then press Solve

 

[shg]

That was messy.

       --A-- -B-- -----C----- --D-- --E--
   1                     Add:    20      
   2                                     
   3   Name  Max    Initial   Dist  % Max
   4   Jack   10           0     3    30%
   5   Sarah 110          10    16    24%
   6   Sally 150          50     0    33%
   7   Frank  85          20     1    25%
   8   John  150         100     0    67%
   9                                     
  10              Left overs:    0       
  11                          StDev  4.6%

D1: Input

E4 and copied down: =(C4+D4)/B4

D10: =D1-SUM(D4:D8)

E11: =STDEV(E4:INDEX(E:E, LOOKUP(9E+307, 1/D1:D8, ROW(D1:D8))))

Data > Solver

Set Target Cell: E11 Equal to: Min By Changing Cells D4:D8

Subject to the constraints:

D10 = 0
D4:D8 = integer
D4:D8 >= 0

Then press Solve

 

[JohnStevensInc]

Thank you!!!!!!!!!!!!!!!

 

[shg]

You're welcome.

I failed to note the original order -- the data should be sorted acending by initial allocation percentage -- Jack, Sarah, Frank, Sally, John.