percentrank : multiple errors in documentation
Submitted by John Denker
Link to original bug (#761107)
Description
These remarks apply to the web documentation and to the interactive gnumeric gui.
The current web documentation is observed at: https://help.gnome.org/users/gnumeric/stable/CATEGORY_Statistics.html.en#gnumeric-function-PERCENTRANK
Current:
If array contains no data points, this function returns a #NUM! error.
Should be:
If array contains no data points, this function returns a #N/A error.
Current:
If significance is less than one, this function returns a #NUM! error.
Should be:
If significance is less than zero, this function returns a #NUM! error.
Current:
If x exceeds the largest value or is less than the smallest value in array, this function returns a #NUM! error.
Should be:
If x exceeds the largest value or is less than the smallest value in array, this function returns a #N/A error.
Current:
If x does not match any of the values in array or x matches more than once, this function interpolates the returned value.
Should be:
If x falls between two values in the array, the result is computed by linear interpolation as described below.
If there is a tie, i.e. if x matches more than data point in the array, this function returns the lowest rank of any of the matched elements.
============
Should be added:
In the array, blank cells and string-valued cells are ignored and do not count toward
N
, the number of "data points"
The result is always in the range [0, 1]. If x matches the smallest element of the array, the result is 0. If x matches the largest element of the array and is not tied, the result is 1.
Suppose some data point with value
V
is part of anM
-way tie, and suppose it corresponds to some percentrank result we denoteRlow
. Then if x is infinitesimally larger thanV
, the result isRhigh = Rlow + (M-1)/(N-1)
, whereN
is the number of valid data points in the array.
This makes sense insofar as it is 1/(N-1)
less than the result for
the next-larger data point in the array, i.e. Rnext = Rlow + M/(N−1).
If x falls in the interior of the open interval between V
and the
next-larger data point, the result is computed by linear interpolation,
starting from Rhigh and ending at Rnext.
See also https://www.av8n.com/physics/spreadsheet-tips.htm#sec-percentrank
Version: 1.12.x