# Difference between revisions of "Calc/ODFF Implementation/Examine functions"

From Apache OpenOffice Wiki

< Calc | ODFF Implementation

(→Statistical Functions) |
(→Statistical Functions) |
||

Line 10: | Line 10: | ||

|- | |- | ||

! BETADIST | ! BETADIST | ||

− | | Draft Spec misses faktor 1/(b-a) in densitiy case || not yet implemented || Inform [[User:ErAck|Eike Rathke]] || | + | | Draft Spec misses faktor 1/(b-a) in densitiy case || not yet implemented || Inform [[User:ErAck|Eike Rathke]] || || |

|- | |- | ||

| ||Has density case and optional parameter, to choose between density and cumulative || not yet implemented || expand function to density case and additional parameter || tracked in {{Bug|91547}}, function code is ready, finishing implementation depends on {{Bug|91602}}|| | | ||Has density case and optional parameter, to choose between density and cumulative || not yet implemented || expand function to density case and additional parameter || tracked in {{Bug|91547}}, function code is ready, finishing implementation depends on {{Bug|91602}}|| | ||

Line 16: | Line 16: | ||

|- | |- | ||

! BINOMDIST | ! BINOMDIST | ||

− | | Parameter C is integer with constraint to 0 and 1. It should be Logical as in other distributions || C=0 is density, all other values of C are treated as cumulative || | + | | Parameter C is integer with constraint to 0 and 1. It should be Logical as in other distributions || C=0 is density, all other values of C are treated as cumulative || || || |

|- | |- | ||

Line 32: | Line 32: | ||

|- | |- | ||

! EXPONDIST | ! EXPONDIST | ||

− | | Parameter ''Cumulative'' has type Logical and is optional. || Last Parameter C has type Number and is not optional. C=0 returns denisity, other values for C return cumulative function. || | + | | Parameter ''Cumulative'' has type Logical and is optional. || Last Parameter C has type Number and is not optional. C=0 returns denisity, other values for C return cumulative function. || || || |

|- | |- | ||

Line 42: | Line 42: | ||

|- | |- | ||

! FINV | ! FINV | ||

− | | inverse of new FDIST || not yet implemented || | + | | inverse of new FDIST || not yet implemented || || || |

|- | |- | ||

− | | || || Name conflicts with actual FINV, which calculates inverse for right tail. || get new UI-name || | + | | || || Name conflicts with actual FINV, which calculates inverse for right tail. || get new UI-name || || |

|- | |- | ||

! GAMMA | ! GAMMA | ||

− | | Parameter is constraint to >= 0 || Gamma is definied for all real numbers, but non-positive integers || Inform [[User:ErAck|Eike Rathke]] || | + | | Parameter is constraint to >= 0 || Gamma is definied for all real numbers, but non-positive integers || Inform [[User:ErAck|Eike Rathke]] || || |

|- | |- | ||

| || ||not yet implemented || prepare implementation using full domain. Add UI texts. || path in {{Bug|94555}}|| | | || ||not yet implemented || prepare implementation using full domain. Add UI texts. || path in {{Bug|94555}}|| | ||

|- | |- | ||

− | | ||is in subgroup Mathematical functions || will go to subgroup Statistical functions || | + | | ||is in subgroup Mathematical functions || will go to subgroup Statistical functions || || || |

|- | |- | ||

! GAMMALN | ! GAMMALN | ||

− | | is in subgroup Mathematical Functions || is in subgroup Statistical Functions || | + | | is in subgroup Mathematical Functions || is in subgroup Statistical Functions || || || |

|- | |- | ||

Line 66: | Line 66: | ||

|- | |- | ||

! GROWTH | ! GROWTH | ||

− | | Last parameter has type Logical (in Excel too) || Last parameter has type Number || | + | | Last parameter has type Logical (in Excel too) || Last parameter has type Number || || || |

|- | |- | ||

− | | ||is in subgroup Statistical Functions (in Excel too) || is in subgroup Array Functions || | + | | ||is in subgroup Statistical Functions (in Excel too) || is in subgroup Array Functions || || || |

|- | |- | ||

! HYPGEOMDIST | ! HYPGEOMDIST | ||

− | | Optional, logical parameter to calculate "exact" or "at most" || no parameter, calculates always "exact" || | + | | Optional, logical parameter to calculate "exact" or "at most" || no parameter, calculates always "exact" || || || |

|- | |- | ||

! INTERCEPT | ! INTERCEPT | ||

