Forum Discussion
Excel formula for finding a Minimum input based on two variables
- Sep 11, 2017
Hi Kent,
You may try Solver add-in
https://support.office.com/en-us/article/Load-the-Solver-Add-in-612926fc-d53b-46b4-872c-e24772f078ca
In your case it returns:
Hi Kent,
You may try Solver add-in
https://support.office.com/en-us/article/Load-the-Solver-Add-in-612926fc-d53b-46b4-872c-e24772f078ca
In your case it returns:
- Kent O'DonohueSep 13, 2017Copper Contributor
Ok so I set up the solver and it worked really well. However I made one tweak and ran into a snag. So I need the smaller whole number size to be priority. I thought I fixed this by making L10 >= 0 instead of 1. This allowed the solver say ok L10 can be 0, but L11 cannot be less then 0, therfore tricking it to give me the minimum number of whole number scenarios with 190 always being a factor.
2. Unfortunatley there are some scenarios where the L11 (500) may be used alone. This would be the case only when 190 is not divisible within 10% but 500 is for example when J10=510. In this scenario 190 does not go within 10% of 510 but 500 does. So hear we would want to use 1 500 but 0 190s.
3. However, when J10 is 525 lets say, it is divisible within 10% of (1) 500 or (3) 190s. In this case we would want the 3 190s because it is the only way to get atleast 1 of the smaller number (190). Equally if J10=575 then we get (1) 500 and (1) 190. This works because we are minimizing the total whole numbers AND we have atleast 1 190.
So basically what I am saying is the solver solution works in almost all cases when we say atleast 1 190 must be used but 0 500s can be used. The only time that is does not work is when 190 is not divisble within 10% of J10 but 500 is.
Sorry about the long response but I kind of had to talk my way through it.
Any ideas?
Thanks
- Kent O'DonohueSep 15, 2017Copper Contributor
Ok I got it. In L17 I put in the formula that IF L13 is >10% then check to see if K10 is within 10% and if it is then round to the nearest whole number of that integer. With the Solver giving me the minimum scenario with a priority to the lower whole number as long as it is within 10% of that lowest whole number, then L17 just runs a check on only the larger whole number if solver doesnt find a solution.
Thanks for your help! This was very useful!
- Kent O'DonohueSep 12, 2017Copper ContributorI was trying to do it in formula form as the users of this equation are not excel savy so I was trying to make it user friendly. However, considering the complexity of what I am trying to do my guess is using solver may be the only option.
Thanks for the help!