-----Original Message-----
Mr. Harlan, thank you for your time but I can't see how
exactly you do it?
...
Drop the Mr. or use my last name, M. Jac.
Question 1:
When you say: "This is easier if you draw from a
population of 1 to 6, so I'll define the name Pop
referring to =ROW(INDIRECT("1:"&NbPers)).", what do you
mean?
In English versions, there's an Insert entry in the main Excel menu, located in
4th position after File, Edit and View entries and before Format, Tools, Data,
Window and Help. Clicking on Insert displays its menu, and part way down there's
an entry for Name, which displays a submenu, and on that submenu there's a
Define... entry. So Insert > Name > Define... displays a dialog titled Define
Name. Enter (without the single quotes) 'Pop' in the topmost field (just beloe
the label 'Names in workbook:'), and '=ROW(INDIRECT ("1:"&NbPers))' in the
bottommost field (just below the label 'Refers to:') and click the OK button.
I have no idea what the corresponding menu entries may be called in French
versions.
Do you enter the numbers 1 to 6 in some cells and name the
range "Pop"? Then, what if I have 1000 persons? Or if I
have to change the number of persons constantly?
I was assuming NbPers was also a defined name or a named cell. If so, then when
you change NbPers, Pop will change accordingly. So if NbPers began as 6, Pop
would evaluate to {1;2;3;4;5;6}. If you then change NbPers to 1000, Pop would
change to {1;2;3;4;...;999;1000}. That's why I made Pop a defined name depending
on the value of NbPers.
What is this formula supposed to be for? It always returns
the number 1 anyway (maximum NbPers or 6 here).
The formula evaluates to an array. Like all arrays, if you enter =Pop into a
single cell, you see only the first entry in Pop, which is always 1. If you type
=Pop in a cell and press [F9] rather than [Enter], you'll see it evaluates to
={1;2;3;4;5;6} (actually, if your version of Excel uses semicolons as list
separator, you may see a different character between the digits, but you
wouldn't see just =1).
Question 2:
In the formula "=LARGE(IF(COUNTIF(A$3:A3,Pop)=0,Pop),
INT(1+(NbPers-ROW()+ROW(A$3))*RAND()))", why do you
use "A$3"? Why not "$A$3"?
Only the row is important. ROW(A$3) is *ALWAYS* the same as ROW($X$3), ROW(X$3),
ROW($X3), ROW(X3) and even ROW(A3), ROW($A3) and ROW ($A$3). Nothing is gained
using ROW($A$3) instead of ROW(A$3), but nothing would be lost either. The only
important thing is that the column portion of the reference, whether absolute or
relative, refer to the column in which these formulas were entered. Then these
formulas would be immune to inserted or deleted columns, excluding deletion of
the column in which they appear.
Question 3:
What do you mean by: " [array formula - in A4]"?
You'd be better off reading about array formulas in online help. They're
frequently mentioned in the English Excel newsgroups, and I'd be very much
surprised if they weren't mentioned as frequently in French Excel newsgroups.
Array formulas are entered differently than
