Help my theoretical trains not crash.
April 6, 2011 6:51 PM Subscribe
I have a rather specific excel question. Please help me.
I'm fairly good at excel, but something I'm working on is stumping me. I think I found a way to do it, but I can't get rid of the idea that there must be an easier and more elegant way.
Let's say I'm scheduling trains. Well, because I am. I'm scheduling trains. I have 12 service patterns. They are in a row across the top. My cities are in a column along the left. I'm going to pull data about which train reaches which city at which time from a model I made that calculates the times based on a start time I give it.
I want to have a formula that makes a cell yell at me when my trains are in danger of colliding at a station. Basically, I need a formula that compares a series of numbers amongst itself and lets me know if any of them are too close together. Then, when I see that cell say "BOOM!" or "CRASH!" or, to be boring, "FALSE," I can go tweak my start time(s). This is an incremental process.
I thought of using an OR statement and the ABS function for every permutation. Like =OR(ABS(B3-C3)>3,ABS(B3-D3)>3,ABS(B3-E3)>3,ABS(C3-D3)>3) and so on and so on and so on until every pair was compared, but before I sit here and type it all out, I wanted to see if perhaps there was a better way.
I'm fairly good at excel, but something I'm working on is stumping me. I think I found a way to do it, but I can't get rid of the idea that there must be an easier and more elegant way.
Let's say I'm scheduling trains. Well, because I am. I'm scheduling trains. I have 12 service patterns. They are in a row across the top. My cities are in a column along the left. I'm going to pull data about which train reaches which city at which time from a model I made that calculates the times based on a start time I give it.
I want to have a formula that makes a cell yell at me when my trains are in danger of colliding at a station. Basically, I need a formula that compares a series of numbers amongst itself and lets me know if any of them are too close together. Then, when I see that cell say "BOOM!" or "CRASH!" or, to be boring, "FALSE," I can go tweak my start time(s). This is an incremental process.
I thought of using an OR statement and the ABS function for every permutation. Like =OR(ABS(B3-C3)>3,ABS(B3-D3)>3,ABS(B3-E3)>3,ABS(C3-D3)>3) and so on and so on and so on until every pair was compared, but before I sit here and type it all out, I wanted to see if perhaps there was a better way.
I would make a VBA function that accepts lookup, range and threshold. Loop through each element in the range and return TRUE if a range element is too close to the lookup value. Then use conditional formatting to get that cadmium red alert.
posted by michaelh at 8:03 PM on April 6, 2011
posted by michaelh at 8:03 PM on April 6, 2011
What's a "service pattern"? I'm having difficulty picturing what your data actually looks like? Can you post a sample table or a Google doc or something?
posted by mhum at 8:04 PM on April 6, 2011
posted by mhum at 8:04 PM on April 6, 2011
Not the most elegant or efficient way, but probably the simplest:
Say you have a table of values representing your cities and arrival times
Create a new table for each row to compute all time differences. For row 1 above, start by creating a column of values referring to each value (transpose the row):
Then type in the formulas to compute the differences for the "a" row:
Where (a) means a reference to the "a" value i.e. A2, and $a means a reference with the row fixed i.e. A$2.
Then drag down the top row to generate the rest of the values:
Repeat this whole process for each row of initial table. The second row of the table would be handled:
To clarify the values, use conditional formatting (look up in Help) to highlight values within your desired range.
(I do hope I understood your initial problem correctly.)
posted by mnemonic at 8:10 PM on April 6, 2011
Say you have a table of values representing your cities and arrival times
a b c
d e fCreate a new table for each row to compute all time differences. For row 1 above, start by creating a column of values referring to each value (transpose the row):
a
b
cThen type in the formulas to compute the differences for the "a" row:
a (a)-$a (a)-$b (a)-$c
b
cWhere (a) means a reference to the "a" value i.e. A2, and $a means a reference with the row fixed i.e. A$2.
Then drag down the top row to generate the rest of the values:
a (a)-$a (a)-$b (a)-$c
b (b)-$a (b)-$b (b)-$c
c (c)-$a (c)-$b (c)-$cRepeat this whole process for each row of initial table. The second row of the table would be handled:
d (d)-$d (d)-$e (d)-$f
e (e)-$d (e)-$e (e)-$f
f (f)-$d (f)-$e (f)-$fTo clarify the values, use conditional formatting (look up in Help) to highlight values within your desired range.
(I do hope I understood your initial problem correctly.)
posted by mnemonic at 8:10 PM on April 6, 2011
So you have a row of values and you want to see if any two of the values are within a given threshold of each other.
Programmatically I would sort and compare neighbors. Unfortunately, Excel doesn't do autosort. (It might do it with its blasted list management mode, but I don't know.) I don't see any better way than the ABS route.
posted by fleacircus at 8:43 PM on April 6, 2011
Programmatically I would sort and compare neighbors. Unfortunately, Excel doesn't do autosort. (It might do it with its blasted list management mode, but I don't know.) I don't see any better way than the ABS route.
posted by fleacircus at 8:43 PM on April 6, 2011
Best answer: What you need is an array formula.
Basically, you set up a secondary table derived from your original table. Each cell in the secondary table has an array formula that returns twelve values, representing the twelve differences between the time in that cell from the original table and the time in the other cells of that row. One of these values will always be zero, so we can't just take the smallest value. Instead we use the SMALL() function to give us the second-smallest value in that array.
So set up your secondary table with the same headings. I'll presume it starts in row 20. Put the following formula in the first results cell and when you finish typing it press ctrl-shift-enter:
=SMALL(ABS(B20-$B20:$M20),2)
Once you've entered it correctly as an array formula, copy that cell to the other cells in the secondary table.
Now we have a secondary table where each city's row has cells containing the smallest difference between the time for that service pattern and all the other service patterns. Use Excel's MIN() function to give you the smallest time difference and IF() to tell you when the smallest time difference is too short.
posted by Joe in Australia at 11:54 PM on April 6, 2011 [1 favorite]
Basically, you set up a secondary table derived from your original table. Each cell in the secondary table has an array formula that returns twelve values, representing the twelve differences between the time in that cell from the original table and the time in the other cells of that row. One of these values will always be zero, so we can't just take the smallest value. Instead we use the SMALL() function to give us the second-smallest value in that array.
So set up your secondary table with the same headings. I'll presume it starts in row 20. Put the following formula in the first results cell and when you finish typing it press ctrl-shift-enter:
=SMALL(ABS(B20-$B20:$M20),2)
Once you've entered it correctly as an array formula, copy that cell to the other cells in the secondary table.
Now we have a secondary table where each city's row has cells containing the smallest difference between the time for that service pattern and all the other service patterns. Use Excel's MIN() function to give you the smallest time difference and IF() to tell you when the smallest time difference is too short.
posted by Joe in Australia at 11:54 PM on April 6, 2011 [1 favorite]
Argh. That formula should be: =SMALL(ABS(B2-$B2:$M2),2) because it's copying stuff from your original table, the one where the values of the first row appear in cells b2:m2. Sorry.
posted by Joe in Australia at 5:12 AM on April 7, 2011
posted by Joe in Australia at 5:12 AM on April 7, 2011
If you have a series of numbers, you can create an automatic sort function as described here:
http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/
Now your values are (a) sorted and (b) indexed. So your original data might look like this:
10:25
10:03
10:01
16:54
14:33
After sort, you will have some additional columns:
Next, you should use a formula to compare every two consecutive rows to determine the delta:
And now you see how you can make a cell yell: if a Delta cell under X minutes, check the corresponding sort order (2 in this case), and cause Excel to turn that cell angry.
The exact functions to do the above are left as an excelsize for the student :)
posted by babbageboole at 5:54 AM on April 7, 2011
http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/
Now your values are (a) sorted and (b) indexed. So your original data might look like this:
10:25
10:03
10:01
16:54
14:33
After sort, you will have some additional columns:
Data Sort Order Sorted 10:25 3 10:01 10:03 2 10:03 10:01 1 10:25 16:54 5 14:33 14:33 4 16:54
Next, you should use a formula to compare every two consecutive rows to determine the delta:
Data Sort Order Sorted Delta 10:25 3 10:01 10:03 2 10:03 0:02 10:01 1 10:25 0:22 16:54 5 14:33 4:08 14:33 4 16:54 2:21
And now you see how you can make a cell yell: if a Delta cell under X minutes, check the corresponding sort order (2 in this case), and cause Excel to turn that cell angry.
The exact functions to do the above are left as an excelsize for the student :)
posted by babbageboole at 5:54 AM on April 7, 2011
Response by poster: joe in australia's idea works the best for my particular data set! thank you!
posted by millipede at 9:05 AM on April 7, 2011
posted by millipede at 9:05 AM on April 7, 2011
This thread is closed to new comments.
posted by slavlin at 7:52 PM on April 6, 2011