Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Comparison/Match Question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Comparison/Match Question
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. -- HTH... Jim Thomlinson "Mark" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Comparison/Match Question
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" wrote: 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. -- HTH... Jim Thomlinson "Mark" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Comparison/Match Question
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. -- HTH... Jim Thomlinson "Mark" wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "Mark" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Comparison/Match Question
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 = = = = = = Mark wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "Mark" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match formula question | Excel Worksheet Functions | |||
match formula question | Excel Worksheet Functions | |||
Match formula question | Excel Worksheet Functions | |||
Text comparison - closest match | Excel Discussion (Misc queries) | |||
Text string comparison - closest match | Excel Discussion (Misc queries) |