Desperate for help in travelling salesman problem!

[krzys_28]

I have an incredibly difficult travelling salesman problem to work out.

I need help writing a formula that will accomplish the following:

sum from row 1 to row ? until the sum does not exceed 1100, then star
summing from the next row until it does not exceed 1100, etc. Ideally
would like to assign a series number to each of those sets. For example
the first set of rows whose sum <=1100 would be assigned a '1', and the
the next set a '2', and so forth.

The attachment might help to illustrate. See the column in bold..
right now it is set up manually, but i need a formula i can copy dow
so when i optimize with solver it will all work out.

thanks-

 

[daddylonglegs]

Can't see any attachment but if your numbers are in column A perhaps yo
can use this formula in row 1 copied down

=CEILING(SUM(A$1:A1)/1100,1

 

[krzys_28]

Thanks for the function suggestion... It doesn't quite work. I'll
actually attach the sheet this time. I somehow forgot in my last post.


-k


+-------------------------------------------------------------------+
|Filename: help.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4682 |
+-------------------------------------------------------------------+

 

[krzys_28]

Thanks for the function suggestion... It doesn't quite work. I'll
actually attach the sheet this time. I somehow forgot in my last post.


-k


+-------------------------------------------------------------------+
|Filename: help.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4683 |
+-------------------------------------------------------------------+

 

[daddylonglegs]

To get totals try this formula in G37 copied down

=IF(G36+D37>1100,0,G36)+D37

[note: G36 should be blank or zero]

for series number put a 1 in H37 then this formula in H38 copied down

=H37+(G38<G37

 

[krzys_28]

that was so simple! Thank you! I think I had thought of something lik
that before, but I wound up getting a circular reference. Now I jus
need to figure out how to get my damned truck back to Washington eac
time I am done with a series.

Thanks again!

-