Excelled myself into a corner
April 1, 2023 11:27 AM Subscribe
This image shows the output of my google form, with some "clever" formulas I added. But I just realised they don't tell me what I need to know. Help me make use of this data please!
Ok, so these 20 respondents have marked which of ten dates they can attend, either in the morning or the afternoon. The leftmost column counts how many days the user can be available, and row 2 and 3 show how many people have said morning or afternoon in the column below. Clever right? But only so clever... it might be the same 3 highly flexible people who are available all the time, and each person can only participate once. I need 3 people per "pass" (session). Their ID is in another column further to the right.
What should I be trying to do here?
Ok, so these 20 respondents have marked which of ten dates they can attend, either in the morning or the afternoon. The leftmost column counts how many days the user can be available, and row 2 and 3 show how many people have said morning or afternoon in the column below. Clever right? But only so clever... it might be the same 3 highly flexible people who are available all the time, and each person can only participate once. I need 3 people per "pass" (session). Their ID is in another column further to the right.
What should I be trying to do here?
(Note: Assuming it's possible for you to run AM and PM sessions on the same day, my algorithm will work a little better if you make 20 columns, that is, a separate column for each meeting time rather than for each date.)
posted by aws17576 at 12:03 PM on April 1, 2023
posted by aws17576 at 12:03 PM on April 1, 2023
Response by poster: To clarify, yes, your point 1 is where I was feeling clever, the guy who can only do the one time (flex value 9, red) is going to be contacted first, in order to extract most "value" from the pool. But yeah that's where my smartness ended. The AM and PM sessions are on the same day. I suspect I will have to make more columns yes, I had hoped that the form would output like that, but no.
posted by Iteki at 12:08 PM on April 1, 2023
posted by Iteki at 12:08 PM on April 1, 2023
Yes, to build on what aws17576 suggests, my initial instinct is to forget about the fact that there are morning and afternoon sessions on the same day, and treat them as 20 totally independent sessions.
If I’m understanding correctly, the fact that a participant can do Day X Morning has no bearing on whether they can do Day X Afternoon. So a “days available” column doesn’t seem useful at all. (A value of 6 could mean the participant can do 6 different sessions, 12 different sessions, or anything in between.)
posted by staggernation at 12:48 PM on April 1, 2023
If I’m understanding correctly, the fact that a participant can do Day X Morning has no bearing on whether they can do Day X Afternoon. So a “days available” column doesn’t seem useful at all. (A value of 6 could mean the participant can do 6 different sessions, 12 different sessions, or anything in between.)
posted by staggernation at 12:48 PM on April 1, 2023
I have some issues with your data - and this is what I would ask if this was a work project - so don't take of this personally. Just want some clarity around your constraints.
-You only have 20 people and need groups of 3 - so what happens to the other 2? Are they excluded or do they get their own group or can some groups have more than 3?
-The leftmost column seems to be counting the unavailable days and not the available days.
-I agree with making am and pm different columns if you really have to do this with formulas in a spreadsheet, which I don't think you do.
Spreadsheets aren't the best for optimizing things but they can be good for visualizing your data and your solution. Also, are you optimizing by the fewest number of sessions, the earliest date of completion, or something else? This seems like it would be faster to manually mark the spreadsheet because you only have 6 or 7 groups. Take the least available people (the 9s) and schedule them with others who have the same time open, leaning towards other less available people. Eventually you may find someone with no availability in common with another unscheduled person, so maybe you have to switch them out, or make them one of your two excluded people.
posted by soelo at 3:46 PM on April 1, 2023
-You only have 20 people and need groups of 3 - so what happens to the other 2? Are they excluded or do they get their own group or can some groups have more than 3?
-The leftmost column seems to be counting the unavailable days and not the available days.
-I agree with making am and pm different columns if you really have to do this with formulas in a spreadsheet, which I don't think you do.
Spreadsheets aren't the best for optimizing things but they can be good for visualizing your data and your solution. Also, are you optimizing by the fewest number of sessions, the earliest date of completion, or something else? This seems like it would be faster to manually mark the spreadsheet because you only have 6 or 7 groups. Take the least available people (the 9s) and schedule them with others who have the same time open, leaning towards other less available people. Eventually you may find someone with no availability in common with another unscheduled person, so maybe you have to switch them out, or make them one of your two excluded people.
posted by soelo at 3:46 PM on April 1, 2023
Another observation, you have 4 people who can only do the first day PM session - so does that mean a group of 4 or a group of 3 with one excluded?
posted by soelo at 3:49 PM on April 1, 2023
posted by soelo at 3:49 PM on April 1, 2023
Response by poster: All this input is super helpful, thanks folks! I expect to have more than 20 candidates, and not all will be assigned most likely, I expect some will have changed their availability closer to the time when we try to book more concretely. It is a work project and there is no demand to spreadsheet it, I was mainly trying to visualize what I had and wondered if there ways to have it make more sense or pull some more insights from it.
posted by Iteki at 4:23 PM on April 1, 2023
posted by Iteki at 4:23 PM on April 1, 2023
This thread is closed to new comments.
1) Start by identifying the most tightly constrained people. Put them into tentative groups (e.g. you seem to have 4 people who can all do afternoon on the first day, and can't do any other time; that's your first group).
2) Delete the rows of the people you just scheduled and the column of the day you just scheduled them for. Make sure you keep a backup copy so you can backtrack if necessary. Your formulas should update automatically.
3) Return to step 1 using the updated data. If things become unworkable, backtrack and make what looks like the next best choice.
Before beginning, you might want to delete people for whom no possible session exists (i.e. the people who are only available at times when there aren't two other people available).
posted by aws17576 at 11:57 AM on April 1, 2023