Rising Tide Distribution Formula

JohnStevensInc

Board Regular
Joined
Dec 2, 2003
Messages
109
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%
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Interested in a Solver solution?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Data > Solver -- a tool for optimizing values in linear programming and related problems.
 

JohnStevensInc

Board Regular
Joined
Dec 2, 2003
Messages
109

ADVERTISEMENT

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

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Code:
       --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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

That was messy.

Code:
       --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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,325
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top