I'm interested...

And, just to make sure I do understand what it's doing, I'm going to ask the dumb question:

You set up columns A-D once (below line 4 at least), set column E before each session, and columns F & G are auto-generated to tell you want to put on your switchlist(s), correct?

Nice. Seems to dovetail well with Tom Driscoll's Simple Car Card system as seen in the current issue of MRH as well.

I too, am interested...

I think it could be a good basis for operations on my layout.

Yup. Before each session, set column E.

Only column you need to update before creating a switching list is column E.

Once you've generated the list, however, you need to manually add any cars "parked" during the last session  because the siding/spot was occupied and note (or let the crew discover) where a siding is slated to accept more cars (of various types) than the siding/spot can hold.

And yes, it is a way to generate the list for Tom Driscoll's system but anyone who wants to assign cars with specific markings can do it, as they assemble the train "off stage" by indicating the car marking(s) they're using to fill the specific order.  And for those who use individual waybills, can use the spreadsheet as the list of cars by type and use the waybill for the specific car they use to fill the order. 

The two real benefits that I see (particularly for those of us who use something akin to Tom's selection process) are the variety of scenarios and ease in creating the switch list. 

Google Docs Version is available

Okay, Google Docs Version (1.0) is available for download at:

https://docs.google.com/spreadsheet/ccc?key=0AmH3g4BYgRtgdFFmMXJWNnRjNjVSNE4tbXlKQXpaS2c&hl=en_US#gid=0

It's not really prettied up, but you can download it and pretty it up yourself (since it seems to lose some of the formatting options when downloaded as an .xls file, anyway).

I set the probabilities of an extra drop-off and delaying the pick-up to zero (meaning there will be no extra cars on a line and no cars not picked up) and work days/cycles to 5.  That number works in conjunction with the frequency figure on each line to determine probability a car will be dropped off.  Simply, it's the frequency divided by the number of days.  If frequency is equal (or greater) than number of workdays, the line will generate a car every time.

Note, if I did it correctly, you shouldn't be able to modify the online version, but you can download it and modify it to your hearts content it if you have Excel or the Open Office equivalent, or re-upload it in your it into your own Google Docs collection and play away.

Just remember the formulae are in columns F & G for all rows that are bordered (rows 6-45).   They aren't protected, but you might want to protect them on your copy as it won't be obvious the ones in G are missing. (It'll just look like there's no drop off to be made there this cycle).

You don't need to delete the formulae in any rows you don't use.  If there's no entry in both the Frequency and car type columns there will be no entry in the  Deliver column (although an entry in the frequency column but not the car type one *will* generate a "2 s" entry if multiple cars are to be delivered.

If you want to test to make sure your sheet isn't missing any formulae, just set the Days/Week on row 3 to zero.

I still welcome any suggestion for improvements.

Mike

(Added by edit:  For those using Excel who want the list to remain static until they decide to generate a new list, you can go to "Tools" and then "Options" and select the Calculate tab.  There you will find an option to recalculate manually - only when you select F9.   It's not only a good idea, it's absolutely necessary if if you want to add notes or car markings before printing the list.) 

Thanks Mike!

I downloaded the OpenOffice version. I'll play with it and see what happens.

Thanks again!

Ken, My pleasure.  Let me

Ken,

My pleasure.  Let me know how it works for you.

Mike

Very interesting...

I downloaded it (into OpenOffice) and filled in my industries and my ideas of what spots and how often they'd be serviced.

It is becoming apparent to me that if I actually want interesting sessions, I'd better starting having more cars per week delivered! Or run several days in one session. I've determined that, to start with, I have 27 car spots spread throughout 11 industries.

What is very neat about this is that it solves one of the problems I most feared - accidentally using the 4 cycle waybills in such a way that I'd end up with a dozen cars decending on an industry that had space for 3 of them.

This is something I can use as a base traffic generator - and then I can fill the appropriate car cards with the appropriate waybills as needed to match - while still incorporating the variety of interchange and "Hauler" or "Turn" options I have as well. This tells me how many cars I need to put where - I still get to decide how they get there - and in many cases, which ones.

In HO, I do care somewhat about the road names & numbers.

Likewise

I downloaded the Google sheet and sent it to Excel. Being an Ho modeler like Jeff, I also want to see my car numbers and vary them. Entering reporting marks for cars will be easy enough to do prior to each operating day. This will dovetail nicely with my car rotation system.

I also came to the same conclusion that Jeff did after I entered the industries for one town. This is a great traffic generator and will be used to determine how many cars the local will bring into town. Unlike Jeff my problem won't be to increase the number of cars for interest, but to adjust the frequency of each industry so that everything doesn't happen at once and plug up the yard. Should be fun to sort things out.

Roy

Options

There are a lot of things you can do with this to enhance it and change the look or adjust the operation.

One easy one is to go three of four columns over and create a switchlist form that looks like whatever switchlist form your favorite road used or to add your railroad's name slogan, whatever, at the top.  You can also change the font to whatever you want.  For example changing the font to look like a typewriter (Courier) or a dot-matrix printer (if you younger guys even know what that is ).  The point the cells in the custom form back to the original.  So if the first industry name is in cell A6 on the original form, and the first industry name on the custom form is cell K7, then in cell K7 you would type =A6.  Whatever is in cell A6 will automatically show up in K7.  Lastly, highlite the entire range of cells in the custom form and set that as a "print area".  When you are done, you can enter all the stuff in the stock form and then hit print and the custom form will print out.

Once you have disconnected the data entry and calculation area from the print area, other stuff is possible.  For example, adding columns for the two probabilities on the main form and entering a custom probability for spot and pull for each individual industry or track.

If you really want to go wild, use the power of the worksheets (those little tabs at the bottom of the screen labeled screen 1, screen 2, etc) to put the main screen on one worksheet and then a separate switchlist for each switch job on its own worksheet.  Fill out the data entry for the railroad and then select the worksheet for the job you want to run and it has only the industries you want it to service with the probabilities for that job.

Too Many Cars?

Whether this poses a "problem" depends largely on how you address it if/when it occurs.  It can happen with most car forwarding systems, and also affects the prototype.  One way to handle it is to use an "agent" (maybe you as layout owner) to designate cars for customer spot to simulate demand, leaving the rest in the yard or another designated track until they are cleared to spot.  Another possibility is to provide crew instructions to only spot the correct number of cars for a given location, again placing the excess in another track someplace.

I recently discussed this issue with a retired SP/UP conductor who spent a lot of time on locals in southern California.  He indicated that it was a regular occurrence to have extra cars for various industries switched into his trains at the yard.  Depending on the situation, the off-spots could be placed elsewhere on the same customer's track (assuming there was sufficient track available to keep them out of the way), or dropped someplace else on the local's route, even on track for another customer (again assuming they didn't interfere with that customer's own cars).  Sometimes the off-spots could also be taken back to the yard at the end of the run, but that was less common.  It was up to the conductor to properly account for these cars so they could be located and spotted correctly later.

Most of our model operations seem to ignore this issue altogether, with operators assuming they can just cram cars into customer tracks figuring they HAVE to spot everything in the train.  There also can be the assumption that having extra cars showing up is somehow unrealistic or to be avoided.  It's not necessarily a bad thing, and there are prototypical ways to deal with it.

Might also interest you...

I think this might be a good companion to Mike's switchlist generator:  https://forum.mrhmag.com/post/how-to-operate-12189354

I have a copy and am still reading but I think it addresses the issues about off spots and other factors that would benefit a lot of operations. I have downloaded the spreadsheet and think it looks good!

Alternate list spreadsheet

Just for giggles I made an alternative switchlist for my railroad.  I added a "job' column so i could easily differentiate which jobs worked which industries.  I probably should have added a "station" column too..  I added delivery and leave probabilities for each industry.  I also created a separate switch list form, customized for my railroad, for each of the 5 jobs that work industries (Wilmington Yard job, Wilmington City job (1st and 2nd shift), Wilmington City job (3rd shift), North local, South Local.  Each job has a different list of industries, some share industries, some industries are only switched by on industry.  In addition there is a master "all" switchlist.  I created the All switchlist, then copied it to each individual job worksheets, then deleted the lines for the industries that weren't worked by that job.  As soon as I can figure out how to post a copy of the spreadsheet, I'll get one available.

On too many cars and how to handle them.....

The spread sheet will generate extra cars for a spot at a frequency depending on cell C1.  But it will generate, at most, one extra car per line.  If a spot can receive multiple types of cars, indicated on separate lines, for a single spot, this can also cause the spreadsheet to generate extra cars (1 or 2 on each line).

When this happens, as Rob pointed out, it's up to the conductor to decide what to do with the extra car - and note where it's been placed off-spot for the next cycle's crew. 

On differing probabilities for differing industries....

Actually, this was supposed to be the function of the days/week  (Cell C5) and frequency (Cells D6-D46).

If you set the sheet up to run 5 days per week, then a spot with a frequency of 1 has a 20% (1/5) chance of receiving a car in the current cycle, a spot with a frequency of 2 has a 40% chance, etc.)

