1
$\begingroup$

I need to keep a set of five elements [a:b:c:d:e] in a ratio of [2:1:3:2:1].

I have a collection of [2035, 1050, 3060, 2040, 1027].

I need to take maximum amount of the elements according to ratio and keep the rest separately.

ELEMENT RATIO TOTAL Collection RATIO WISE QTY REMAINDER
a 2 2035 ? ?
b 1 1050 ? ?
c 3 3060 ? ?
d 2 2040 ? ?
e 1 1027 ? ?

I do it this way... I do it this way

ELEMENT RATIO TOTAL COLLECTION RATIO WISE QTY REMAINDER
a 2 2035 2034 1
b 1 1050 1017 33
c 3 3060 3051 9
d 2 2040 2034 6
e 1 1027 1017 10

How can I build an algorithm to get the last two column value from the first three columns.

New contributor
Amin is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
$\endgroup$
2
  • $\begingroup$ Welcome to MSE. What did you try? $\endgroup$ Jun 23 at 10:14
  • $\begingroup$ I have edited and added screenshot how I tried it. But I'm trying to make a very precised algorithm/formula. $\endgroup$
    – Amin
    Jun 23 at 10:35

2 Answers 2

0
$\begingroup$

I think (until some one with a better idea comes along) - you essentially have an optimisation problem. To solve this in a naive way, would be to solve with a binary search, where you choose an upper boundary

l = #some lower bound
r = 1050 + 1 # the unitary value e.g. ratio = 1 max value 
min_diff = # some sufficiently large number
while l <= r:
   mid = (l + r) //2
   cur_diff = condition(mid)
   if cur_diff < min_diff:
      min_diff = cur_diff
      best_unitary_value = mid
      l += 1
   else:
      r -= 1

where best_unitary_value will then be equivalent to 1017

the condition could look like

def condition(mid, ratios, totals):
   diff = 0
   for _, ratio in ratios:
       diff += (total - ratio * mid) 
       if ratio * mid > total:
           # this ensures we are larger than any other value
           return float('inf')
       
   return diff
   

If we have lots of values, then you can use regression with a condition on the output.

$\endgroup$
0
$\begingroup$

I've found an easier solution:

  1. We divide Col C by Col B and ignore the decimal points and store the FLOOR value into Col D
  2. We find the minimum value from the values in Col D and store it into Col E.
  3. We multiply Col E with Col B and store it into Col F which is our expected value.
  4. We deduct Col F from Col C and store it into Col G and this is our another expected value.

Thus we get rid of long-stairs calculation and find solution in a easy way.

A B C D=FLOOR(C/B) E=MIN([D]) F=E*B G=C-F
a 2 2035 1017 2034 1
b 1 1050 1050 MIN(D1:D5) 1017 33
c 3 3060 1020 1017 3051 9
d 2 2040 1020 2034 6
e 1 1027 1027 1017 10

Still searching for more easier solution if any...

New contributor
Amin is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
$\endgroup$

Your Answer

Amin is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.