Getting maximum values from a set of numbers in a ratio

Question

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...

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.

Answer 1

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.

Answer 2

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...