If I put 3 numbers at row 100, the counta would be 3*rand(). leading to
integers 0,1,2 as offsets to A1. To me that says it doesn't work, but
maybe I don't understand the problem.

--
Regards,
Tom Ogilvy

"cush" <cush@discussions.microsoft.com> wrote in message
news:AD823321-46A7-4541-82D2-819D39720B9E@microsoft.com...
> Actually, the numbers do not have to start in A1. You can have blanks,
but
> not text,(labels, or headers).
>
> "Tom Ogilvy" wrote:
>
> > An excellent solution - but
> > Just a heads up, that you must start your numbers in A1 with this
solution.
> > That was not a requirement in your specification - reason for the
warning.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> > news:OJDVfDQyFHA.3124@TK2MSFTNGP12.phx.gbl...
> > > Perfect and simple = "simply perfect"
> > >
> > > Thanks cush
> > >
> > > B.B.
> > >
> > > "cush" <cush@discussions.microsoft.com> wrote in message
> > > news:B566DEAB-75D7-4749-8FAC-7F084B6F1226@microsoft.com...
> > > > try this in B1:
> > > >
> > > > =INDEX(A:A,ROUND(RAND()*COUNTA(A:A),0))
> > > >
> > > > "William Benson" wrote:
> > > >
> > > >> User enters any array of numbers beginning in any cell: Example
> > > >>
> > > >> A15 23
> > > >> A16 11
> > > >> A17 5
> > > >> . .
> > > >> . .
> > > >> . .
> > > >> A172 145
> > > >> A173 220
> > > >>
> > > >>
> > > >> Required: Formula (not VBA, but perhaps array-entered) which will:
> > > >>
> > > >> 1) return a RANDOM number from AMONG the items in the list
> > > >> 2) Not require that the list start, list end, or # of items
in
> > > >> the
> > > >> list be fixed (only col A is fixed).
> > > >> 3) not require that the list be in ascending or descending
> > order
> > > >> 3) be entered in cell B1
> > > >>
> > > >> I started something involving INDEX and the ROW() of the starting
and
> > > >> ending
> > > >> item, but I want it to be more dynamic. Then I tried to use MATCH
to
> > tell
> > > >> me
> > > >> the start or end values, by using 0.001 and 1,000,000 and matching
with
> > > >> Match_Type = 1, or -1, but that required the items be in ascending
or
> > > >> descending order. So, I am stuck... can it be done?
> > > >>
> > > >>
> > > >>
> > >
> > >
> >
> >
> >