Error in Excel 2003 FORECAST function

  • Thread starter Thread starter Jonathan B. Marder
  • Start date Start date
J

Jonathan B. Marder

I just found a bug in the FORECAST function of Excel 2003.
Excel Spreadsheets that function perfectly well in the Office XP version
report incorrect results under the Office 2003 version.
Here is an illustration of the error

Known_X Known_Y Unknown_X Forecast(Unknown_X,
$Known_Y_range, $Known_X_range)
1 4 0
4.4444 (should be 5)
2 3 1
3.8889 (should be 4)
3 2 2
2.7778 (should be 3)
4 1 3
2.2222 (should be 2)
5 4
2.2222 (should be 1)
6
1 4
2 3
3 2
4 1
5
6

I can see what Excel 2003 is doing - it is replacing BLANKS in Known_Y with
zero values. This is incorrect and inconsistent with earlier versions.

(Please mail me separately if you know how/where I should report this
properly, and if you know of a fix).


Jonathan B. Marder

Proneuron Biotechnologies (Israel) Ltd.
P.O.Box 277, Ness Ziona, 74101, Israel
Fax: +972 8 9409560 Voice: +972 8 9409550
E-mail: (e-mail address removed)
Web page: http://www.proneuron.com
 
RESENT BECAUSE OF FORMATTING ERRORS . . .
--
I just found a bug in the FORECAST function of Excel 2003.
Excel Spreadsheets that function perfectly well in the Office XP version
report incorrect results under the Office 2003 version.
Here is an illustration of the error


Known_X
Known_Y
Unknown_X
Forecast(Unknown_X, $Known_Y_range, $Known_X_range)

1
4
0
4.4444 (should be 5)

2
3
1
3.8889 (should be 4)



3
2
2
2.7778 (should be 3)



4
1
3
2.2222 (should be 2)

5

4
2.2222 (should be 1)

6




1
4



2
3



3
2



4
1



5




6






I can see what Excel 2003 is doing - it is replacing BLANKS in Known_Y with
zero values. This is incorrect and inconsistent with earlier versions.

(Please mail me separately if you know how/where I should report this
properly, and if you know of a fix).

Jonathan B. Marder

Proneuron Biotechnologies (Israel) Ltd.
P.O.Box 277, Ness Ziona, 74101, Israel
Fax: +972 8 9409560 Voice: +972 8 9409550
E-mail: (e-mail address removed)
Web page: http://www.proneuron.com
 
I agree with your "should be" values, but if all blank y values were
treated as zero, then the forcast values would be
4.6667
3.8095
2.9524
2.0952
1.2381

Perhaps you slightly mistated the contents of your spreadsheet?

Jerry
 
Jerry,

You are right that Excel 2003 Forecast is (mis)handling Blank cells as if
they contain zero values.
However, I definitely did NOT misrepresent my spreadsheet. The cells I
showed as blank are indeed blank.
Earlier versions of Excel (up to Office XP version) handled blank cells
properly, excluding them from the calculations.

I picked this problem up when an existing template started producing
innapropriate results on a new (Office 2003-equipped) computer.
I suggest you try this for yourself using the new Excel.

--
Jonathan Marder, Ph.D.
Proneuron Biotechnologies (Israel) Ltd.
P.O.Box 277, Ness Ziona, 74101, Israel
Fax: +972 8 9409560 Voice: +972 8 9409550 ext. 110

--
Proneuron Biotechnologies (Israel) Ltd.
P.O.Box 277, Ness Ziona, 74101, Israel
Fax: +972 8 9409560 Voice: +972 8 9409550
E-mail: (e-mail address removed)
Web page: http://www.proneuron.com
 
Sorry I was not clear; what you report as forecasts with empty known_y
cells, does indicate a bug in Excel's FORECAST function, but is NOT what
would be calculated by replacing the missing values with zeroes in the
data set that you reported. Perhaps there were additional decimal
places that you did not disclose? Perhaps some other reason.

You apparently reproduce your posted estimates if you use zeroes instead
of empty cells, but I do not, and cannot guess why without more
information from you. I do not have the new Excel to be able to try it
for myself, but would like clarity on what the issues are.

Your forecast issue is probably related to the slope and intercept issue
reported in
http://groups.google.com/[email protected]

Jerry
 
See my reply of a few minutes ago in the "Bug in SLOPE/INTERCEPT
formulas in Excel 2003" thread for how Excel 2003 incorrectly calculates
slope and intercept with missing values (it does NOT involve setting
missing values to zero). I would expect that forecast uses these
particular incorrect slope and intercept estimates.

After a few hours that post should be available from Google in the
following thread

http://groups.google.com/[email protected]

Jerry
 
Back
Top