Help: I need a formula to help me allocate Bourbon allocation

[BigPerm79]

Good day!

I keep running into a problem that I need help with. I work for a liquor distributor and am building a spreadsheet to automatically allocates highly sought-after items based on account level criteria. I have a clip of the spreadsheet below.

Here is what I need:
1) Create a formula that allocates the available units for each customer (F1*A4), but stop allocating when the "initial allocation" achieved.
2) All allocations must be a whole number
3) The total allocations can not be larger than the initial allocation in Row 1

When I use =round(F1*A4), the total allocated is less than the initial allocation
When I use =RoundUp(F1*A4,0) the total allocated ends up being 4x larger than the initial allocation.

Any help is much appreciated.
BigPerm79

 

[alansidman]

You refer to column and rows, yet we cannot determine from your picture which those are. Please reload your sample file using XL2BB so that we don't have to recreate the file you already have. We cannot manipulate data in a picture.

 

[BigPerm79]

Sorry, this is a work computer and I can not download XL2BB. I tried but it wouldn't let me. I saved the file into a box.com account.

Here is a link to the file: Box

 

[BigPerm79]

You refer to column and rows, yet we cannot determine from your picture which those are. Please reload your sample file using XL2BB so that we don't have to recreate the file you already have. We cannot manipulate data in a picture.

Sorry I tried to download that but I couldn't because I am on a work computer. Please access the file from this link: Box

 

[alansidman]

Ok. Try this formula and see if it works. Cannot test beyond your sample so i don't know if it will come out to 85.

=IF(MOD($F$1*A5,1)>=$F$1*A5,ROUNDUP($F$1*A5,0),ROUND($F$1*A5,0))

Basically, if the calculation is greater than .5 then it rounds up otherwise it rounds down.

 

[alansidman]

Just tested my formula above on your Box.net file . FAIL!!! Back to the drawing board.

 

[Herakles]

You seem to be trying to allocate little stock amongst too many customers?
You have 85 units amongst 368 customers.

If any one customer only had one unit then only 85 customers could be satisfied but there are customers
who will be allocated 2 units so the number of customers who are allocated any stock at all reduced.

If you are allocating from the top and stopping when you run out of stock then the customers need
to be sorted by the % allocated in a descending order.

 

[BigPerm79]

You seem to be trying to allocate little stock amongst too many customers?
You have 85 units amongst 368 customers.

If any one customer only had one unit then only 85 customers could be satisfied but there are customers
who will be allocated 2 units so the number of customers who are allocated any stock at all reduced.

If you are allocating from the top and stopping when you run out of stock then the customers need
to be sorted by the % allocated in a descending order.

yes, I need it to stop allocating once it hits the max available. Once the total is gone, the rest of the customers are SOL. This happens often in my industry often because demand on some items is higher than the availability. The only fair way to allocate is to rank the customer on separate metrics (how they perform with the other products) which is why I have an allocation %.

 

[BigPerm79]

Just tested my formula above on your Box.net file . FAIL!!! Back to the drawing board.

Thanks for the work on this. I have spent way too much time trying to figure this out. You all are my last hope!

 

[Herakles]

Put this formula into F4 - T4
=ROUNDUP(F$1*$A4,0)

and this formula into F5 to F428 and copy across to column T.
=IF((SUM(F$4:F4)+ROUNDUP(F$1*$A5,0))>F$1,0,ROUNDUP(F$1*$A5,0))

The total of each column, row 4 downwards, should be the Initial Allocation figure for the column.