Another good reason NOT to use Excel for any statistical analysis. Jon Cryer
At 09:26 AM 12/24/2002 -0500, you wrote:
After much experimenting, I believe that the mistake that generated the negative sums of squares was my mistake and not any problem with Excel. Let me explain. As I said, I created a 3x3 grid of random numbers that were generated with =RAND() and fed these numbers into ANOVA and I get negative sums of squares about half the time when I repeat the experiment over-and-over. However, if I highlight the random numbers and do Edit/Copy and then Edit/Paste Special (values) to convert them to specific numbers, I never get negative sums of squares. I just repeated the experiment at least 50 times and no negative SS's. I have no way to be 100% sure, but what I think is happening is that Excel takes more than one pass at the data to calculate ANOVA so if I leave the =RAND() formulas in the cells, then the worksheet recalculates between these multiple passes and so Excel finishes the calculations with a different set of random numbers than which it starts. When Excel places the ANOVA results into the worksheet, it does recalculate and so the random numbers do change then, I am just unable to determine if they change in between the start of the problem and placing the ANOVA results into the worksheet. Ronny Richardson At 12:37 PM 12/21/2002 -0500, you wrote: >On Sat, 21 Dec 2002, Ronny Richardson wrote in part: > >> First, in experimenting around with a 3x3 ANOVA without replication, >> I filled the cells with =RAND() to generate random numbers and I >> happened to get the following: >> >> SS df MS F >> Rows -2.105562167 2 -1.052781083 -1.336268078 >> Columns -0.226230909 2 -0.113115455 -0.143574551 >> Error 3.151406819 4 0.787851705 >> Total 0.819613743 8 > >Your intuition is correct: negative results for SS are not possible. >I have heard (some folks have documentation, I gather) that EXCEL's >statistical algorithms sometimes leave something to be desired; perhpas >this is an instance. OTOH, it is imaginably a rounding problem, in >subtracting two numbers whose difference is near zero, so that one is >losing the most significant digits in each value in the subtraction, and >is left with an unreliable difference. Hard to tell without the actual >numerical data being used, as you point out below. > >> Unfortunately, I did not convert the random number functions to >> numbers so the specific data I used to generate this table was lost >> when the worksheet recalculated. >> >> I've repeated this experiment several times and sometimes the sums >> of squares are positive and sometimes one or more of them are >> negative. Is this a bug in Excel? I don't see how a sum of *square* >> can be negative even though I am using random numbers so all of the >> SS should be in error. .. .. ================================================================= Instructions for joining and leaving this list, remarks about the problem of INAPPROPRIATE MESSAGES, and archives are available at: .. http://jse.stat.ncsu.edu/ . =================================================================
. . ================================================================= Instructions for joining and leaving this list, remarks about the problem of INAPPROPRIATE MESSAGES, and archives are available at: . http://jse.stat.ncsu.edu/ . =================================================================
