# RAND() function error? ##

JAgger1
Guest
Posts: n/a

 13th Mar 2012
I have a set of numbers in cell A1:J1

I use =INDEX(\$A1:\$J1,ROUND(RAND()*COUNTA(\$A1:\$J1),0)) in cell L1 to
N1 to get 3 random numbers from my set of numbers. Sometimes I end up
with ## instead of a random number in one of the cells? Anyone know
why? Thanks

Martin Brown
Guest
Posts: n/a

 13th Mar 2012
On 13/03/2012 11:42, JAgger1 wrote:
> I have a set of numbers in cell A1:J1
>
> I use =INDEX(\$A1:\$J1,ROUND(RAND()*COUNTA(\$A1:\$J1),0)) in cell L1 to
> N1 to get 3 random numbers from my set of numbers. Sometimes I end up
> with ## instead of a random number in one of the cells? Anyone know
> why? Thanks

There is a chance that ROUND(RAND()*COUNTA()) will be <0.5

And so rounds to 0 which is an invalid index

--
Regards,
Martin Brown

JAgger1
Guest
Posts: n/a

 13th Mar 2012
On Mar 13, 8:14*am, Martin Brown <|||newspam...@nezumi.demon.co.uk>
wrote:
> On 13/03/2012 11:42, JAgger1 wrote:
>
> > I have a set of numbers in cell A1:J1

>
> > I use =INDEX(\$A1:\$J1,ROUND(RAND()*COUNTA(\$A1:\$J1),0)) in cell L1 to
> > N1 *to get 3 random numbers from my set of numbers. Sometimes I end up
> > with ## instead of a random number in one of the cells? Anyone know
> > why? Thanks

>
> There is a chance that ROUND(RAND()*COUNTA()) will be <0.5
>
> And so rounds to 0 which is an invalid index
>
> --
> Regards,
> Martin Brown

Hmm, so is there a way to write this so that wouldn't happen?
Also, is it possible to modify the formula so there would be no
repeats?

joeu2004
Guest
Posts: n/a

 13th Mar 2012
"JAgger1" <(E-Mail Removed)> wrote:
> I have a set of numbers in cell A1:J1
> I use =INDEX(\$A1:\$J1,ROUND(RAND()*COUNTA(\$A1:\$J1),0))
> in cell L1 to N1 to get 3 random numbers from my set
> of numbers. Sometimes I end up with ## instead of a
> random number in one of the cells? Anyone know why?

I am not quite sure why that ever works as written; but it does. I would
expect a #REF error when ROUND(RAND()*COUNTA(\$A1:\$J1),0) returns 2 or more
because that requests row 2 or more from a range that includes only 1 row.

Anyway, I believe the following is what you might want:

=INDEX(\$A1:\$J1,1,ROUND(RAND()*COLUMNS(\$A1:\$J1),0))

Of course, you could replace COLUMNS(\$A1:\$J1) with 10 unless you anticipate
inserting columns between columns A and J.

If RAND() returns less than 0.05, the column number will be zero. No harm
done [1], since INDEX(A1:J1,1,0) is perfectly valid. That returns the
entire range A1:J1. But in this context, Excel will select the first cell
of the range, namely A1.

RAND() always returns less than 1. So ROUND(RAND()*COLUMNS(\$A1:\$J1),0)
should never exceed 10, the number of columns in A1:J1.

-----
[1] "No harm done" means: it should not cause an Excel error. However, it
does skew the probability distribution toward A1. That is, it is more
likely to return A1.

joeu2004
Guest
Posts: n/a

 13th Mar 2012
Errata.... I wrote:
> Anyway, I believe the following is what you might want:
> =INDEX(\$A1:\$J1,1,ROUND(RAND()*COLUMNS(\$A1:\$J1),0))

[....]
> If RAND() returns less than 0.05, the column number will be zero. No harm
> done [1], since INDEX(A1:J1,1,0) is perfectly valid. That returns the
> entire range A1:J1. But in this context, Excel will select the first cell
> of the range, namely A1.

My bad! INDEX(A1:J1,1,0) is indeed valid, and it does indeed return the
entire range A1:J1. But Excel selected A1 only because I put the formula in
a row below A1:J1, not in row as you are doing. Otherwise, Excel returns a
#VALUE error.

