How to get Excel to generate any random number besides x?

[moonman239]

Here's my dillemma:

I'm writing up a spreadsheet where we have 100 passengers on a plane. Excel randomly generates their assigned seat, and the seat they end up taking. But the problem is, I need to make it so that Excel does not generate the same number twice.

 

[Hurkyl]

Here's my dillemma:

I'm writing up a spreadsheet where we have 100 passengers on a plane. Excel randomly generates their assigned seat, and the seat they end up taking. But the problem is, I need to make it so that Excel does not generate the same number twice.

The problem is that you're not asking the right question.


You seem to be trying to make the random number generator output seat assignments, and running into the problem that it can output the same number twice.


But what you should be trying to do is to find a (fair) scheme for using the output of the random number generator to select a seat.

 

[moonman239]

You seem to be trying to make the random number generator output seat assignments, and running into the problem that it can output the same number twice.

But what you should be trying to do is to find a (fair) scheme for using the output of the random number generator to select a seat.

Any suggestions?

 

[AlephZero]

Generate 100 random real numbers between 0 and 1. It is unlikely you will get two numbers exactly the same, but it doesn't matter if that happens.
Label the numbers "1" to "100".
Sort them.
Use the sorted order of the labels as your seat assignments

For example using 3 seat numbers, suppose your random numbers were
0.4, 0.7, 0.2
Label them in the order they were generated:
1: 0.4
2: 0.7
3: 0.2
Sort them:
3: 0.2
1: 0.4
2: 0.7
Your random seat assigments are 3, 1, 2

 

[moonman239]

Generate 100 random real numbers between 0 and 1. It is unlikely you will get two numbers exactly the same, but it doesn't matter if that happens.
Label the numbers "1" to "100".
Sort them.
Use the sorted order of the labels as your seat assignments

For example using 3 seat numbers, suppose your random numbers were
0.4, 0.7, 0.2
Label them in the order they were generated:
1: 0.4
2: 0.7
3: 0.2
Sort them:
3: 0.2
1: 0.4
2: 0.7
Your random seat assigments are 3, 1, 2

Excuse me for asking this, but could you please further explain your example?

 

[EnumaElish]

Here's my solution to the problem as I understand it.

Let's say the craft has 10 rows of seats with three seats on each row. That's a matrix with 10 rows and 3 columns. X can take on 3 values: left, middle, right. Y can take on values from 1 to 10. Let x and y be the actual random numbers generated independently. For example, if there is one person boarding, generate one x and one y independently. Suppose x = 0.523566 and y = 0.121252. Since 1/3 < x < 2/3, X = middle. Since 0.1 < y < 0.2, Y = 2 i.e. it's the second row, so the passenger requests "middle seat on the second row." Do this as many times as there are passengers. If a passenger requests a seat which is "taken," then keep generating new pairs of numbers for that person until they request one of the seats that has remained empty until then.

 

[AlephZero]

Excuse me for asking this, but could you please further explain your example?

There are two different ways to think about your seat assignment problem.

Your OP is thinking about the same way that it happens in real life. The first passenger turns up and chooses a seat and random. Then the second passenger chooses one of the remaining empty seats at random, etc.

The other way to look at it is to see what you have after the plane has been filled up. Now, the first seat contains the passenger who was at some random position in the queue of passengers. The second seat contains the passenger who was at another (different) random position in the queue, etc.

My method uses the second way to think about it, not the first. Imagine you have all the passengers in a queue waiting to board the plane. You give them all lottery tickets with different random numbers on them.

Then you say, "the passenger with the lowest numbered ticket gets to sit in seat 1". In my example, that was the third person waiting in the queue. Then you say, "the passenger with the next lowest numbered ticket ges to sit in seat 2", etc.

The "sorting" step of my procedure is a quick way to do this all at once, rather than picking out the smallest numbers one at a time. But you could pick them one at a time if you want to.

I used real numbers instead of integers for the "lottery tickets numbers", so it is very unlikely you get two "tickets" with the same number. But if two tickets DO have the same number, all it means is that those two people sit in adjacent seat numbers, and which of them sits in which of seat doesn't affect the randomness much, because this situation will be very rare in practice.

Hope that helps.