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

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.
• Welcome to MSE. What did you try? Jun 23 at 10:14
• I have edited and added screenshot how I tried it. But I'm trying to make a very precised algorithm/formula.
– Amin
Jun 23 at 10:35

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.

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.