Excel solver, variable cell to change adjusts data set

Question

I am new to excel solver and I'm trying use it for what I think fairly simple task but it just won't work.

I have 3 columns as follows:

%CHNG   |   PnL  |  Criteria met
  1     |    1   |      1         
 .5     |    2   |      2         
  2     |   -1   |      -1         
  3     |    6   |      6         
 -1     |   .5   |                
-.2     |   -5   |

and the following 2 cells:

Criteria:   0

Total: =sum of criteria met column

When the number in the 'criteria' cell is changed the value in the 'criteria met' column is posted from the PnL column only if the value in the '%CHNG' column is greater than the value in the 'criteria' cell using an if statement.

The 'total' cell is then the sum of the 'Criteria met' column. (The PnL values that are greater than the criteria value)

I am trying to get solver to maximize the 'Total' cell, by changing the value in the 'criteria' cell, however everytime I try this it leaves the criteria cell unchanged and claims a solution has been found. No matter what value I initially have in the criteria cell the solver claims that is the solution when it is run.

Is there any way to get this to work? preferably without VBA

Thank you

xidgel · Accepted Answer · 2016-03-10 18:07:46Z

1

A way to do this without Solver: With your %CHNG and PnL data in A2:B7, in D2 use this formula to create a score if Criteria equals A2:

=SUM(IF($A$2:$A$7>A2,$B$2:$B$7,0))

(entered as an array formula: CTRL-SHIFT-ENTER). Copy/paste that formula into D3:D7 to get a score for the other %CHNG values. For this set of data, this gives a max score of 8 when %CHNG is -0.2.

A formula to get the max of all the scores is:

=MAX(D2:D7)

And a formula to get the %CHNG value associated with the max is:

=INDEX(A2:A7,MATCH(MAX(D2:D7),D2:D7,0))

Hope that helps.

answered Mar 10, 2016 at 18:07

xidgel

3,1452 gold badges15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

crams Over a year ago

Thanks this worked great, is there a way to get more advanced using this approach. IE. if I had another column other than %CHNG labeled distance, and I wanted to still find the max PnL but by creating a rule for both %CHNG and Distance. Instead of simply PnL is maximized when %CHNG is >-0.2 in the first example, it could give a range; profit is maximized when %CHNG is between -.2 to 1.5 and distance is between -1 and 0. something like that?

xidgel Over a year ago

I posted a new answer that provides the range/distance functionality.

kindoflost · Accepted Answer · 2016-03-10 18:25:31Z

1

If you want to do it with Solver, then use the "Evolutionary" Solver and not the default "Simplex LP" Solver.
And cell C2 should be
=IF(A2>=criteria,B2,"")
etc
If the Solver wants the variables bounded just limit "criteria" to between -100 and 100.
HTH

answered Mar 10, 2016 at 18:25

kindoflost

911 gold badge1 silver badge7 bronze badges

Comments

xidgel · Accepted Answer · 2016-03-10 21:16:41Z

1

Here's a way to get the maximum over a range. This is called the Maximum Subarray Problem see here.

As before I assume that your %CHNG and PnL data in A2:B7. First you'll need to sort the data based on %CHNG. Column D will hold "maximum ending here" and column E will hold "maximum so far". We'll also have "First" in column F and "Last" in column G --- these are the first and last values of %CHNG that correspond to the maximum sum.

In D2 through G2 enter these formulas:

=B2
=B2
=A2
=A2

Now in D3 through G3 enter these formulas:

=IF(D2+B3>=B3,D2+B3,B3)
=MAX(D3,E2)
=IF(D2+B3>=B3,F2,A3)
=IF(E3=D3,A3,G2)

Copy/paste those formulas down to the end of your data. Cell E7 gives the maximum sum. Cells F7 and G7 give the values of %CHNG over which PnL is summed to get the maximum sum.

Hope this helps.

answered Mar 10, 2016 at 21:16

xidgel

3,1452 gold badges15 silver badges23 bronze badges

1 Comment

crams Over a year ago

hmmm the data comes in live based on time so i dont think i would be able to sort high to low. My goal was to be able to open the spreadsheet and have the formula automatically pick out the optimal range for %CHNG to maximize PnL based on the incoming data. im not sure if this is possible or not

Collectives™ on Stack Overflow

Excel solver, variable cell to change adjusts data set

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related