Wait, looking at your spreadsheet, it looks like you have Ryan, Delaney, and Swalwell with a 0.3% polling average? How is that possible, if they each have three polls at 1%? Shouldn't they have a 1% average? Or am I misunderstanding the numbers?
You're right. I had forgotten to remove the bit of code only include national polls as part of the average. It's been fixed now.
There is a bias in your assignment of debates.
What you are doing is equivalent to having each candidate flipping a coin for each candidate until either group is filled up, and then assigning the remainder to the other group.
This makes assignment of latter candidates dependent on earlier assignments.
The odds of Biden and Sanders being in the same group under your method is 1/2 (Sanders matches Biden's flip). In actuality, the odds of two candidates being in the same group should be 9/19. 10 of a candidate's 19 opponents will be in the other group, only 9 in his own.
Moreover, one group or the other will tend to fill up, forcing the last candidates into the same group. For example if you flip 16 coins, the probability of 10 or more landing the same way is 45.4%. Thus, the final four have 45.4% chance of all being placed in the same group, making it a de facto undercard.
A better approach would be to have 20 balls with candidate names, and 20 balls with debate number and podium position (A1, ..., A10) (B1, ..., B10). For simplicity, we could eliminate the podium position.
The a formula like:
H
n =IF(RANDBETWEEN(COUNTIF(H$1:H
n-1,1)+1, 20-COUNTIF(H$1:H
n-1,2)<=10, 1, 2)
Adjust parentheses and commas to mae a valid formula.
That is, Biden would draw a number between 1 and 20, and be assigned to Group 1 if his number is 10 or less, and Group 2 if it is 11 or more.
If Biden is in Group 1, then Sanders would draw a number between 2 and 20; if Biden is in Group 2, then draw a number between 1 and 19.
That is we reduce the chances to draw into a group as it fills up.
The above assumes 20 candidates, make adjustments for fewer numbers. Replace 20 with number of candidates, Replace 10 with CEIL(number of candidates/2). This would make Group 1 the larger group if there is an odd number.
Poll