− | | Current, "Calculates the point at which a line will intersect the y-values by using known x-values and y-values." (BTW: That is literally from Excel help.) Problems:1. It calculates no point but a single value. 2. The line does not intersect the y-values but the y-axis. 3. It does not say, that the line is the linear regression of the known values.|| | + | | Current, "Calculates the point at which a line will intersect the y-values by using known x-values and y-values." (BTW: That is literally from Excel help.) Problems:1. It calculates no point but a single value. 2. The line does not intersect the y-values but the y-axis. 3. It does not say, that the line is the linear regression of the known values.|| || || || |

|- | |- | ||

! NORMDIST | ! NORMDIST | ||

− | | Last Parameter has type Logical and is optional || Last Parameter has type Number and is required || | + | | Last Parameter has type Logical and is optional || Last Parameter has type Number and is required || || || |

|- | |- | ||

! LEGACY.NORMSDIST | ! LEGACY.NORMSDIST | ||

− | | "This is exactly NORMDIST(x)." Should be "This is exactly NORMDIST(x;0;1)." because mean parameter and standard deviation parameter are not optional.|| | + | | "This is exactly NORMDIST(x)." Should be "This is exactly NORMDIST(x;0;1)." because mean parameter and standard deviation parameter are not optional.|| || || || |

|- | |- | ||

! PERCENTRANK | ! PERCENTRANK | ||

− | | It has an optional parameter ''Significance''||not yet implemented|| | + | | It has an optional parameter ''Significance''||not yet implemented|| || || |

|- | |- | ||

! PHI | ! PHI | ||

− | | Description is unclear. PHI(x)=NORMDIST(x;0;1;FALSE()). It is the density function of the standard normal distribution || | + | | Description is unclear. PHI(x)=NORMDIST(x;0;1;FALSE()). It is the density function of the standard normal distribution || || || | |

|- | |- | ||

! POISSON | ! POISSON | ||

− | | Parameter ''Cumulative'' has type Logical and is optional.|| Parameter 'C' is required. 'C'=0 is density function, all others are cumulative|| | + | | Parameter ''Cumulative'' has type Logical and is optional.|| Parameter 'C' is required. 'C'=0 is density function, all others are cumulative|| || || |

|- | |- | ||

Line 104: | Line 104: | ||

|- | |- | ||

! RANK | ! RANK | ||

− | | Description "If not 0, Data is ranked in descending order." has to be "If not 0, Data is ranked in ascending order."|| | + | | Description "If not 0, Data is ranked in descending order." has to be "If not 0, Data is ranked in ascending order."|| || || || |

|- | |- | ||

! SKEWP | ! SKEWP | ||

− | | | + | | || not yet implemented|| || || |

|- | |- | ||

! STDEVA | ! STDEVA | ||

− | | The text "cells with text are converted to 0;" conflicts with "The handling of strings is implementation defined."|| | + | | The text "cells with text are converted to 0;" conflicts with "The handling of strings is implementation defined."|| || || || |

|- | |- | ||

! TDIST | ! TDIST | ||

− | | Draft spec shows the density function. || Calc (and Excel) evaluates the cumulative function. || Inform [[User:ErAck|Eike Rathke]] || | + | | Draft spec shows the density function. || Calc (and Excel) evaluates the cumulative function. || Inform [[User:ErAck|Eike Rathke]] || || |

|- | |- | ||

! TREND | ! TREND | ||

− | | The optional parameters ''knownX'' and ''newX'' may be empty, denoted as ;;|| ''knownX'' and ''knownY'' cannot be empty|| | + | | The optional parameters ''knownX'' and ''newX'' may be empty, denoted as ;;|| ''knownX'' and ''knownY'' cannot be empty|| || || |

|- | |- | ||

− | | ||Parameter ''AllowOffset'' is Logical|| Parameter is 0 or ''not 0''|| | + | | ||Parameter ''AllowOffset'' is Logical|| Parameter is 0 or ''not 0''|| || || |

|- | |- | ||

! TRIMMEAN | ! TRIMMEAN | ||

− | | The limits in the definition formula are wrong. Either count i from 0, then sum from i=cutOff to n−1−cutOff, or count i from 1, then sum from i=cutOff+1 to n−cutOff || | + | | The limits in the definition formula are wrong. Either count i from 0, then sum from i=cutOff to n−1−cutOff, or count i from 1, then sum from i=cutOff+1 to n−cutOff || || || || |

|- | |- | ||

! TTEST | ! TTEST | ||

