Find a combination and choose the lowest one

I have a big problem getting this right. I want to find the lowest offer possible for a particular product category. There are a couple of offers (in this case 3), and each of these have price offerings for each of the products. One product category can contain different products and we have to choose only one offer for one product-category, ie. we cannot choose the lowest offerring for each product within the category. This results with that we have to (in the Optimal alternative column) manually compare the offerings and try to fins to lowest offer possible (for one category).

In column K, we have the volume and in L we can see the lowest and current cost. In column O, we see the difference between Lowest and current cost, which we of course want to be zero.

I think the logic should look like this:

//start position for i is Row2

OfferA=$B
OfferB=$C
Volume=$K

//This goes only for the first product category (from row 2 to 6)

for (i=2;i<6;i++)
{
sumA+=OfferA[i]*Volume[i];
sumB+=OfferA[i]*Volume[i];
}

If sumA "is Less than" sumbB
H2:H6="Offer A"
else
H2:H6="Offer B"


I tried to realize the logic, but this is a bit 'static'. Amount of offerings and products within the categories can of course vary. How can I achieve this in Excel, in a more 'dynamic' way? If I have to name each of Product categories, that's fine (then we can know the start and end position of each category).
Hope you get the point. Please shout if you dont understand it. Im attaching a pic on the sheet.


Thanks in advance!

Note: I had to modify the code as some characters didnt show up.

\1

hi shg, Im attaching the example sheet. The prices of the 3 offer are entered manually. The lowest price is automatically highlighted. In the Volumes column (K) you can see the volumne for each product and the orange-cell is calculating the sum of all the offers according to column H. If you change the content of H2:H6 to "Offer B" you will see that the sum then is based on prices from "Offer B" and so on. Column I is not used, so just ignore it.

The yellow cell present the lowers cost possible (mixed Offerrings). But only one offering can be selected for each product group. Hope you follow me? :SS

hi there...anyone who can help me? :SSS