By the way, in this manner, you can also assignb different frequencies by car type at a given spot.

On multiple jobs

On my layout, I run only one turn from the division point serving this area (okay, from the fiddle track where I set up the incoming based on the list for that session), but when I have visitors I run two local switching crews, each with a separate area of responsibility.  When that happens, I actually use two separate spreadsheets.

The nice thing about that is that my local yard is intended for storage, not classification and, by building the turn from one list and then the other, the consist arrives blocked, meaning both crews can get in each other's way right from the start, rather than waiting until one crew sorts the cars while the other sits around consuming unholy amounts of liquid libations.

Again, different folks have different layouts designed to represent different sets of conditions.  I'm just tickled that folks have found ways to make the idea fit those different situations.

Alternate Spreadsheet

Here is the alternate spreadsheet at Google Docs:

https://docs.google.com/spreadsheet/ccc?key=0Av-G9-8pxpQ5dEJQTWZKRjJ4OUU0NTZzVjV1dUhXb3c#gid=0

re: Alternate Spreadsheet

Dave:

You need to make the spreadsheet available before we can see it.

Now ask me how I did that.... I'm trying to remember.

Oh, yeah.... see here:

https://support.google.com/docs/bin/answer.py?hl=en&answer=180199&topic=1360909&ctx=topic

