set of numbers, i.e. [1..48]

D

Drew K

Is it possible to work with sets of integers in Excel?
=set(1..48)
or will I end up using booleans in a column to signify presence and absence?
 
N

Niek Otten

Perhaps you can explain what you're trying to achieve?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Is it possible to work with sets of integers in Excel?
| =set(1..48)
| or will I end up using booleans in a column to signify presence and absence?
 
D

Drew K

Thanks Niek,

I wrote a program so long ago in Pascal that handles placing students at my
school at random dinner tables for our formal meals. I want to pass the
assignment responsibility off to a non-programmer, and feel excel might be
strong enough to do it without learning any visual basic.

To simplify: I've got four sets of 80 students. I randomly pick one kid in
the set and place him at table 1, and remove him from the set. I repeat,
going around the room until that set is empty. Then I start on the second
set, doing the same thing.

It works very well, and the only shortcoming is that a student will randomly
end up back at the same table later on, or with many of the same students
later on. The ideal would be to store a seating history, and if a student
gets placed poorly then he'd go back into the set and another one selected
for placement.

I hope this makes some sense, and thanks to anyone who has read this far!
 
T

Tom Ogilvy

=index({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48},1,Trunc(rand()*48+1))

is an example of randomly selecting a number from a set of 48 numbers

on each calculate, a new number is selected at random (there may be
repeats). This is just an example to illustrate a set of numbers. The
functionality is not the point.
 
B

Biff

=INDEX(ROW(1:48),INT(RAND()*48+1))

Or, to make it robust against row insertions:

=INDEX(ROW(INDIRECT("1:48")),INT(RAND()*48+1))

Biff
 
T

Tom Ogilvy

Your assuming his numbers are sequentially numbered - I didn't make that
assumption - just used it as an example.
 
Top