Formula/Comparison/Match Question

[Mark]

Spreadsheet with 2 columns, first has site name, second has time taken to
make a delivery to that site (In decimal)
So for instance
A1 = London B1 = 2.10
A2 = Birmingham B2 = 4.60
A3 = Edinburgh B3 = 6.70

etc,etc

Is it possible to build a formula that will take the data in column B and
work out which deliveries will go together to form a working day of no more
than 11.00 hours?

Mark

 

[Jim Thomlinson]

So in the grander scheme of things you want to know which permutations and
combinations will add up to 11. Assuming that to be the case then the answer
depends on how many deliveries you have in a day and did you want to know all
of the different options. For example in this list of 20 delivery times:
4.8, 2.1, 3.9, 9.8, 4, 6.4, 3.2, 4.5, 3.6, 4.6, 9.5, 1.5, 0.5, 4.2, 7.5,
9.2, 9.9, 5.3, 9, 8.2

there are 10 possible solutions
6.40 4.60
9.50 1.50
4.20 3.60 3.20
5.30 4.20 1.50
5.30 3.60 2.10
9.00 1.50 0.50
4.20 3.20 2.10 1.50
4.50 3.90 2.10 0.50
4.80 4.20 1.50 0.50
4.80 3.60 2.10 0.50

As the list of deliveries gets larger the number of possible solutions grows
exponentially and the time to calculate goes up in kind. There is no formula
to work this out. It requires a macro.

 

[Mark]

Jim,

Yes, you are correct in that I have a large number of deliveries each day to
work on.
Where should I direct myself to find help on a macro for this?

Thanks for your help

 

[Jim Thomlinson]

The problem you will be running into is that large lists (anything over about
50) contain a staggering number of perutations and combinations. If you want
the full list the amount of time it will take to process is well beyond what
you are willing to wait. If you only want the first solution then we might be
able to do something for you. Even then it could potentailly take a very long
time to process for some solutions. There is some code created by Harlan
Grove that is being quite widely used but would need to be modified to stop
at the first solution.

 

[Dana DeLouis]

to form a working day of no more than 11.00 hours?

Just to mention another option... one technique is to pick the largest
value, and use Excel's Solver to pick the remaining values with the
constraint that the total does not exceed 11. One Maximizes the
solution just in case there are no "Subsets" that total 11.

The other technique that is often used in these problems (especially in
other programs) is to use an input of Integer minutes, instead of
fractional hours.

HTH
Dana DeLouis
= = = = = =