Solver

[comradarthur]

Solver and Drop down menus problems

I'm trying to use Solver to Optimize a simulator I built in excel. The target cell is Market Share and the adjustable cells are drop down menu links. In other words, the drop down boxes change the price of a product and that affects the share. The issue I'm running into is solver goes through fractions even if I constrain it to be intergers and since the values can only be 1-7, I get errors for my values and solver stops working.

I tried including an iferror statement so errors wouldn't appear, but it doesn't seem to help.

I would really appreciate help on this issue. I'm hoping it doesn't involve vba code because I'm not too familiar with it.

thanks.

 

[Oaktree]

Re: Solver and Drop down menus problems

Welcome to the board.

In addition to setting an integer criterion, you'll want (need) to change the Tolerance to 0% within the solver options to ensure an integer value.

 

[comradarthur]

Thanks so much for responding. I tried what you suggested and it didn't resolve the problem. the issue seems to be that excel still goes through other values and when it does it gets an error message because my formulas can't handle the fractions. I tried adding iserror formulas to prevent errors and its kind of working so far. I'll respond again if it works.
Thanks again.

 

[comradarthur]

Nevermind, that doesn't work either, even when the tolerance is at 0, I still got a solution where ".999999999" was in one of the changed cells, which gave me an error.

 

[Oaktree]

It's hard to know without seeing your model setup, but I'm guessing that you're seeing 0.999999 instead of 1 because of floating point arithmetic.

What are the formulas you're using that can't handle the fractions? Since floating point errors are a limitation of Excel, it will be better to adjust your formulas... something like =IF(ROUND(A1,2)=1, do_some_stuff, do_other_stuff) instead of =IF(A1=1, do_some_stuff, do_other_stuff)

 

[comradarthur]

The target cell is just an average which creates a share, the changing cells are links to drop down boxes, which I use to Hlookup columns of data, so when excel inputs .999999 the hlookup doesn't work and the whole thing falls apart.