Newsgroups: bit.listserv.stat-l
Date:         Mon, 25 Jan 1993 10:18:11 -0800
From:         JAMES@UCRAC1.UCR.EDU
Subject:      excel: an example of how NOT to send/apply zaps

This somehow makes me think of that banal saying that 'When the only tool you
have is a hammer, all problems look like nails' (or something like that)...
Anyway folks who insist on using spreadsheets for statistical analyses might
possibly be interested in this.

What a nightmare it would be if fixes/zaps/patches to major statiscal packages
were distributed in similar fashion to that apparently used by Excel!

james ssemakula
u.c. riverside

------------------- begin forwarded  message-----------------------------------
From:   SMTP%"BSCS-L%EMUVM1.BitNet@pucc.PRINCETON.EDU" 25-JAN-1993 00:32:31.77
To:     JAMES
CC:
Subj:   EXCEL 4.0 REGRESSION BUG AND FIX

Date:         Sun, 24 Jan 1993 21:56:00 EST
Reply-To:     Business School Computing Support
 <BSCS-L%EMUVM1.BitNet@pucc.PRINCETON.EDU>
 <BSCS-L%EMUVM1.BitNet@pucc.PRINCETON.EDU>
From:         "Eric R. Fliegel" <EFLIEGEL%EMUBUS.BitNet@pucc.PRINCETON.EDU>
Subject:      EXCEL 4.0 REGRESSION BUG AND FIX
To:           Multiple recipients of list BSCS-L
 <BSCS-L%EMUVM1.BitNet@pucc.PRINCETON.EDU>

>>> I THOUGHT SOME OF YOU WOULD BE INTERESTED IN THIS.
    HERE IS THE MESSAGE THAT MY EXCEL CONTACT SENT TO HIS TECHNICAL GROUP:

Eric Fliegel at Emory University is reporting the following two problems in
Excel's regression function:

1. It is possible for an independent variable to be dependent on
another independent variable without Excel generating an error message.
 It seems Excel should not allow a user to do this.

2. A regression is run on the following data:

X - independent variable                Y - dependent variable
=====================           ====================
.6                              220
.62                             200
.58                             280
.6                              250
.64                             190
.62                             240
.

When Excel runs this regression, Excel reports the following values:

Significant F = .0301
P Value = .0216

According to Emory, the Significant F and P Values should be the same.
SAS reports the values as being the same on the identical data set.

>>> HERE WAS THEIR RESPONSE:

We have some input on one of the problems you reported in Excel's
regression function.  Our description of the problem as it appears in
our database is listed below.  Also listed are changes that can be made
to the Excel Add-in Macro REGRESS.XLA which will correct the problem
(REGRESS.XLA) is the macro used to perform the regression in Excel. We
plan to implement the fix below in the next version of Excel.

Database entry is as follows:

The P-value output from the ATP Regression procedure is incorrect.
This problem was tracked down by one of our customers to cells B383 and
B391 in REGRESS.XLA.  Changing the 12,1,1,1 strings in the OFFSET()
formulas in these two cells to 11,1,1,1 should correct the problem with
the P-value and also cause the P-value to match the Significance F value.

Line 383 is:
=FORMULA(OUTPUT(TDIST(ABS(OFFSET(TEXTREF(soutrng_txt),16,3,1,1)),OFFSET
(TEXTREF(soutrng_txt),12,1,1,1),2)),OFFSET(TEXTREF(soutrng_txt),16,4,1,1))

Should be:
=FORMULA(OUTPUT(TDIST(ABS(OFFSET(TEXTREF(soutrng_txt),16,3,1,1)),OFFSET
(TEXTREF(soutrng_txt),11,1,1,1),2)),OFFSET(TEXTREF(soutrng_txt),16,4,1,1))

And line 391 is:
=FORMULA(OUTPUT(TDIST(ABS(OFFSET(TEXTREF(soutrng_txt),regress_rows1+
Counter,3,1,1)),OFFSET(TEXTREF(soutrng_txt),12,1,1,1),2)),OFFSET(TEXTREF(
soutrng_txt),regress_rows1+Counter,4,1,1))

And should be:
=FORMULA(OUTPUT(TDIST(ABS(OFFSET(TEXTREF(soutrng_txt),regress_rows1+
Counter,3,1,1)),OFFSET(TEXTREF(soutrng_txt),11,1,1,1),2)),OFFSET(TEXTREF(
soutrng_txt),regress_rows1+Counter,4,1,1))

>>> FOR YOUR INFORMATION, TO EDIT AN ADD-IN (.XLA) FILE, HOLD THE <SHIFT> KEY
DOWN WHEN YOU SELECT THE FILE.  THE REGRESS.XLA FILE IS IN THE
\EXCEL4\LIBRARY\ANALYSIS SUBDIRECTORY.  SIMPLY MAKE THE CHANGES TO LINES 383
AND 391 AND IT WORKS!  FOR MACINTOSH USERS, THE FILE REGRESSION IS IN THE
ANALYSIS TOOLS FOLDER WHICH IS IN THE MACRO LIBRARY FOLDER WHICH IS IN THE
EXCEL4 FOLDER.  NOTE THAT THE LINES TO MODIFY ARE 384 AND 392.  HOPE THIS
HELPS.  ERic Fliegel
------------------------end forwarded message----------------------------
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU