# Two Questions:Roundup to the max value of

#### mr.bank

##### New Member
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

##### New Member
if you want equal distribution then

=INT(banana / stores)

#### Scott Huish

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

#### mr.bank

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

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

#### mr.bank

##### New Member
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.

1,082,139
Messages
5,363,363
Members
400,731
Latest member
Jackserver

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...