Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel 2003's RAND() bug (2003)

    The latest Woody's OW 2003 outlined a bug with the RAND() function in Excel 2003. It also listed a way to reproduce. I have tried rep[roducing the bug and have been unable to do so even after pressing F9 as many as 30 times. Has anyone else tried reproducing this problem and if so were they successfull? The only thing I can think of that may be different about my install of Office is that I still have the prior version (XP) installed.

    Thanks
    Ed

  2. #2
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003's RAND() bug (2003)

    I tried it too and recalculated more than100 times. I upgraded from XP but don't still have it installed.
    Is it possible there was a "slipstream" upgrade? I upgrade about a month ago and have build 11.5612.5703

    Frank S.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel 2003's RAND() bug (2003)

    After some changes I was able to spot the change to a negative value. I had to change the cells format of the number display so that it displayed the complete value and did not use scientific notation. When it was using scientific notation the conditional formatting for the cell didn't work.

    Ed

  4. #4
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003's RAND() bug (2003)

    I initially set the number format to General with 5 decimal places with negative numbers appearing in red. I also set conditional formatting for all the cells in the work sheet to have a yellow background. Although I didn't check the whole work sheet after every recalc, I did display quite a bit of it on screen and didn't see any negative numbers.

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel 2003's RAND() bug (2003)

    I also have build 11.5612.5703 and was able to replicate the bug. I had about 16000 rand functions and had to recalculate in the region of 100 times before the bug reared it's ugly face. Up until the bug surfaced the average number produced was nearly always between 0.495 & 0.505, afterwards it dropped to about 0.33

    Using =ABS(RAND()) the average value was around 0.42 once the bug appeared so this is not really a suitable workaround pending a fix from Microsoft.
    As mentioned in Woody's Office 2003, the only way to reset the fault was to restart Excel.

  6. #6
    Lounger
    Join Date
    Oct 2001
    Location
    Erskine, Renfrewshire, Scotland
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003's RAND() bug (2003)

    Reference Woody

  7. #7
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003's RAND() bug (2003)

    I created another work sheet of 1500 rows by 26 columns and tried again. After hitting F9 to recalc well over a hundred times I didn't see any negative numbers. Also I would say it looked like a fairly random distribution of numbers, from the high .99's to just a little over 0 with everything in between--of course that's wasn't a very scientific count but it did look like a good distribution to me. I upped the size of the work sheet to 1500 rows by 52 columns and started pushing F9 again. After about 150 times or so I got tired of that so I used CTRL Y to repeat the recalc. After doing that for about 15-20 times I finally got the bug to kick in and about half the cells went negative and I continued to get negative cells until I shut Excel down and restarted. I tried again with a smaller work sheet but using CTRL Y some of the time to recalc, that seemed to make the bug show up. It doesn't look like that's what it takes anybody else to reproduce the bug, but that's how it worked for me.
    As an aside here, while I was watching the work sheet recalc as quick as I pushed F9, I thought back to the days when I was using Lotus V2.1 on an 8MHZ 80286 PC with a math coprocessor and with a work sheet that was nowhere near that big, hitting recalc and being able to go for coffee and come back and the recalc still wasn't done. I guess we have come along some.

    Frank S.

  8. #8
    Lounger
    Join Date
    Oct 2001
    Location
    Erskine, Renfrewshire, Scotland
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003's RAND() bug (2003)

    File is too big - if you want a copy, send mail to tconroy@dsl.pipex.com

  9. #9
    New Lounger
    Join Date
    Dec 2003
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003's RAND() bug (2003)

    Increase the number of cells with Rand(). This will of course decrease the number of times you need to press F9.

    Try 20 entire columns of RAND(). You should get the bug manifested rather quickly.

    E Takasaki

  10. #10
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003's RAND() bug (2003)

    Thanks, that makes it show up pretty quick.

  11. #11
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel 2003's RAND() bug (2003)

    Microsoft has issued the following statement regarding the RAND bug.

    <hr>Hi all.

    I just wanted to give you some information on this concern direct from Microsoft. In addition, this problem was mentioned in Woody's Watch, and we wanted to provide some speaking points to that piece.

    Microsoft has recently learned of a flaw that affects random number returns in Microsoft Office Excel 2003's RAND and RANDBETWEEN function. In some cases, this may result in inaccurate data. Microsoft is currently in the process of developing and testing a fix for the Excel flaw, which will be made available to customers worldwide in the near future.

    How did such a glaring bug survive the in-house testing that was presumably done?
    Microsoft makes every effort to identify and fix software bugs prior to product shipment, but these reports are the reality of software development. Through new technologies such as Watson and the new customer connection tools, for example, Microsoft is able to better respond to feedback and fix bugs in a timely manner.

    Why were multiple notifications of this problem to Microsoft ignored?
    The bug is easily replicated so there would seem to be no good reason for the issue not to have been escalated promptly. We are still investigating what reports were actually received by Microsoft. At present it appears that certainly we weren't aware of this prior to RTM, which would have enabled us to fix the bug much earlier.

    What steps will Microsoft take to fix this bug and advise customers of the solution?
    Microsoft is currently in the process of developing and testing a fix for the Excel flaw, which will be made available to customers worldwide in the near future.

    What steps will Microsoft take to ensure that any future Excel problems are properly addressed?
    Microsoft is continually looking for ways to improve processes and make customers' software experience better. A key part of the software development process is being able to involve customers in product testing. Through new technologies such as Watson and the new customer connection tools, for example, Microsoft is better able to respond to customer feedback and fix bugs in a timely manner.

    Sincerely,

    Rita Nikas
    Microsoft MVP Lead
    Microsoft Corporation<hr>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •