9/26/12

Original Poster

Bas Braams# Errors in the spreadsheets program

This posting condenses some earlier reports of errors in the Google Spreadsheets program. All these error reports are still valid today and specific examples are provided in a spreadsheet [0, 0'] that is available to anyone with the link and that was produced jointly with @APL+. That sheet is organized into a Main sheet and 12 further sheets, with titles:

ElemOps: Elementary operations

ElemFuncs: Elementary functions

SearchFuncs: Sorting and search and match functions

GoogleFuncs: Google functions

DBFuncs: Database functions

MathElem: Elementary mathematical operations

MathFuncs: Mathematical functions

StatDists: Statistical distributions

StatAnal: Statistical analysis functions

Finance: Financial operations and functions

ImpExp: Import and export

Docum: Documentation

It is not totally polished, but the principal intended content is all there.

User reports about problems with the Google Spreadsheets functions for searching and matching in sorted arrays [1-3] have been met with the advice that searching and matching in sorted arrays is buggy and one should just treat the arrays as unsorted in order to work around the problems. In [2] user ahab wrote: “I think there is a real bug in MATCH probably to do with a binary search [...] not having been implemented properly.” Indeed the Google Spreadsheets functions for searching and matching in sorted arrays (basic functions match, hlookup and vlookup) are messed up in a way that strongly suggests erroneous implementation of the binary search algorithm. Specifically, an invocation of the match or vlookup function for an array of N elements may access the element in the N+1 position and if the value there constitutes a match then that match defines the return value of the function. There are additional issues with the treatment of blank values, with treatment of arrays of length 1 even if they are not specified as being sorted, and the behavior of the hlookup function is erroneous beyond description.

In the area of statistical distributions, functions for statistical analysis and higher mathematical functions nothing in Google Spreadsheets can be trusted. Errors include the following [4-5]. The complementary error function erfc(x) is evaluated using erfc(x)=1-erf(x) also where erf(x) is close to 1; on top of this a poor implementation of erf is used so that erfc(0).neq.1. The standard deviation and the variance are evaluated via average of the squares minus square of the average. Correlations and regressions are evaluated without centering the data first, probably using the normal equations on uncentered data. The multinomial is evaluated factor-by-factor, causing needless overflow. Discrete probability densities such as poisson(n,x) = pow(x,n)*exp(-x)/n! are also evaluated factor-by-factor, causing needless overflow. The factorial function itself is evaluated somehow through Stirling's approximation factor-by-factor, causing further needless overflow, or maybe table lookup is done in a table that was prepared in such a bad way. The cumulative normal distribution is profoundly wrong; it goes outside the [0,1] range already at 5 standard deviations and the approximation looks nonsensical even earlier. The coders were apparently not aware of the connection to the error function and I suspect that normsdist is evaluated through some poorly implemented direct numerical quadrature. The cumulative Poisson and cumulative binomial distribution both fail the normalization property; it appears that they are evaluated by explicit summation and the normalization property fails due to the accumulated round-off. There is a general loss of accuracy for implementations of any functions in Google Spreadsheets relative to implementations of the same functions in other spreadsheet programs.

The elementary mathematical functions in Google Spreadsheets are another mess [6]. The descriptions of functions such as rounding are unintelligible; basically the descriptions might as well say “whatever the name suggests, try it out if you need to know more”. The implementations are wrong whatever the meaning of the documentation; e.g. when rounddown (to integer) is applied to the exactly representable integer N=2^52 then the GSheets result is N+1, when rounddup is applied then the result is N-1 and when round is applied then the result is N+2. The correct result would be N in all cases. (I can guess what went wrong for round: the coders thought to use a hack whereby the evaluation of round(x,0) for positive x involves a preliminary substitution x->(1+macheps)*x so that ties may get resolved in the desired direction.) The error with rounding to integer propagates into rounding to a fixed number of decimal places behind the dot; e.g. for 6 decimal places, round(2^32,6) returns 2^32+2^(-20). In a separate issue of elementary arithmetic small numbers are corrupted on entry in an undocumented way as they are rounded to a multiple of 1e-15; e.g. see [7-8].

It is strange and disturbing for me to see errors such as those documented here and in [0-8] being made in a company that supposedly has access to the best graduates of computer science programs. It is more than strange to see the broad structural pattern of errors and to see how these errors survive all attempts to have them corrected.

[0] (Google Spreadsheet under development) Issues with Google Spreadsheets

[0'] (2012-09-25) Issues with Google Spreadsheets: frozen pdf version produced on 2012-09-25

[1] (2011-06-25) VLOOKUP function does not work when you include more than 2 rows in the function after last row of data in a sheet

[2] (2012-01-06) VLOOKUP bug

[3] (2012-07-13) VLookup only works for a certain number of rows

[4] (2011-11-13) normdist throws negative value still

[5] (2012-05-06) Errors and other issues with statistical and mathematical functions in GSheets

[6] (2011-07-24) rounding error in google spreadsheets

[7] (2009-01-13) Scientific Notation

[8] (2012-07-05) Scientific notation still has a problem

Community content may not be verified or up-to-date. Learn more.

All Replies (3)

9/26/12

GillThank you, Bas.

1/2/13

Original Poster

Bas BraamsAll the issues raised in the OP here are still issues on 2013-01-02. I refer also to [1-3].

[1] (2011-11-13 and later) normdist throws negative value still

http://productforums.google.com/d/topic/docs/XfBPtoKJ1Ws/

[2] (2012-05-06) Errors and other issues with statistical and mathematical functions in GSheets

/docs/forum/AAAABuH1jm0rxFCHYeMhrU/

[3] (2012-07-22) Trouble with searching and matching; functions SORT, MATCH, HLOOKUP and VLOOKUP

http://productforums.google.com/d/topic/docs/BnujI7D52IM/

[1] (2011-11-13 and later) normdist throws negative value still

http://productforums.google.com/d/topic/docs/XfBPtoKJ1Ws/

[2] (2012-05-06) Errors and other issues with statistical and mathematical functions in GSheets

/docs/forum/AAAABuH1jm0rxFCHYeMhrU/

[3] (2012-07-22) Trouble with searching and matching; functions SORT, MATCH, HLOOKUP and VLOOKUP

http://productforums.google.com/d/topic/docs/BnujI7D52IM/

9/7/13

Original Poster

Bas BraamsAll the errors described earlier in this posting are still errors on 2013-09-07. These errors include binary search in an N-element sorted array returning the N+1 element, round to integer returning a non-integer or the wrong integer, cumulative distribution functions returning a negative value, standard deviation and variance functions implemented in known bad ways, error function at 0 not equal to 0, gratuitous overflow errors in evaluation of discrete probability functions and combinatorial functions, and function descriptions that read like "we haven't a clue, try it out".

Additional errors with the CEILING and FLOOR functions were identified by Yogi Anand [1-2] since the previous posting here, and the situation with those functions appears to be the following [3]. Let N be a nonnegative integer (but not so large that roundoff issues further complicate matters). If 0<x<=1e-7 then Google Sheets CEILING(N+x,1) returns the value N although a return value N+1 would be expected and FLOOR(N+1-x,1) returns N+1 although a return value N would be expected.

My reporting of major numerical and logical errors in the Spreadsheets program goes back to November 2011 [4] and the errors themselves go back much further; for all I know they were created at inception of the program. The persistence of these errors makes sense to me only under the assumption that development of the Spreadsheets program has stopped. I think that this is so, and (for similar reasons) I think that also development of the Docs text editor side of the product family has stopped; I explain under [5].

[1] yogi_Some Anomalies In Use Of CEILING Function Comparing Google Spreadsheet Excel And OpenOffice

[2] (2013-03-13) Re: Is there anyway to round up?

[3] (2013-05-20) Re: (2012-05-06) Errors and other issues with statistical and mathematical functions in GSheets

[4] (2011-11-13) normdist throws negative value still

[5] (2013-04-14) Prediction: Docs text editor and spreadsheets to be frozen and abandoned in favor of Quickoffice and other products

Were these replies helpful?

How can we improve them?

This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

### Badges

Some community members might have badges that indicate their identity or level of participation in a community.

Community content may not be verified or up-to-date. Learn more.

### Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

- Post an answer.
- Having your answer selected as the best answer.
- Having your post rated as helpful.
- Vote up a post.
- Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

### View profile in forum?

To view this member's profile, you need to leave the current Help page.

### Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

### Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.