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 start
summing from the next row until it does not exceed 1100, etc. Ideally I
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 then
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 down
so when i optimize with solver it will all work out.

thanks-
k


--
krzys_28
------------------------------------------------------------------------
krzys_28's Profile: http://www.excelforum.com/member.php...o&userid=33759
View this thread: http://www.excelforum.com/showthread...hreadid=535325

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

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=535325

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
------------------------------------------------------------------------
krzys_28's Profile: http://www.excelforum.com/member.php...o&userid=33759
View this thread: http://www.excelforum.com/showthread...hreadid=535325

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 |
+-------------------------------------------------------------------+

--
krzys_28
------------------------------------------------------------------------
krzys_28's Profile: http://www.excelforum.com/member.php...o&userid=33759
View this thread: http://www.excelforum.com/showthread...hreadid=535325

To get totals try this formula in G37 copied down

=IF(G36+D371100,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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=535325

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

Thanks again!

-k


--
krzys_28
------------------------------------------------------------------------
krzys_28's Profile: http://www.excelforum.com/member.php...o&userid=33759
View this thread: http://www.excelforum.com/showthread...hreadid=535325