Solver error - Linearity condition not satisfied

The linear Solver can not work with CountIf and If functions. This is because these are discontinuous functions. Solver is not capable of determining a "finite difference" to help with a
derivative. Then there is also the division in formula F7 and G7.

The GRG Non-Linear engine may sometimes manage that and the evolutionary engine will find a solution in this case. But for a solution using the simplex engine you have to rebuild your mode.

Alf

Deleted my comments as my suggestion was made on a wrong assumption.

Alf

Originally Posted by tapaspanda1321

So the division in F7 and G7 might be causing the issue.

Assuming that c is a constant, a constraint like

a/b < c

can always be written in a linear form as

a < b*c

However, this workaround will not solve the COUNTIF problem. For that, you need an additional set of variables and constraints.

Let me know if the attached file works for you.

HTH,

Francesco

Hi Francesco, Thanks for your help and sorry for the delayed response. I didn't understand the use constraint you put in your file for column G. Was it necessary? What is it trying to do?. Also one of the condition was not as per the requirement. I will attach the sheet with corrected condition.

In your worksheet, the use of COUNTIF in cell E26 makes the model non-linear. This isn't bad in itself, but we must rely on GRG or Evolutionary engines if we want a solution, and we can't be sure it is the best one.

The constraints are needed only if you want to solve your problem using Simplex, because they will transform your model from non-linear to linear.
The trick is to first introduce a set of binary variables, so that we can use a simple (and linear) function as SUM in E26, and then add some constraints that will switch the variables to 1 as soon as the Funding is > 0.

This choice has several advantages: we are sure that the found solution (if it exists) is optimal, solving time is very small, and even the standard Solver engine can return a result. In the file ver.2 I have updated the conditions, and the answer looks reasonable, but feel free to test it with different inputs. Please note, however, that there may be no solution to your problem if Funding needs is set too low.

We can also decide to keep the non-linearities, and multiply binary and continuos variables by each other in every row (the funding is > 0 only when the associated binary var is 1), but then we need a better engine than the standard ones bundled with Excel. This can be found in OpenSolver, a free Add-In you can download here. If you decide to try it, get the advanced version. In file ver.3 I have already setup the engine for this problem (Couenne), and as you can easily test, the result is the same as ver.2.

HTH,

Francesco