[HyperFormula] Acceptance testing of build-in formulas

[aninde]

A checklist for the acceptance testing/ conformance of build-in formulas.

Release date: Jan 2020

Chore checklist

• 1. Gathering requirements
  #13 Built-in functions required for the initial release
  #54 First Release (January 2020)
  Appendix 5
  Excel https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
  Google Spreadsheet
  Libra Office
  Gnumeric -default rounding numbers

• 2. Listing test cases for formulas
  List is avaliable in Google spreadsheet https://docs.google.com/spreadsheets/d/1jMvfY5DzMxmTiUTZB-8wwtMaKB2OqWEWqi-Ku1FogZE/edit#gid=1120069524

  List #13 Built-in functions required for the initial release

  Cheat sheet

• 3. Development of test cases on the branch https://github.com/handsontable/hyperformula/tree/conformance

Testing checklist
EDIT: Choose and do precisely 3

• All required 77 functions are supported
  • Does all parameters are supported
  • Does it work without optional parameters
  • Can parameters be a reference to another cell
  • Are the correct errors returned when something went wrong
  • How does it behave when parameters are out of bounds (eg. -1 when it should be >0)
  • passing an array of data we can give:
    • string,
    • number,
    • undefined,
    • null
    • NaN,
    • Infinity,
    • -Infinity,
    • -0, which the engine should not support. He shouldn't have crashed either
• how zero suppression is handled In JS we can write a number as .3, but in hyperformula it is a string, not always number
• adding and subtracting small numbers from large ones, precision
• the maximum and minimum number served. If the type is number then there will be a JS limit, but there is no such limit in the string.
• getValues with volatile functions https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation#volatile-and-non-volatile-functions
• getAllSheetsValues with volatile functions

1 stage

Based on small evaluator group
https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__711846_826425813

• ABS 6.16.2
• ACOS 6.16.3
• AND 6.15.2
• ASIN 6.16.7
• ATAN 6.16.9
• ATAN2 6.16.10
• AVERAGE 6.18.3
• AVERAGEIF 6.18.5
• CHOOSE 6.14.3
• COLUMNS 6.13.5
• COS 6.16.19
• COUNT 6.13.6
• COUNTA 6.13.7
• COUNTBLANK 6.13.8
• COUNTIF 6.13.9
• DATE 6.10.2
• DAVERAGE 6.9.2
• DAY 6.10.5
• DCOUNT 6.9.3
• DCOUNTA 6.9.4
• DDB 6.12.14
• DEGREES 6.16.25
• DGET 6.9.5
• DMAX 6.9.6
• DMIN 6.9.7
• DPRODUCT 6.9.8
• DSTDEV 6.9.9
• DSTDEVP 6.9.10
• DSUM 6.9.11
• DVAR 6.9.12
• DVARP 6.9.13
• EVEN 6.16.30
• EXACT 6.20.8
• EXP 6.16.31
• FACT 6.16.32
• FALSE 6.15.3
• FIND 6.20.9
• FV 6.12.20
• HLOOKUP 6.14.5
• HOUR 6.10.10
• IF 6.15.4
• INDEX 6.14.6
• INT 6.17.2
• IRR 6.12.24
• ISBLANK 6.13.14
• ISERR 6.13.15
• ISERROR 6.13.16
• ISLOGICAL 6.13.19
• ISNA 6.13.20
• ISNONTEXT 6.13.21
• ISNUMBER 6.13.22
• ISTEXT 6.13.25
• LEFT 6.20.12
• LEN 6.20.13
• LN 6.16.39
• LOG 6.16.40
• LOG10 6.16.41
• LOWER 6.20.14
• MATCH 6.14.9
• MAX 6.18.45
• MID 6.20.15
• MIN 6.18.48
• MINUTE 6.10.12
• MOD 6.16.42
• MONTH 6.10.13
• N 6.13.26
• NA 6.13.27
• NOT 6.15.7
• NOW 6.10.15
  • [ ]getValues
  • [ ]getAllSheetsValues
• NPER 6.12.29
• NPV 6.12.30
• ODD 6.16.44
• OR 6.15.8
• PI 6.16.45
• PMT 6.12.36
• POWER 6.16.46
• PRODUCT 6.16.47
• PROPER 6.20.16
• PV 6.12.41
• RADIANS 6.16.49
• RATE 6.12.42
• REPLACE 6.20.17
• REPT 6.20.18
• RIGHT 6.20.19
• ROUND 6.17.5
• ROWS 6.13.30
• SECOND 6.10.16
• SIN 6.16.55
• SLN 6.12.45
• SQRT 6.16.58
• STDEV 6.18.72
• STDEVP 6.18.74
• SUBSTITUTE 6.20.21
• SUM 6.16.61
• SUMIF 6.16.62
  • [ ]getValues
  • [ ]getAllSheetsValues
• SYD 6.12.46
• T 6.20.22
• TAN 6.16.69
• TIME 6.10.17
• TODAY 6.10.19
  • getValues
  • getAllSheetsValues
• TRIM 6.20.24
• TRUE 6.15.9
• TRUNC 6.17.8
• UPPER 6.20.27
• VALUE 6.13.34
• VAR 6.18.82
• VARP 6.18.84
• VLOOKUP 6.14.12
• WEEKDAY 6.10.20
• YEAR 6.10.23

Volatile Functions:

1. NOW
2. TODAY
3. RANDBETWEEN
4. OFFSET
5. INDIRECT
6. INFO (depending on its arguments)
7. CELL (depending on its arguments)
8. SUMIF (depending on its arguments)

[aninde]

IF(A2=B2, "Yes", "No")
IF(A2<>B2, "No", "Yes")

[wojciechczerniak]

Useful resources. Each workbook has worksheets with multiple tests.

All operators and types combinations:
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/operator.xls

Bitwise:
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/bitwise.xls

=ADDRESS() function:
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/address.xls

=DATE() and other date functions:
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/datefuns.xls

Other functions:
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/dbfuns.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/engfuns.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/finfuns.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/functions.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/logfuns.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/lookfuns.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/lookfuns2.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/statfuns.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/mathfuns.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/textfuns.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/infofuns.xls
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/trig.xls

Parsing text to value:
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/complex-parsing.xls

[aninde]

Simple tests were developed on the basis of the small group evaluator
https://www.oasis-open.org/committees/download.php/16826/openformula-spec-20060221.html

https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__711846_826425813

and listed in https://github.com/handsontable/hyperformula/blob/conformance/test/conformance/small-group-evaluator.spec.ts

[aninde]

Work on HyperFormula has been postponed, for now, so I am closing #1 for later reopening.