"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.
> > 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 ---------------------------------------------------------------------------- ----------------------- This is a normal EXCEL process of updating the worksheet when ever any values are entered/changed manually. The HELP responses talk about this for random numbers, such as shutting off the normal updating operation. The repeated updating is ideal for exploratory spreadsheet studies, where you always get an instantaneous re-calculated value for each cell. I prefer to enter a set of random numbers with the constant updating to a column (or row) and continue data/model entry, then stoping the updating, to get a fixed value. Given the very large period of RAND, the repeated recalculation of the random number set results in a more random set. You get a pretty good set if you pick out every seventh random number and use only these values. David Heiser > > 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/ . =================================================================
