I need help developing a calculator that my co-workers can use to figure out how much weight of large, medium, and small pieces of jerky should go into a 10oz bag. This would be easy, 10 divided by 3 right? But the number of bags that can be filled would be informed by the total available weights of large and medium pieces, and 2/3rds of the bag must be filled with just large and medium pieces. The final 3rd being filled with just small pieces. If there are more large pieces, we should use more of them to make up the 2/3rds of the bag and if there is more medium pieces, they should make up most of the 2/3rds of the bag. Additionally, if there isn’t enough of the small pieces to fill 1/3rd of every bag, that means the total amount of large and smalls needs to increase to cover the small deficit.
Example being; There are 2500oz of large pieces, 1000oz of medium pieces, and 1500oz of small pieces. How many bags can we make with this batch. Also how much large and medium each should go in a bag. The goal being to maximize the number of bags being made with an equal distribution of large and medium pieces and have basically no large or medium pieces left over. It’s ok if there are small pieces left over.
P.S.: I am sorry if this is the wrong place for this question. I know it’s more of a mathematical question but I want to make it into an excel calculator that any of my co-workers can use to plug the numbers into and I am sorry if this is an easy question for other people, I’m just not smart enough.