I have the alaphabet in column 1 starting at row 4. I would like for a cell
to randomly pick one of the 26 letters of the alphabet. In cell B4 I have the
following formula:

=INDEX($A$4:$A$29,INT(RANDBETWEEN(4,29)),0)

It works, every few refreshes (F9), it will give me a #REF! error. I can not
figure out why. Any help would be appreciated as this is frustrating me.

Thanks.

Here's one way that doesn't require you to list letters.

=CHAR(RANDBETWEEN(65,90))

That'll return the letter in uppercase.

--
Biff
Microsoft Excel MVP


"Lost in Microbiology" wrote
in message ...
I have the alaphabet in column 1 starting at row 4. I would like for a cell
to randomly pick one of the 26 letters of the alphabet. In cell B4 I have
the
following formula:

=INDEX($A$4:$A$29,INT(RANDBETWEEN(4,29)),0)

It works, every few refreshes (F9), it will give me a #REF! error. I can
not
figure out why. Any help would be appreciated as this is frustrating me.

Thanks.

Your array only have 25 spots. Change the RANDBETWEEN arguement to:

RANDBETWEEN(1,26)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Lost in Microbiology" wrote:

I have the alaphabet in column 1 starting at row 4. I would like for a cell
to randomly pick one of the 26 letters of the alphabet. In cell B4 I have the
following formula:

=INDEX($A$4:$A$29,INT(RANDBETWEEN(4,29)),0)

It works, every few refreshes (F9), it will give me a #REF! error. I can not
figure out why. Any help would be appreciated as this is frustrating me.

Thanks.

=INDEX($A$4:$A$29,INT(RANDBETWEEN(4,29))-3)

If Randbetween returns 29; the array contains only 26..So adjust as above

If this post helps click Yes
---------------
Jacob Skaria


"Lost in Microbiology" wrote:

I have the alaphabet in column 1 starting at row 4. I would like for a cell
to randomly pick one of the 26 letters of the alphabet. In cell B4 I have the
following formula:

=INDEX($A$4:$A$29,INT(RANDBETWEEN(4,29)),0)

It works, every few refreshes (F9), it will give me a #REF! error. I can not
figure out why. Any help would be appreciated as this is frustrating me.

Thanks.

First, you don't need the int() portion.

Second, =randbetween(4,29) will return values between 4 and 29.

=index(A4:A29,4) will work ok
=index(A4:A29,29) won't work ok. There aren't 29 cells in that range.

Just because your range starts in row 4, it's still indexed from 1 to 26.

=INDEX($A$4:$A$29,RANDBETWEEN(1,26),0)

Should work ok.


Lost in Microbiology wrote:

I have the alaphabet in column 1 starting at row 4. I would like for a cell
to randomly pick one of the 26 letters of the alphabet. In cell B4 I have the
following formula:

=INDEX($A$4:$A$29,INT(RANDBETWEEN(4,29)),0)

It works, every few refreshes (F9), it will give me a #REF! error. I can not
figure out why. Any help would be appreciated as this is frustrating me.

Thanks.

--

Dave Peterson