How to easily de-conflict event timings for conferences

Today’s topic has nothing to do with dinosaurs, but it is something some of you may encounter every once in a while. Let’s say you’re planning a conference with many events – talk sessions, poster sessions, workshops, whatnot. Obviously, you’ll ideally time all events so that everybody can take part in all events they want to. However, it is usually not possible to have only one single session at any one time. As soon as you have dozens of people attending you’ll end up with more events than time.

So now, you’d ideally find out who wants to attend which events, then find the combination of timings that reduces collisions to a minimum. But how to do that? How do you find out which potential collisions occur least often?

I am facing this problem right now for the DigitalSpecimen 2014 conference, where up to 100 people may wish to attend up to 8 workshops each. Here’s how I solved the problem with a little very primitive EXCEL magic.

1. Polling the attendants
I asked people to register for the workshops they WANT to attend. I assume that they all answer truthfully. Thus, I know who wants to attend what combination of events, i.e. which events should not be at the same time for that person.

2. Hacking the data into EXCEL
I now made a table in EXCEL where each line is for one attendant. It has one column for each workshop. I enter a “1” if the person registered for the workshop, and a “0” if the person did not register. This is what things look like now:

Don’t mind the gap – I only use it to separate MfN employees from the rest. Sums and all work across gaps, too.

3. Computing collisions
I now added further column, one each for each possible conflict. Thus, I no have columns titles 1/2, 1/3, 1/4….. 2/3, 2/4, 2/5…. 3/4….. and so on. (Remember to format the cells as “text”, otherwise the program will turn these entries into dates or whatnot!)  Now, I enter formulas into the cell from the row with the first attendant: =PRODUCT(Cell1;Cell2), with Cell1 the cell that holds the info for that attendant on the first of the two possibly conflicting workshops, and Cell2 the info on the other one.
If the person registered for both workshops, the result obviously is 1*1=1. If she or he registered only for one or for none of the workshops, the result is 1*0=0 or 0*1=0 or 0*0=0. Thus, if there is a potential conflict, the result is 1, if there is none the result is 0.
Now, I know which collisions occur for the first person whose data I entered. By pulling down the bottom right corner of the field across all rows that hold names I can make EXCEL apply the same formula for all of them, and quickly have the info for all attendants.

4. Summing up collisions
To find out how often each collision occurs, I now add a row of sums at the bottom. Under each column of 0s and 1s (for no collision and collision) I enter =SUM(Cell3:Cell4), with Cell3 the top-most data cell in the column, and Cell4 the last one. And presto – I get a nice row of numbers that tell me how often each collision would occur for a given pair of workshops.
Here’s how that looks:

And now, I can see at a simply glance which events I can place at the same time without pissing off too many of my esteemed colleagues, and which pairings I better avoid! In this case, Workshops 1, 3, 5 and 7 should all be planned to not run in parallel, whereas a WS 2 – WS 8 collision would hurt only few people.