Getting maximum values from a set of numbers in a ratio 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.
 A: 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.
A: I've found an easier solution:

*

*We divide Col C by Col B and ignore the decimal points and store the FLOOR value into Col D

*We find the minimum value from the values in Col D and store it into Col E.

*We multiply Col E with Col B and store it into Col F which is our expected value.

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