As an example of the kind of analysis I think would be more valuable here.
Since row 5 and 7 have the highest price, and the "detractors" (rows 6 and 8) do not take away as much as 5 and 7 add, it seems like the maximum should be one where these two are at maximum (135-60=75). Now, in order to meet the criteria, you need to add the smallest amount to rows 6 and 8 (135-25=110, 110-60=50, so 50 goes in these rows). Working up from there, row 4 has a positive price, and both rows above and below row 4 also have a positive price, so this one should also be maximized (135-60=75). Row 3 has a positive price, but row 2 above it has the opposite (negative) price. So, as long as row 3 meets the criteria determined by row 4 (50 to 75) and row 2 is set to a minimum relative to row 3 (so that total row 2 comes out 25 less than row 3), row 2 and row 3 will cancel each other out and have no effect on the final result. This analysis leads to the solution you suggested in post #5, amongst other possible solutions.
I think what this analysis suggests is that, rather than a NR type algorithm, and algorithm that can look at price (maybe a three row moving average of price and/or and overall average price) and see that, when average price is positive, move positive prices to "maximum, then minimize the "detractors" relative to those maxima. A scenario where average price is negative would probably suggest first minimizing all of the negative prices, while maximizing the the positives relative to those negatives. If we can prove to ourselves that this kind of algorithm works, then we can use the appropriate spreadsheet formulas to optimize different scenarios without using Solver. This kind of analysis is looking more at what is going on in the price and constraint columns than in the final revenue column.
Bookmarks