Access granted

Sorry didn't know that step was required, never used Google Docs before.

Enjoy.

https://docs.google.com/spreadsheet/ccc?key=0Av-G9-8pxpQ5dEJQTWZKRjJ4OUU0NTZzVjV1dUhXb3c

All entries make changes

Mike,

I am curious about this switch list generator program. I downloaded in the Excel format and have a question about entering data. I have all my industries set up according to the information you have provided to make the switch list but it always changes with anything I do to the sheet to enhance it.

No matter where on the spreadsheet I enter anything a number, date, letter, word the entries that are for some industry change every time I write to a cell and it is not related to the switch list at all.

Is this typical for the cells to change in the spreadsheet with anything done to the sheet?

Barry

 Standard Excel. Go to tab

Standard Excel. Go to tab "calculation options" in Excel, change "automatic" to "manual", hit F9 when you want to run a manual re-calculation.

Smile,
 Stein

Thanks!

Mike (and Dave),

Thank you for sharing - this will be fun to play around with. I'm trying to avoid going the car card route - building switch lists instead. Hopefully I'll have some time to muck around with DaveH's updated version, and customize it for the B&NW.

Regards,

D.M. Mitzel

Customizing Dave's version

All,

This will show my ignorance of how to work with Excel, but how would one go about customizing Dave's revisions for one's own railroad?  I really like Mike's program and Dave's extension of it to include the generation of switchlists.  I was able to change the industry locations to those on my railroad and have the program work but I'm not certain how to add the switchlist component so the switchlists are automatically generated for printing.

Thanks,

Mike Toms

multiple car types

Not an Excel programmer either - I've been muddling with this one for a while with no success.  At its simplest, I have one industry with one spot which can take say a boxcar OR a reefer.  I don't want the two cars to show up at the same time.  Extra delivery probability is already set to 0.

I think the logic would go something like this:

IF (it's the same industry  AND it's the same spot AND we didn't have a car to deliver on the previous line)

   THEN (we do the calculation for the current line)

Here's a cheap version to try in column K (pop it in at K5 and copy down the column).  I don't _think_ it will work properly for more than two car types at a given spot.  In fact I know it doesn't.  And it will always favour the first car type listed for that spot.  But it's a start.

=IF(AND(B5=B4;C5=C4;NOT(K4="));";J5)

David

Wow - great tool!

Mike, I went looking last night for some ideas on how best to move cars on my under-construction shortline and I think this really fit the bill.  Thanks for all the work you put into it and for sharing it. 

I had spent some of yesterday planning my final track and industry layout and sort of mentally "ran it" to see how it'd work, but this spreadsheet took it one step further.  I was able to enter all of my industries, car spots, types, etc., set the parameters, and then after a couple of times tweaking it I did 40 quick operating sessions.  My layout will be interchange to customers, then back, once per day - very simple.  I would push F9, then note how many cars were picked up and how many were dropped of that trip.  I'd then remark the sheet based on that and push F9 again, and repeat.  By doing this I was able to see what my average train size would be, what industries were getting too many or too few deliveries, and other assorted observations. 

For example, a few times there would be zero pick-ups or zero set-outs, which I hadn't really considered before.  I also rethought how I would handle it when more than one car was to be delivered to the same "spot".  Previously I had planned to set them out in a stub track en-route for later delivery, but by visually running a session I could see that in most cases I could also simply bump the car up or down to another spot at the same customer.  I think there was only two or three times across 40 sessions where an industry couldn't accommodate a particular delivery.  In those cases, I figure I could actually just type over the formula in the "Deliver" cell with the spot where a car is sitting on the stub track, then after picking it up and delivering in the next session I could just copy/paste a formula back in for future deliveries.

Anyhow, a long winded way of saying thank you for this great tool!

One question is how to alter the probabilities for specific industries or spots?  I don't remember reading what anyone had done with that.  If someone has an example of a formula, I'd appreciate it.

Thanks!

-Dave