Solver doesn't find maxium revenue

In 9 dimensional space, it can be difficult to visualize the peaks and valleys and ridges (thinking in terms of 3D topography like on a topographic map) of your profit function. My guess is that, as currently formulated, your function has many peaks and valleys. The standard, default algorithms (Newton Raphson type algorithms if you are familiar) "go uphill" until they cannot go uphill any more. This is effective at finding a peak, but it can find a local maximum that may or may not be the global maximum. The solution in these cases is to understand your function and solution well enough to make better initial guesses.

The evolutionary algorithm available as an option may do a better job at finding "the tallest peak among several peaks".

I tend to test an algorithm/spreadsheet like this on problems where I know the final result. Do you know what your final, absolute maximum should be? I also find it useful to study my functions enough so that I know how they behave whether they have multiple maxima and other behaviors.

Interesting. Using Solver and starting values of 40 for D2 and 60 for D3:D9, I get a maximum of ~655.5 with the following values in D:
34.44
65.56
68.33
73.89
48.89
73.89
51.67
60
I cannot be certain that this is the absolute maximum without taking a good deal of time to further study the function. The Evolutionary algorithm is not available in my version (2007), so you might try the evolutionary algorithm if you have not yet tried it. You indicate that you are "not that fit" in math, but I would suggest you spend time with different values and try to understand the behavior of the function until you can better understand where the maxima occur and how to better use Solver to find the maximum.

Why is col G used in the calculation for col H when it's blank?

I'm not sure that VBA is going to be better at this point, because VBA does not have any "magical" algorithms preprogrammed in it that are better than Solver's algorithms. VBA may be a better solution after we have figured out a suitable, reliable algorithm for finding the maximum. It seems to me that, at this point, we are just "guessing". Solver will work just fine for "just guessing". We "guess some starting values, let Solver find the maximum from those starting values, then guess another set of starting values and let Solver run, and keep doing that until we have decided that we have found the maximum. VBA could automate this kind of "guess and check" method, but I'm not sure how many loops we would need before we would be confident that we have found the one and only maximum.

I think this needs more math work outside of Excel. The profit function seems simple enough. I think column H can be simplified to =F2*D2, and the profit is simply the sum of these products. It seems to me that the maximum is going to occur where we can add the most of row 5 and 8 while adding the least from row 2 6 and 9. Your constraint in column E does not make much sense to me, but I suspect that the constraint is going to have the biggest impact on the maximum, because that is where the real maximization occurs. I would probably spend some time outside of Excel understanding how this constraint/check column and the revenue column are related. From this work, I would probably formulate a new objective function that can better account for the relationship between the constraint and the revenue. Then, with that objective function in the spreadsheet, run Solver to find the maximum. I expect that, when you have a well formulated objective function, Solver's algorithms should have little difficulty finding the maximum.

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.

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.excelguru.ca/forums/showt...maxium-revenue

Looking at the formula for your revenue calculation I do wonder how this affects the solver solution

Please Login or Register  to view this content.

As you have set the the values in the price range as positive or negative this will then cause solver to find a negative value if the price is negative and a posetive one for the D value if the corresponding F value is posetive, all this in order to maximize the sum of revenues (cell G10)

So you could write the revenue calculation as

Please Login or Register  to view this content.

and still get the same result. If on the other hand you write the the calculation results as

Please Login or Register  to view this content.

then you allow Solver to use only posetive values in the range D2:D8 and and you total revenues goes up to 1245,12.

There are two small glitchs in you model, cells to change is set to range D2:D9 but since changing D9 will not affect revenues, range could be set to D2:D8. And the same change could be done in cell G10 i.e. "=SUM(G2:G8)

Alf