Excel formula for finding a Minimum input based on two variables

[Kent O'Donohue]

I have looked for a solution to this at no avail.

I have two variables that create number D31(15)*F18(94.98)=1424.7

Based on this answer I have two whole numbers multiples I can round this answer up or down to as long as the answer is within 10% of the whole number.  In this example the whole numbers are 190 and 500.  With these numbers 1424.7 is within 10% of multiples 190 and 500.  190 rounds down to 7 multiples at 1330 which the difference from 1424.7 and 1330 is 6.65% from 1424.7 so 1330 can be used.  500 rounds up to 3 multiples at 1500 which the difference from 1424.7 and 1500 is 5.29% from 1424.7 so 1500 can be used.

Currently I have the following formula to come up with the proper rounding to 1330 which puts a priority of rounding to the smaller whole number of 190, but it could easily be switched to prioritze 500 first: =IF((ABS(MROUND(D31*$F$18,190)-(D31*$F$18))/(D31*$F$18))<=0.1,MROUND(D31*$F$18,190),IF((ABS(MROUND(D31*$F$18,500)-(D31*$F$18))/(D31*$F$18))<=0.1,MROUND(D31*$F$18,500)*D31*$F$18))

The issue I have is this is wrong.  What I really need to do is say give me the smallest possible combination of 190 and 500 that is within 10% of the answer 1424.7 and if there are no combinations within 10% then just give me 1424.7 as the answer.  So in this example above the answer would actually be 1380 with two 500 multiples making 1000 and then two 190 multiples making 380 for a total of 1380.  This answer gives me the smallest possible combination of the two numbers while producing a number (1380) that is within 10% of the solution 1424.7.

Note: I am aware that three 500 multiples would actually produce a smaller multiple answer, but the reason that is not an acceptable solution is because the smaller number (190) needs to hold priority.  So if one 500 would work or two 190s then we would want to use 190 because it is better to stay under and in smaller intervals.  The issue is when you get othe bigger numbers like 1424.7 we do not want seven 190s when we could use two 500s and two 190s.

I have attached a file showing my current formula with the correct cells mentioned above.

I appreciate any help with this as this has racked my brain very much.  It may not be possible but I just thought I would give the Community a shot at it before I give up.

Thanks,

[#2 · Accepted solution]

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

https://support.office.com/en-us/article/Define-and-solve-a-problem-by-using-Solver-9ed03c9f-7caf-4d...

In your case it returns:

[#3]

I 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!

[#4]

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

[#5]

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!