And how exactly is this *your* mistake and not Excel's???? Yet another argument to use Excel for data preparation and simple calculations, and a *real* stats program for analysis.
If you haven't tried yet, I highly recommend R (roughly speaking, open-source S). See http://www.r-project.org/ This runs very well under Windows (as well as Linix, FreeBSD, OS/X) and is the product of the best professional statistical minds. See the project page for more info. You can easily import your Excel-prepared data with the read.csv() function: read.csv(file, header = TRUE, sep = ",", quote="\"", dec=".") and then do real statistics on it. "Ronny Richardson" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > 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/ . =================================================================
