RAND() and RANDBETWEEN() not working - Returning values outside defined range

[jsl]

I use RAND() frequently with parameters such that I don't get generated
numbers that will cause nonsensicle returns given their use. I've been
using this successfully for a long time. Now, they are no longer working.
The only difference I know of is that I am now using Office 2003 instead of
Office XP (2002?). In any event, here are some specifics.
I used to use:
=ROUND(RAND()*(25-1)+1,0)
to generate a random integer between 1 and 25, inclusive. Worked fine.
Until now that is. Now, it often returns negative values, which I don't
want. I changed it to min(ROUND(RAND()*(25-1)+1,0),1) as a workaround, but
that didn't work either. I then discoverd the RANDBETWEEN() function in the
Analysis Toolpack, so I tried it:
=ROUND(RANDBETWEEN(1,25),0)
or
=ROUND(RANDBETWEEN(100,500),-2)
No dice. I often get zeros returned, which don't fly. Anybody have any
idea if I am doing something wrong or if this is a serious bug in Excel
2003?

Thanks,
Jim

 

[Frank Kabel]

Hi
this is an known bug in Excel 2003 (see http://tinyurl.com/3yqkw for a
discussion about this).

A repost 8including a hotfix location9:
-----
Hi all.

I just wanted to let you know that we do have Microsoft Knowledge Base
(KB)
articles related to the RAND problem and a **released fix** for the
RAND
issue (it actually addresses a couple of other concerns, too) is now
available.

KB 834520 specifically talks about RAND returning negative numbers and
refers to the fix available as part of 833618. If you'd like to review
these articles for more information, please see:

http://support.microsoft.com/?kbid=834520 (for the RAND problem
specifically)
http://support.microsoft.com/?kbid=833618 (for the fix)

Please also know that you can contact Product Support Services through
several different support options:

http://support.microsoft.com/default.aspx?scid=sz;[ln];top

 

[Tony]

It's a known bug, see:
http://support.microsoft.com/default.aspx?kbid=834520
http://support.microsoft.com/default.aspx?kbid=833618

hth
Tony

 

[jsl]

I post a follow-up to the group for possible consideration... I wonder if
Excel can't handle these type of calculations in *large* spreadsheets. That
is, on a hunch, I shrunk my spreadsheet down considerably from about 12 megs
(45 large workbooks with tons of RAND() calcs in them) to about 4 megs (15
large workbooks with tons of RAND() functions in them). Now, the problem
described below doesn't seem to be occuring. Does this make any sense to
anyone?
Thanks,
Jim

 

[jsl]

All - Thanks for the known bug report - don't know why I couldn't find it.
Guess I don't know how to properly search for them.

The responses happened so fast that I posted a follow-up before I saw them.
Thanks again!

Jim

 

[Tony]

The problem occurs after a large number of RAND calculations (in the order
of 10s or 100s of thousand calcs) Even with the smaller files if you
recalculate enough times the negative random numbers will appear. Once the
bug rears it's ugly head you have to quit Excel and re-start it to
temporarily fix the problem.

Tony