formula for ordering inventory based on maximum allowed

[ctrrs817]

product example.xlsx

i have 24 items on my inventory list each one has a ranking based on their importance and min and max allowed in warehouse. I am only allowed to purchase 10 pallets a week. Each item might need to have 2 pallets ordered depending on min and max value. I need help on a formula to order only the top ten percent but it must equal to 10 pallets. Then the next week if one item was not order because it did not meet the criteria then the formula might pick it up for next weeks order.

please help! thanks!

[Melvinrobb]

Try uploading a sample workbook, with mock data in the structure you are looking for, with a realistic example of a few weeks, and show what you would want the formula to return each week.
Try showing unique scenarios, so the solutions provided for you will meet those criteria.

[ctrrs817]

ok. took a while. new at this

[Melvinrobb]

Is this the current inventory for one week? You want the formula to order only the top ten percent, but i am not sure what that means. Top 10 percent of what? how is this number determined?

Can you explain how you would go through this manually, and what 10 pallets you would end up wanting to order in this scenario, and why?

[ctrrs817]

yes, inventory is for one week. Inventory can not be based on more then a week. the points are the point system they assigned each product. I then changed it to a percent using the percentrank formula to get a better view. if you see under the min and max those are the number of pallets each product can have in the warehouse. since its mandatory we order a truck load of only 10 pallets a week i need to have a formula where i change the on hand section and it will only order the top items to equal 10 pallets. the water 1LT (21 points= 100%) is more important then the water .5 LT(2 points=17%) because of the point that was assigned to it. so even though i might need to order .5 LT i cant this week because i need 10 pallets of the products that are higher in rank. Sorting for top 10% is not what i need. I need to order top % of products that would fit in a 10 pallet truckload. Also since some products are seasonal the Min and Max will change. that will also need to be considered. i can do both VBA and Macro or Formula but just need some direction on how to get to it. thanks for any feedback you can give!

I will do it manually and let you know which product i want to buy.