Rounding then Evenly Distributing

[thelegazy]

Hi All, 

I have bit of a complex solution I'm trying to work on, at least for me. I've attached an excel sheet with the proof of concept. Any help would be greatly appreciated!

The scenario is as such: 

• We can have 150 units of Product 1(Cell G1)
• The product is offered in sizes: S-XL
• The size ratio we maintain is for sizes XS-XXXL
• I  want to lookup the Size Ratio for the Sizes Offered (S-XL)
  • However, once you add up the ratios you fall below 100% since we aren't offered all the sizes
• I want to distribute the remaining % amongst the Offered Sizes (in the excel example it's 35%)
• So it would be the initial Size Ratio + Difference amount
• Then multiply each size by the Quantity Offered (150 Units) to get the quantities by size
  • This needs to be whole numbers as I can't purchase .5 of a unit
• Another crease is that I need the total to equal the Quantity Offered of 150 units, essentially the ceiling and floor.
  • In my current process, due to rounding it's often times below. 
• When the Product is offered in fewer sizes than what the curve is for, the total 

I have tried to do the most I can, but I'm not as advanced to complete this logic. 

Any help or pointing in the right direction would be greatly appreciated!

[jdunkerley79 · Accepted solution]

Try the attached.

It starts by selecting the percentage allocation for the available sizes

It then computes the nearest integer below for each size based on total allocated percentage and availabile qty.

You then know you will be within 1 of the ideal allocation for each size, so calculates the residual (r) and then adds 1 to the first r rows ordered by size of the fractional allocation.

This will mean that qty will be right and allocation will be fairly close to target percentage.

[thelegazy]

You sir are a gentleman and a scholar. 

I thought this was gonna take me forever to figure out, managed to implement it in one day. Thank you so much!!

[collin_pace]

Thanks @jdunkerley79.  A few years down the road from this posting and your solution helped me out this morning.  Much appreciated.