Col A1=0, A2=2 etc down to A91=90

Col B1=RAND() etc down to B91=RAND()

D1=INDEX($A$2:$A$92,RANK(B2,$B$2:$B$92))

I press F9 until 0(zero) appears (in D1) then:

I press F9 (once) and a single random number (1-90) appears (in D1)

A normal, commercial, random selector (when pressing the start button)
visually revolves through all numbers (for several seconds) before stopping,
having selected a number.

Question: What do I need to add to D1 (above) to make it do the same?

I know it can be done, but it has got me beaten. Please advise.

--
Regards,
John

What you appear to want is a random number between 0 and 91, assuming you
meant A2=1 (not 2 as in your post); otherwise A91 would not equal 90.

In Excel 2007 you could use...

=RANDBETWEEN(0,91)

In earlier versions use

=INT(RAND()*(92))

If you want numbers to 'roll' around for some visual effect then you need
some VBA code to control the output. Excel does not have this function built
in. Try something like...and assign this macro SpinNumber to a worksheet
control.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub SpinNumber()
Dim iX As Integer
For iX = 1 To 200
Range("D1") = Int(92 * Rnd)
Sleep 10
Next
End Sub

--

Regards,
Nigel




"JOHN BERNARD" wrote in message
...
Col A1=0, A2=2 etc down to A91=90

Col B1=RAND() etc down to B91=RAND()

D1=INDEX($A$2:$A$92,RANK(B2,$B$2:$B$92))

I press F9 until 0(zero) appears (in D1) then:

I press F9 (once) and a single random number (1-90) appears (in D1)

A normal, commercial, random selector (when pressing the start button)
visually revolves through all numbers (for several seconds) before
stopping,
having selected a number.

Question: What do I need to add to D1 (above) to make it do the same?

I know it can be done, but it has got me beaten. Please advise.

--
Regards,
John

Thanks Nigel. Yes - A2 = 1.

Not sure about the 'roll' around macro, but will give it a try.
Much appreciated
--
Regards,
John


"Nigel" wrote:

What you appear to want is a random number between 0 and 91, assuming you
meant A2=1 (not 2 as in your post); otherwise A91 would not equal 90.

In Excel 2007 you could use...

=RANDBETWEEN(0,91)

In earlier versions use

=INT(RAND()*(92))

If you want numbers to 'roll' around for some visual effect then you need
some VBA code to control the output. Excel does not have this function built
in. Try something like...and assign this macro SpinNumber to a worksheet
control.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub SpinNumber()
Dim iX As Integer
For iX = 1 To 200
Range("D1") = Int(92 * Rnd)
Sleep 10
Next
End Sub

--

Regards,
Nigel




"JOHN BERNARD" wrote in message
...
Col A1=0, A2=2 etc down to A91=90

Col B1=RAND() etc down to B91=RAND()

D1=INDEX($A$2:$A$92,RANK(B2,$B$2:$B$92))

I press F9 until 0(zero) appears (in D1) then:

I press F9 (once) and a single random number (1-90) appears (in D1)

A normal, commercial, random selector (when pressing the start button)
visually revolves through all numbers (for several seconds) before
stopping,
having selected a number.

Question: What do I need to add to D1 (above) to make it do the same?

I know it can be done, but it has got me beaten. Please advise.

--
Regards,
John