I am trying to solve for optimization through a series of binary criteria, and was thinking that maybe Solver could help? I can't wrap my head around how to make it work though. Maybe Solver isn't even the answer, I'm not sure :/
Capture.PNG
I am trying to solve for optimization through a series of binary criteria, and was thinking that maybe Solver could help? I can't wrap my head around how to make it work though. Maybe Solver isn't even the answer, I'm not sure :/
Capture.PNG
First let me comment on "ratio".
1:0 would be the same as 2:0 - both leading to infinity
But let's focus on difference rather than mathematical ratio
Shall 2:0 be treated as equaly good solution as 3:1 ? I assumed that 2:0 is better than 3:1 so in final goal setting (maximizing) i used
=C17-1,01*D17
where in row 17 are taken into account positive and negative results (meeting constraints)
As variable in solver I used integers -1;0;1 -1 meaning given constrain being ignored, 0 set to 0 and 1 set to 1.
Solver shall find one of best solutions either B,E,F for constr3=0 (and other not taken into account) or both constr3 and constr4 = 0 (and other not taken into account)
See attached. Solver Engine is set to evolutionary (I don't remember, but I think it could be already available in Excel 2007).
Best Regards,
Kaper
Ah nice work! Suuuper smart way of doing this, very nice!
You're right about the ratio thing too of course haha, and yes what you have provided is exactly what I was trying to accomplish.
I can't seem to get it to provide the most optimal solution on my older version of excel (perhaps because there is no option to enable an evolutionary mode), so I will have to figure something out, but that is beyond the scope of what can be helped with here haha.
Thanks and good work!
Of course with this number of constraints you can use a brute force approach and list all posiibilities. it's 3^4 - 1 so just 80:
0 0 0 0
1 0 0 0
-1 0 0 0
0 1 0 0
1 1 0 0
-1 1 0 0
0 -1 0 0
1 -1 0 0
etc.
See columns N:Q in sheet2.
Each cell N3:Q3 has similar but different formula. These formulas are copied down to list all 80 possibilities.
Then calculations from J3:J9 are made in columns R:X. It is one formula in R3 copied right and down. (I used OFFSET to use data from respective number of rows down)
What was calculated in C17:D17 and C19 is now in Y3:AA3 and copied down (here I had to use TRANSPOSE to made both arrays passed to SUMPRODUCT oriented the same way)
Conditional formatting is used to highlight best results (as previously noted -1 -1 0 0 and -1 -1 -1 0 give best results - 3 positive and 0 negative.
So we found best solutions without using solver in this sheet
Last edited by Kaper; 08-24-2021 at 05:40 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks