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