Optimising sumifs with solver

[surfsoc]

Hi,

I am using excel 2007 and am trying to optimise a SUMIFS equation using solver.

I am trying to optimise the following ratio:

SUMIFS(y_values,x_values,$G$2,x_values,$G$3) / COUNTIFS(x_values,$G$2,x_values,$G$3)

where $G$2 is =">="&F2 and $G$3 is ="<="&F3.

F2 and F3 are cells that I am asking solver to change. Solver can't find a different solution than what is already in the cells, but says that it has found a solution.

I suspect that it is not working because sumifs is not a linear functions.

Does anyone know a solution to how i might be able to optimise a sumifs statement? I have a spreadsheet with this problem in it if someone needs me to upload it (you'll have to remind me how to do this again though).

Thanks,

Surfsoc

 

[pgc01]

Hi Surfsoc

If the Solver does not change the values that should be because it's a local minimum.

Try changing the initial values for the cells.

You usually know your problem and have an idea of what the result should be and may use it for initial value. In other cases, a table or a chart with input-output values may help.