RAND() in Excel 2003

Themes: Excel statistical functions Excel techniques Spreadsheet errors

See the list of related resources at the bottom of this page.

It appears that a bug crept into the final release of Excel 2003 that wasn't present in the betas. The RAND() function in Excel 2003 was apparently upgraded so that it produced a better distribution of pseudo-random numbers between 0 and 1. The trouble is that the numbers it produces may be more random, but aren't always in the correct range; sometimes they are negative. The problem affects both the RAND() and RANDBETWEEN() functions.

Microsoft have now (January 2004) released a hotfix (their term, not mine) that they claim fixes the problem. They also claim that it fixes several other problems, a number of which they had not previously mentioned. Some of these problems cause Excel to quit unexpectedly; at least it's obvious to the user when this happens (although you may lose your work). Others are more subtle:

Sometimes the cells in a range are not actually updated when the range is recalculated.
When you use a VBA macro to calculate your worksheet, a custom function from a different worksheet may appear to run.
When you create multilevel subtotals for your data in an Excel 2003 worksheet, the totals may appear staggered incorrectly, and may exclude grand totals for some functions.

The hotfix is not downloadable. You have to contact Microsoft and convince them that you need it. Also, the installation process includes editing the registry by hand.

Resources

Excel 2003 hotfix package: Microsoft Knowledge Base Article 833618 gives details of the hotfix released on 12th January 2004. This hotfix corrects the bug in the RAND() and RANDBETWEEN() functions as well as some other problems. It is available at http://support.microsoft.com/default.aspx?kbid=833618.

	:: About :: Blog :: Notes :: All topics :: All links :: Publications :: Downloads :: Contact Related topics: Statistical functions in Excel 2003 Defining Names Lookups in Excel Using Names in charts Dynamic Ranges Using Names for formulae Names can be local to worksheets Nevada City budget problems TransAlta Corporation Pasting error Project financing spreadsheets Bookmarks in Excel Kodak earnings restatement Fannie Mae $1.2bn honest mistake Provident Financial modelling problem Spreadsheet error rates What is a bug? Read my blog. Is your software use Competent, Complacent or Compromised? Take my 30 second quiz now, and find out! Read past issues of my newsletter. Tools download a simple code profiler for Excel VBA. Automated spreadsheet testing, documentation and more. Download now, or find out more at http://www.xlsior.com. Search WWW Search louisepryor.com
RAND() in Excel 2003 Themes: Excel statistical functions Excel techniques Spreadsheet errors See the list of related resources at the bottom of this page. It appears that a bug crept into the final release of Excel 2003 that wasn't present in the betas. The RAND() function in Excel 2003 was apparently upgraded so that it produced a better distribution of pseudo-random numbers between 0 and 1. The trouble is that the numbers it produces may be more random, but aren't always in the correct range; sometimes they are negative. The problem affects both the RAND() and RANDBETWEEN() functions. Microsoft have now (January 2004) released a hotfix (their term, not mine) that they claim fixes the problem. They also claim that it fixes several other problems, a number of which they had not previously mentioned. Some of these problems cause Excel to quit unexpectedly; at least it's obvious to the user when this happens (although you may lose your work). Others are more subtle: Sometimes the cells in a range are not actually updated when the range is recalculated. When you use a VBA macro to calculate your worksheet, a custom function from a different worksheet may appear to run. When you create multilevel subtotals for your data in an Excel 2003 worksheet, the totals may appear staggered incorrectly, and may exclude grand totals for some functions. The hotfix is not downloadable. You have to contact Microsoft and convince them that you need it. Also, the installation process includes editing the registry by hand. Resources Excel 2003 hotfix package Microsoft Knowledge Base Article 833618 gives details of the hotfix released on 12th January 2004. This hotfix corrects the bug in the RAND() and RANDBETWEEN() functions as well as some other problems. It is available at http://support.microsoft.com/default.aspx?kbid=833618.
Copyright © 2003-2008 Louise Pryor. All rights reserved. Site developed by Louise Pryor. Louise Pryor is no longer practising as a consultant — the site is maintained as an archive, and is not updated.