Two Questions:Roundup to the max value of

[mr.bank]

Hi,

I have thought out an example that may help clarify what I am looking for. Say I wished to purhcase 200 bananas for 25 retail outlets. I can only ship whole bananas. Is there a function that will split the bananas out to each store in a whole number to a max of 200 for all stores.

Kind Regards,

Mr. Bank

 

[tshooter]

if you want equal distribution then

=INT(banana / stores)

 

[Scott Huish]

If this is a clarification, the clarification should be posted to the original thread.

 

[mr.bank]

Okay this rounds it down to the nearest integer which is great. But let me give you an example. I purchase 200 bananas. I have over 100 stores but only 25 are to recieve a portion of those 200 bananas. Each of the 25 stores must recieve at least 1 full banana to a maximum sum of 200. Any thoughts?

if you want equal distribution then

=INT(banana / stores)

 

[Scott Huish]

I think that formula would still do it.

In this case your banana count is 200
and your stores count is 25

Each store would get 8 bananas.

 

[mr.bank]

Hi HOTPEPPER,

Yes in a simplified world. However, Lets add this problem. I am distributing these based off of a percentage of store sales. So if I have a store that has 0 sales thus they will recieve 0 product. However they must recieve at a minimum 1 unit. (taking some quantity away from all the other stores so the max that all the stores recieve is 200).

I think that formula would still do it.

In this case your banana count is 200
and your stores count is 25

Each store would get 8 bananas.

All the best,

Mr. bank

 

[Scott Huish]

Is this the extent of the problem? Because this is not what you originally asked, nor was it asked in your clarification.

Do you have a column that contains the percentage of bananas they are to receive?

 

[mr.bank]

Yes I do (below is a simplified version. I have many logical statements eliminating stores that are not to receive bananas).

Column A
Store Identifications

Column B
Sales

Column C
Percentage of total Sales

Column D
Distribution

Is this the extent of the problem? Because this is not what you originally asked, nor was it asked in your clarification.

Do you have a column that contains the percentage of bananas they are to receive?

 

[Scott Huish]

How do you account for a store not receiving bananas? Your previous post indicated every store must receive at least 1 banana.

 

[mr.bank]

Hi Hotpepper,

I will remove all of the sensitive information tonight and provide it to you to show what I have done.

Are you able to send me an private message with your e-mail in it.

Best regards,

How do you account for a store not receiving bananas? Your previous post indicated every store must receive at least 1 banana.