software

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 lt;=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: www.excelforum.com/member.php...oamp;userid=33759
View this thread: 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: 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: www.excelforum.com/attachment.php?postid=4682 |
-------------------------------------------------------------------

--
krzys_28
------------------------------------------------------------------------
krzys_28's Profile: www.excelforum.com/member.php...oamp;userid=33759
View this thread: 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: www.excelforum.com/attachment.php?postid=4683 |
-------------------------------------------------------------------

--
krzys_28
------------------------------------------------------------------------
krzys_28's Profile: www.excelforum.com/member.php...oamp;userid=33759
View this thread: www.excelforum.com/showthread...hreadid=535325
To get totals try this formula in G37 copied down

=IF(G36 D37gt;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 (G38lt;G37)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: 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: www.excelforum.com/member.php...oamp;userid=33759
View this thread: www.excelforum.com/showthread...hreadid=535325