EDIT #2 5/17/2020: I re-phrased my question once more. My original question is still at the end. Thanks for the feedback.
I want to know if it's possible to maximize the sum of cumulative distribution functions for independent normal distributions in Excel using OpenSolver.
$$\text{maximize } \Phi(\sum_i x_ip_i) + \Phi(\sum_i x_iq_i)$$ $$\text{ s.t.} \sum_ix_i=3 $$ $$x_i=1 \text{ or } 0$$
where $\Phi(\cdot)$ is the standard normal cdf (or NORM.DIST
in Excel).
ORIGINAL:
Here is a snapshot of the workbook: https://i.stack.imgur.com/QqIKC.jpg
Here are the formulas: https://i.stack.imgur.com/4x6X0.jpg
I'd like to maximize a cell that is the sum of other cells that are not linear. I have Z scores for categories I and II (I hardcoded in a mean of 30, and stdev of 15 for category I and 40 and 20 for category II).
I want to maximize the sum of their normdist values in Row 13 (G13 and H13). Clearly, solver won't find a linear solution since the normdist function isn't linear. I have heard there was a way to get around this by doing piecewise-linear approximation.
Other notes:
- Columns B and C contains raw data - no formulas
- Column E is made up of 1s and 0s that are the variable cells determined by Solver
- G2:H6 will return the raw data found in columns B and C if column E displays a 1
- The sum of E2:E6 must equal 3
- This is very scaled down from the real problem I'm trying to solve, so no brute force methods will suffice.