Im trying to make a recipe card (for my bespoke paints) which will allow me to change to quantity of any of 10 ingredients and automagically reduce the amount of other ingredients such that the total never exceeds 1000mL?
Here is a shot at it.
Row 1 is a header. Rows 12 is a footer.
Column B is the original mix. While doubtful you'd mix every color (yuck), this was just a test.
Column C you enter new values for the ones you want to change/set manually. The others are blank/empty.
Column D adjusts all the others so it adds to 1000 (actually it is to whatever the value is in footer cell B12)
B13 =SUM(B)
fill right to column D
D2=IF(C2,C2,B2×(B$12−C$12)÷SUMIF(C,"",B))
fill down
If your precision for each color is to a whole ml, it may not be possible to get exactly 1000 ml as the total. For instance, if I round the values in column D to whole numbers they sum to 1001. There are actually a lot more decimal places I'm not showing. If it must be to the exact 1000 ml, it may take another column or more and selectively (randomly perhaps) choosing which one(s) to add or subtract 1 or more ml to so the total is exact.
So cool. Thank you, this seems pretty close. I'll experiment and let you know how it goes.
Numbers for Mac/ Recipe Formula
