Macro/Solver to assign correctly the correct quantity of purchased apples

[Antoniozarzal]

Hello everybody

I will purchase a quantity of apples during the year, and 3 sellers made me their offers (quantity of apples and price per apple). Those offers are in sheets Seller 1, 2 and 3.

So I need to determine what is the best combination among those offers, purchasing at the lowest possible price while purchasing the required quantities.

I have been using Solver. I created 3 decision variables, which are a percentage that affects the total quantity of offered apples per seller. The objective is to minimize the $/apple. Constraints are: decision variables <=1 and Total purchased apples = Total required apples.

I got to the solution, partially. I'm having an issue when the sellers offer the same price for the apples and the sum of their offers makes exceed the required quantity of apples (I don't know how solver determines the results of the decision variables when this happens). And I need to be fair with the sellers, so, when this happens I need solver or a macro that:

1. Divides: Total apples required(after subtracting the best offer if there was any)/Sum the total of apples of sellers with the same price (for this example, sum of sellers 2 and 3).
2. Assing the result in 1) as the % of the decision variables of sellers 2 and 3.

I hope I have made myself understood. Thank you for your advice.

Antonio I.

 

[Antoniozarzal]

Gofile - Free Unlimited File Sharing and Storage

Gofile is a free, secure file sharing and storage platform. With unlimited bandwidth and storage, you can easily store and share files of any type without any limits. Our advanced features, such as CDN support and password protection, make Gofile the ideal choice for individuals and businesses...

gofile.io

This is a link to the file if you want to watch it.