=INDEX(\$A1:\$J1,1,MIN(COLUMNS(\$A1:\$J1),INT(1+RAND()*COLUMNS(\$A1:\$J1))))

The MIN function should not be necessary. However, there is a defect in INT
[1] that causes INT(x) to return the next larger integer(!).

-----
[1] I call it a defect because INT(x) should never return the next larger
integer by definition, and VBA Int returns the correct result.

For example, INT(1+(1-2^-53)*10) returns 11. Note that 1-2^-53 is the
largest number less than 1 than can be represented internally by Excel.
However, it is unclear whether your RAND function returns values that close
to 1. In XL2003 and XL2007, RAND cannot return a number so close to 1 that
the INT expression will "fail" (return an unexpected result). However, RAND
was redesigned for XL2010.

Martin Brown
Guest
Posts: n/a

 14th Mar 2012
On 13/03/2012 12:34, JAgger1 wrote:
> On Mar 13, 8:14 am, Martin Brown<|||newspam...@nezumi.demon.co.uk>
> wrote:
>> On 13/03/2012 11:42, JAgger1 wrote:
>>
>>> I have a set of numbers in cell A1:J1

>>
>>> I use =INDEX(\$A1:\$J1,ROUND(RAND()*COUNTA(\$A1:\$J1),0)) in cell L1 to
>>> N1 to get 3 random numbers from my set of numbers. Sometimes I end up
>>> with ## instead of a random number in one of the cells? Anyone know
>>> why? Thanks

>>
>> There is a chance that ROUND(RAND()*COUNTA()) will be<0.5
>>
>> And so rounds to 0 which is an invalid index
>>
>> --
>> Regards,
>> Martin Brown

>
> Hmm, so is there a way to write this so that wouldn't happen?

Someone else has alreay posted a suitable tweak.

> Also, is it possible to modify the formula so there would be no
> repeats?

If you mean by no repeats so that it behaves like drawing numbered balls
from a bag without replacement the short answer is NO, or at least doing
it would be so clumsy that it isn't worthwhile.

Simplest way to do that is have an InitMy_Random VBA function that
copies the list of possible values to a private array, shuffles them a
decent number of times by swapping a random pair of values and then
returns the shuffled array each time the My_Random() is called until
values run out when it should return #VALUE or some other "failed" flag.

--
Regards,
Martin Brown

JAgger1
Guest
Posts: n/a

 14th Mar 2012
> >>> I use =INDEX(\$A1:\$J1,ROUND(RAND()*COUNTA(\$A1:\$J1),0)) in cell L1 to
> >>> N1 *to get 3 random numbers from my set of numbers. Sometimes I endup
> >>> with ## instead of a random number in one of the cells? Anyone know
> >>> why? Thanks

>
> >> There is a chance that ROUND(RAND()*COUNTA()) will be<0.5

>
> >> And so rounds to 0 which is an invalid index

>
> >> --
> >> Regards,
> >> Martin Brown

>
> > Hmm, so is there a way to write this so that wouldn't happen?

>
> Someone else has alreay posted a suitable tweak.
>
> > Also, is it possible to modify the formula so there would be no
> > repeats?

>
> If you mean by no repeats so that it behaves like drawing numbered balls
> from a bag without replacement the short answer is NO, or at least doing
> it would be so clumsy that it isn't worthwhile.
>
> Simplest way to do that is have an InitMy_Random VBA function that
> copies the list of possible values to a private array, shuffles them a
> decent number of times by swapping a random pair of values and then
> returns the shuffled array each time the My_Random() is called until
> values run out when it should return #VALUE or some other "failed" flag.
>
> --
> Regards,
> Martin Brown- Hide quoted text -
>
> - Show quoted text -

Thanks for all your help :-)

I won't worry about the repeats right now, the formulas you've given
me will do what I need.

Thanks again

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Superblonde64 Microsoft Excel New Users 1 28th Feb 2010 03:13 AM DR Microsoft Dot NET Framework 1 22nd Nov 2007 12:36 PM DR Microsoft ADO .NET 3 22nd Nov 2007 10:17 AM =?Utf-8?B?YXJtaW4=?= Windows XP Help 1 11th Nov 2004 06:12 PM =?Utf-8?B?YXJtaW4=?= Windows XP Help 1 11th Nov 2004 04:28 PM

Features