large application of a lot of arithmetic to order rows

[ftngrave]

Attached I have a spread sheet of 99 rows, or events, that have start and end times (all in military time).

Let me try to very carefully explain what I would like to do. The events are already listed from smallest amount of time from start to end to largest amount of time from start to end (or should be, I'm actually currently a total excel amateur). The first event is in row 2. I would like to take the difference in time from row 2's starting time with EACH AND EVERY other rows' starting times. Then I would like to take the difference in time from row 2's ending time with EACH AND EVERY other rows' ending times. Then row 3's differences would be added/summed together, then row 4's, and so on to row 100. I would then like the row with the smallest sum to be the next event after row 2. Then I want to repeat the process, so now we have row 2 and 3 established, so row 4 is now "compared" (or has the formula described above applied) to all rows except row 2 and 3. Then row 5 to all rows but 2, 3, and 4.

That explanation might simply not be clear enough, so let me illustrate what I'd like to do with rows 2-99 but acting as if we only have rows 4, 6, 7, and 9 as the complete list of start and end times.

row 4-5:05 to 9:15
row 6-4:40 to 9:25
row 7-5:15-10:05
row 9-4:50-9:45

so row 4, like row 2 is listed first because it encompasses the least overall amount of time from start to end. so to decipher which row should be "ordered" next we do this:
comparing rows 4 and 6: difference between starting times is 25 minutes, difference between ending times is 10 minutes, sum is 35 minutes
comparing rows 4 and 7: difference between starting times is 10 minutes, difference between ending times is 50 minutes, so sum is 60 minutes
comparing rows 4 and 9: difference between starting times is 15 minutes, difference between ending times is 30 minutes, so sum is 45 minutes

row 6 has the least sum of 35 minutes so it would be ordered next. so now we have row 4 and 6 ordered.

comparing rows 6 and 7: difference between starting times is 35 minutes, difference between ending times is 40 minutes, so sum is 75 minutes
comparing rows 6 and 9: difference between starting times is 10 minutes, difference between ending times is 20 minutes, so sum is 30 minutes

row 9 has the least sum so it would be ordered next. so we end up with ordered rows of 4,6,9,7.

to try and describe it conceptually, i want the events listed from smallest amount of time to largest, but I want the start and end times from row to row to be as close to each other as possible.

if anyone could help me on this it'd be greatly appreciated, doing this by hand would take some time. also, if someone could explain "how" to implement the formula in excel that'd be great as i haven't yet used formulas in excel, i've just used it for entering data. but even with the right code i think i can figure out the basics of applying the formula.

thanks.

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[zbor]

Can you write in your example desired ouput?

[ftngrave]

I would like to order the 99 start and end times. The first row in the list would be the one with the shortest time span from start
to end out of the 99. Then the next row, or start and end time, in the list will be the one with the smallest sum of differences withthe start and end times of the first row in the list. And so on.

Starting with the event or row that has the shortest time range from start to end, I then want to order the start and end times where the next event is the smallest possible amount of time difference taking its start and end times in comparison to the event before it.

It's hard to explain in words. But my original post uses as example 4 rows and does the computations desired to order the rows. I want to do exactly what the example does but with 99 rows.

Thanks.