Hi
I have a question that I’m going to find difficult to explain in a few words, so please persevere and be patient if you're good at problem solving.
The issue is this:
I have parcels that I send out every day - on average about 600. The parcel weights vary greatly. Some are 20 grams, others are 2kg.
Each item to be sent is in column A, and its weight is in column B.
The couriers I use charge in price bands by average weight per sack. For example: If the average weight of the contents of one sack is, say, 99 grams, then all products in that sack are charged at the 100g price band. But if the average weight is 101 grams, then all items are charged at the next band up (which in this case is 250g, the next band again is 500g, then 750 and so on).
Also, the bags hold a maximum weight of 8kg.
Is there a formula or macro or solver of some sort that can tell me the best configuration to get the cheapest overall price? Next to each item it would place (in column C) a bag number to sort that parcel into.
My IT team has so far drawn a blank because we are sending some sacks out with an average weight of, say, 140g, then another at 350g.
It would work out cheaper for us if some of the items in the latter sack were placed in the former sack until its weight was close to 250g.
The weight of the latter sack would go up - but we have 150g to play with before the price per item increases, so it's not so important.
Hopefully someone can give me an idea.
Thanks
I have a question that I’m going to find difficult to explain in a few words, so please persevere and be patient if you're good at problem solving.
The issue is this:
I have parcels that I send out every day - on average about 600. The parcel weights vary greatly. Some are 20 grams, others are 2kg.
Each item to be sent is in column A, and its weight is in column B.
The couriers I use charge in price bands by average weight per sack. For example: If the average weight of the contents of one sack is, say, 99 grams, then all products in that sack are charged at the 100g price band. But if the average weight is 101 grams, then all items are charged at the next band up (which in this case is 250g, the next band again is 500g, then 750 and so on).
Also, the bags hold a maximum weight of 8kg.
Is there a formula or macro or solver of some sort that can tell me the best configuration to get the cheapest overall price? Next to each item it would place (in column C) a bag number to sort that parcel into.
My IT team has so far drawn a blank because we are sending some sacks out with an average weight of, say, 140g, then another at 350g.
It would work out cheaper for us if some of the items in the latter sack were placed in the former sack until its weight was close to 250g.
The weight of the latter sack would go up - but we have 150g to play with before the price per item increases, so it's not so important.
Hopefully someone can give me an idea.
Thanks
