Two Questions:Roundup to the max value of

mr.bank

New Member
Joined
Nov 4, 2009
Messages
13
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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,886
If this is a clarification, the clarification should be posted to the original thread.
 

mr.bank

New Member
Joined
Nov 4, 2009
Messages
13
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
Joined
Mar 17, 2004
Messages
19,886
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
Joined
Nov 4, 2009
Messages
13
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
Joined
Mar 17, 2004
Messages
19,886
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
Joined
Nov 4, 2009
Messages
13
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
Joined
Mar 17, 2004
Messages
19,886
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
Joined
Nov 4, 2009
Messages
13
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top