− | | (1) All formulas miss the degrees of freedom. It is necessary for TDIST. In case unequal variance there must be defined, whether the degrees of freedom are made integer by FLOOR or whether they remain real. (2) case paired: <math>\scriptstyle \sqrt {n-1}</math> is wrong because it is already contained in <math> s_{X_1 - X_2}</math>. (3) case equal variance: definition of s² is missing. (4) case unequal variance: definition does not meet usual definitions. In fact it is the definition for the equal variance case, with pooled variance.(5) As TDIST actually returns the values of the CDF, this formulas has to be revised together with TDIST.|| | + | | (1) All formulas miss the degrees of freedom. It is necessary for TDIST. In case unequal variance there must be defined, whether the degrees of freedom are made integer by FLOOR or whether they remain real. (2) case paired: <math>\scriptstyle \sqrt {n-1}</math> is wrong because it is already contained in <math> s_{X_1 - X_2}</math>. (3) case equal variance: definition of s² is missing. (4) case unequal variance: definition does not meet usual definitions. In fact it is the definition for the equal variance case, with pooled variance.(5) As TDIST actually returns the values of the CDF, this formulas has to be revised together with TDIST.|| || || || |

|- | |- | ||

! WEIBULL | ! WEIBULL | ||

− | | Parameter ''Cumulative'' is logical.||Parameter is 0 or ''not 0''|| | + | | Parameter ''Cumulative'' is logical.||Parameter is 0 or ''not 0''|| || || |

|- | |- | ||

− | | ||Alpha and Beta are exchanged in the formula, compared with the current implementation in OOo, Excel and Gnumeric.|| | + | | ||Alpha and Beta are exchanged in the formula, compared with the current implementation in OOo, Excel and Gnumeric.|| || || |

|- | |- | ||

! ZTEST | ! ZTEST | ||

− | | | + | | || Calc calculates z-value wrong. Read discussion in {{Bug|90759}} || Correct sigma-bug|| Patch in {{Bug|90759}}|| |

|- | |- | ||

− | | || Calculates <math>\scriptstyle 1-P( -|z| \le Z \le |z|)</math>|| Beside the sigma-bug OOo calculates <math>\scriptstyle 1-P( Z \le z)</math> for z>0 and <math>\scriptstyle P( Z \le |z|)</math> for z<0|| | + | | || || Uses instable algorithm for standard deviation.||Change to stable version or use STDEV directly.|| || |

+ | |- | ||

+ | | || Calculates <math>\scriptstyle 1-P( -|z| \le Z \le |z|)</math>|| Beside the sigma-bug OOo calculates <math>\scriptstyle 1-P( Z \le z)</math> for z>0 and <math>\scriptstyle P( Z \le |z|)</math> for z<0|| || || | ||

|- | |- |

## Revision as of 12:43, 27 October 2008

## Examine Changes in Functions for ODFv1.2

This is a collection of the differences I found and what I know is already done or in progress. I also note, when I found problems in the draft spec itself. You are invited to add your observations. Regina 19:40, 10 October 2008 (CEST)

### Statistical Functions

Function | ODFv1.2 | CALC | Action | Progress | |
---|---|---|---|---|---|

BETADIST | Draft Spec misses faktor 1/(b-a) in densitiy case | not yet implemented | Inform Eike Rathke | ||

Has density case and optional parameter, to choose between density and cumulative | not yet implemented | expand function to density case and additional parameter | tracked in Issue 91547 , function code is ready, finishing implementation depends on Issue 91602 | ||

BINOMDIST | Parameter C is integer with constraint to 0 and 1. It should be Logical as in other distributions | C=0 is density, all other values of C are treated as cumulative | |||

LEGACY.CHIDIST | Returns 1 for x <0 | x < 0 returns Err:502 | expand domain to x < 0 internal | patch in Issue 94555 | |

CHISQDIST | density function and cumulative left tail of chi-square distribution | not yet implemented | prepare Implementation | patch in Issue 94555 | |

CHISQINV | inverse of CHISQDIST(...TRUE()) | not yet implemented | prepare Implementation | patch in Issue 94555 | |

EXPONDIST | Parameter Cumulative has type Logical and is optional. |
Last Parameter C has type Number and is not optional. C=0 returns denisity, other values for C return cumulative function. | |||

FDIST | density function and cumulative left tail of F-distribution | not yet implemented | prepare for implementation | first draft, depends on BETADIST Issue 91547 | |

Name conflicts with actual FDIST, which calculates right tail. | get new UI-name | have brought problem to dev@sc | |||

FINV | inverse of new FDIST | not yet implemented | |||

