Formula for restricted volumes

[SteveP100]

Hi all,

I am struggling to pull together a formula and hope someone out there can help.

I have three dispatch sites and a load of customer sites. I have all the mileages between the various points. I can choose which dispatch site is nearest to all the customer sites easily enough but what I am struggling with is if I have a limited volume of "stuff" at one dispatch site, how can I write a formula to choose the site based on the best site until it hits a certain limiting volume (that can be changed to change the profile) and then pick the second best site instead when it goes over that limit?

I also need to order it so that the site with the lowest mileage gets preference

Hope that makes sense?

[Richard Buttrey]

Hi and welcome to the forum.

Ahhh! The classic Travelling Salesman puzzle rises again.

Easily stated, not so easy to solve.

Have a look at this web site which addresses the subject. If you'd like to put together a workbook which contains your data and following the example that's discussed on page 99, then upload the workbook here we may be able to help further.