How to solve such an optimisation problem?

Hi guys,

So I was planning on referring to the solver function, but i'm not sure that it is suitable for the problem I have at hands. Let me try to explain as clearly and concisely as possible the situation:

• I am going to market to buy (say 100 as in the spreadsheet) fruitbowls.
• I have 3 bags that I want to split them into according to a specific breakdown 75% in bag 1, 20% into bag 2, and the remaining 5% into bag 3

The key question I have is to do with the allocation into these 3 bags, which should meet the following 3 conditions:

• 1) The average price of the bowls in each bag should be exactly the same. I.e. as in the example spreadsheet the overall price is 104.55 paid. So the average price of the bowls in each of the 3 buckets should be as close to this number, and the same across all 3
• 2) The number of bowls in each bucket meets my target split (i.e. 75/20/5 split)
• 3) Only whole numbers of bowls exist in each bucket, and any 0.5 get rounded up to 1

I've attached an example spreadsheet as described here. Any help would be greatly appreciated.