Name conflicts with actual FINV, which calculates inverse for right tail. | get new UI-name | ||||

GAMMA | Parameter is constraint to >= 0 | Gamma is definied for all real numbers, but non-positive integers | Inform Eike Rathke | ||

not yet implemented | prepare implementation using full domain. Add UI texts. | path in Issue 94555 | |||

is in subgroup Mathematical functions | will go to subgroup Statistical functions | ||||

GAMMALN | is in subgroup Mathematical Functions | is in subgroup Statistical Functions | |||

GAMMADIST | Last Parameter has type Logical | Last Parameter has type Number | Change type internal to bool. Change UI texts. | patch in Issue 94555 | |

Domain for Parameter x includes x < 0 | x < 0 returns Err:502 in OOo2.* | Change implementation | integrated in OOo3.0 | ||

GROWTH | Last parameter has type Logical (in Excel too) | Last parameter has type Number | |||

is in subgroup Statistical Functions (in Excel too) | is in subgroup Array Functions | ||||

HYPGEOMDIST | Optional, logical parameter to calculate "exact" or "at most" | no parameter, calculates always "exact" | |||

INTERCEPT | Current, "Calculates the point at which a line will intersect the y-values by using known x-values and y-values." (BTW: That is literally from Excel help.) Problems:1. It calculates no point but a single value. 2. The line does not intersect the y-values but the y-axis. 3. It does not say, that the line is the linear regression of the known values. | ||||

NORMDIST | Last Parameter has type Logical and is optional | Last Parameter has type Number and is required | |||

LEGACY.NORMSDIST | "This is exactly NORMDIST(x)." Should be "This is exactly NORMDIST(x;0;1)." because mean parameter and standard deviation parameter are not optional. | ||||

PERCENTRANK | It has an optional parameter Significance |
not yet implemented | |||

PHI | Description is unclear. PHI(x)=NORMDIST(x;0;1;FALSE()). It is the density function of the standard normal distribution | ||||

POISSON | Parameter Cumulative has type Logical and is optional. |
Parameter 'C' is required. 'C'=0 is density function, all others are cumulative | |||

QUARTILE | Example calculates with Quart=1.5 although there is a constraint INT(Quart)=Quart |
- | - | - | |

RANK | Description "If not 0, Data is ranked in descending order." has to be "If not 0, Data is ranked in ascending order." | ||||

SKEWP | not yet implemented | ||||

STDEVA | The text "cells with text are converted to 0;" conflicts with "The handling of strings is implementation defined." | ||||

TDIST | Draft spec shows the density function. | Calc (and Excel) evaluates the cumulative function. | Inform Eike Rathke | ||

TREND | The optional parameters knownX and newX may be empty, denoted as ;; |
knownX and knownY cannot be empty |
|||

Parameter AllowOffset is Logical |
Parameter is 0 or not 0 |
||||

TRIMMEAN | The limits in the definition formula are wrong. Either count i from 0, then sum from i=cutOff to n−1−cutOff, or count i from 1, then sum from i=cutOff+1 to n−cutOff | ||||

TTEST | (1) All formulas miss the degrees of freedom. It is necessary for TDIST. In case unequal variance there must be defined, whether the degrees of freedom are made integer by FLOOR or whether they remain real. (2) case paired: is wrong because it is already contained in . (3) case equal variance: definition of s² is missing. (4) case unequal variance: definition does not meet usual definitions. In fact it is the definition for the equal variance case, with pooled variance.(5) As TDIST actually returns the values of the CDF, this formulas has to be revised together with TDIST. | ||||

WEIBULL | Parameter Cumulative is logical. |
Parameter is 0 or not 0 |
|||

Alpha and Beta are exchanged in the formula, compared with the current implementation in OOo, Excel and Gnumeric. | |||||

ZTEST | Calc calculates z-value wrong. Read discussion in Issue 90759 | Correct sigma-bug | Patch in Issue 90759 | ||

Uses instable algorithm for standard deviation. | Change to stable version or use STDEV directly. | ||||

Calculates | Beside the sigma-bug OOo calculates for z>0 and for z<0 | ||||

Functionname | ODF | Calc | Action | Progress |

### Other Functions

Function | ODFv1.2 | CALC | Action | Progress | |
---|---|---|---|---|---|

GCD | "Return the largest value that can be evenly divided (no remainder) into the given numbers." Should be "Return the largest value that can divide the given numbers evenly (no remainder)." | - | - | - | |

Functionname | ODF | Calc | Action | Progress |