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 - Cloud Storage Made Simple

Secure, fast and free cloud storage solution. Upload and share files instantly.

gofile.io

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