Solving cell range based on multiple criteria

Hello everyone,

My problem is as it follows;

Enclosed herewith is a table with a simplified example calculation for certain products.

Those products undergo 3 invoicing stages, so at the end of each stage there's a certain amount of profit.

What I need to do when creating such analysis is to calculate those profits (Profit 1,2 & 3) for the entire range of products.

So, to bypass manually inputting every discount percentage and balancing profits, I would need a tool (something like solver) trough which I could calculate discounts for an entire product range based on different criteria (i.e. Profit 1 should not exceed value xxx, Profit 2 should not exceed value xxx, and Profit 3 should not be less then xxxxx).

I haven't done any coding myself, so any kind of help towards solving this problem would be much appreciated.

Thank you,
Best regards.

One way of setting up solver and running it in a loop.

In order to test this you need to set a reference to solver in Visual Basic.

On "Developers" tab click "Visual Basic" icon, then "Tools" and finally "References" and find and tick box for "Solver". See picture.

solv_ref.jpg

To test run macro "SolvLoop"

I've specified one value for profit 1, 2 and 3. It is of course possible to specify a new profit min / max for each row in the table.

Alf

Hi Alf,

Thank you for your quick reply.

I've tried to test run your loop, but unfortunately I keep getting Compile Error as shown in picture.

Untitled-1.jpg

Solver was already ticked in the VBA References. Any idea what the problem might be?

Thank you.

Sorry my bad. What I gave you is a solution that works foe Excel 2010 and higher. In these versions of Excel you do have 3 different engines. The GRG Non-linear (engine 1), the Simplex Lp (engine 2) and finally the Evolutionary (engine 3).

As you are running Excel 2007 were the models are Simplex and GRG you specify what engine solver should use a bit different that's why the macro can't cope with "Engine:=2"

You can use a macro like this instead should work both for Excel 2003 and 2007.

Please Login or Register  to view this content.

In this macro solving method is set by "AssumeLinear:=True" i.e. solver will use the simplex model in order to solve the problem. To use the GRG engine one sets "AssumeLinear:=False"

Macro assumes same layout on the excel file as the one I uploaded.

Alf

Ah yes, I understand.

I tried the suggested macro and it works like a charm.

Thank you very much for your kind help.

As far as I'm concerned, this thread is now SOLVED.

Best regards.

You are welcome.

Thanks for feedback and rep

As most of the commands in SolverOptions are standard setting (default values) for solver this line could be simplified to

Please Login or Register  to view this content.

If on the other hand your solver setup should require specific settings set by choosing specific options this should be present in a solver macro.

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved