# Testname: Functions and formulas
Document Owner: Frank Stecher | Last document change: 09.06.2008 | Status of document: Preliminary
Valid for version: OpenOffice.org_2.2, OpenOffice.org_2.2.1, OpenOffice.org_2.3, OpenOffice.org_2.3.1, OpenOffice.org_2.4, OpenOffice.org_3.0

### Test purpose:

Check the functions available for calculations

### Known issues:

### Preconditions of test:

Testcases works best on en_US locales, make sure this is set under tools-options-language settings or in the system locale setting

### Test documents:

http://www.openoffice.org/nonav/issues/showattachment.cgi/49963/Funktionen.ods
http://www.openoffice.org/nonav/issues/showattachment.cgi/49964/percent.sxc
http://www.openoffice.org/nonav/issues/showattachment.cgi/42238/COT.ods
http://www.openoffice.org/nonav/issues/showattachment.cgi/46306/i47724_div0-xp.xls
http://qa.openoffice.org/source/browse/*checkout*/qa/qatesttool/calc/special/input/minimal.sdc?rev=1.1
INDEX-LINEST function.xls
i71158_GCD_LCM.ods

### Testcases:

Basic function test | Status: Standard | -
Download and open the testdocument Funktionen.ods Press CTRL+SHIFT+F9 to perform a forced re-calculation of the Sheet Check column D for other Values as Zero (0). A red cell background makes such occurences more visible. If the value shown in such red cells is zero, a rounding error causes this coloring and the difference has to be evaluated. Check this with an Excel and decimal places set to 16. The following cells must show differences : D49 as the Error types from Excel and OOo Calc does not match D73 as the reference value could not be stored with the document and therefore TODAY() is much later than this referenced value. D87 same as for D73 but with the function NOW() D218 as a random number is random and can't be seriously compared with a fixed value in this case D233 shows zero and green but obviously the values in B233 and C233 does not match. This is correct as the output for the function and the compared cell are text and text values are in fact zero values. Zero minus Zero is Zero so the comparison is correct. The function INFO itself gives a different output on OOo Calc and Excel as Calc is also available for other Operating Systems.
Edit Formula | Status: Standard | -
File - New - Spreadsheet Type in a cell the value =rand() After typing the last bracket the brackets their formatting has to be changed to bold =rand() Press [F9] A small bubble help containing the function result will be visible Place the result into the cell with [ENTER]
Matrix Limits | Status: Standard | -
File - New - Spreadsheet Click on the Name Box located on the formula toolbar (It's to the left of the f(x) symbol at the beginning of the toolbar) It should show A1, if not delete the content in this box and type A1 Type ':IV2049' (without the quotes) and press enter Click again on the name box and erase the characters A1:IV2049 Type test and press Enter Type '1' (without the quote) and press ALT+SHIFT+ENTER After as moment the range A1:IV2049 is filled with 1 in each cell Go to A2051 and type =test . Press CTRL+SHIFT+ENTER to create an Array formula A2051 must show Err:514 Press CTRL+F3 Click on test in the range name dialog Change the number 2049 on the Assigned to : Line to 2048 Click on Modify Click on Ok Select A2051 again and type =test Press CTRL+SHIFT+ENTER to create an Array formula Wait a moment. The range A2051 :IV4098 must show up as a copy of A1:IV2048. If not file an Issue. File - Save as Give it a name of your choice and select OpenDocument Spreadsheet .ODS as fileformat in the filter line. Click on Save. File - Save as Give it a name of your choice and select StarCalc 5.0 (.sdc) as fileformat in the filter line. Click on Save. Confirm the Warning about possibly lost formats with Yes If you have the chance to use Excel, do the same for the Excel filter. Press CTRL+F3 Click on test in the range name dialog Change the number 2048 on the Assigned to : Line to 2049 Click on Modify Click on Ok A2051:IV4098 must show Err:514. If not, file an Issue Close the document by pressing CTRL+W File - Open Select the previously saved ODS document and open it. Press CTRL+SHIFT+F9 to perform a recalculation of the Sheet The range A2051:IV4098 must show a single 1 in each cell, if not, file an Issue. Close the document by pressing CTRL+W File - Open Select the previously saved SDC document and open it. Press CTRL+SHIFT+F9 to perform a recalculation of the Sheet The range A2051:IV4098 must show a single 1 in each cell, if not, file an Issue. If possible load the previously saved XLS file with Excel and check that the result is 1 for all cells within A2051:IV4098
Percent Sign | Status: Standard | -
Download and open the test document percent.sxc Press CTRL+SHIFT+F9 to force a recalculation Have a look at column D. All colored backgrounds must be Green and the values must be shown as zero. If not file an Issue.
Excel Export for COT, ACOT, COTH, and ACOTH | Status: Standard | -
- File / Open / testdocument COT.ods (it's an OpenDocument Format; ODF)
      the spreadsheet contains
-     COT(x)
-     ACOT(x)
-     COTH(x)
-     ACOTH(x)
- File / Save As / Name_of-your-choice.xls and select the Excel97 filter File / Close File / Load / Name_of-your-choice.xls (the exported Excel97 file) now the spreadsheet should have replaced the functions as follows:
      COT(x) in the COT.ods should be now 1/TAN(x)
-     ACOT(x) in the COT.ods should be now PI/2-ATAN(x)
-     COTH(x) in the COT.ods should be now 1/TANH(x)
-     ACOTH(x) in the COT.ods should be now ATANH(1/x)
#DIV/0! Error code implemented for Excel files | Status: Standard | -
File / Load testdocument i47724_div0-xp.xls Press [CTRL+SHIFT+F9] Check that the functions in B2:B14 shows #DIV/0! Check that the functions in B20, B22 and B23 shows #DIV/0! File / Save As / name_of_your_choice.xls (Excel97 filter) File / Close File / Load / name_of_your_choice.xls (the exported Excel97 file) Check that the functionresults show the #DIV/0! errorcode
#DIV/0! Error code implemented for StarOffice binary files | Status: Standard | -
Download and Open the test document minimal.sdc (see test documents) Press [CTRL+SHIFT+F9] Validate that the value in cell A1 is 1,2 (or 1.2 - depending on the delimitter) Validate that the value in cell A2 is #DIV/0! (devision with zero) Validate that the value in cell A3 is 3,14 (or 3.14 - depending on the delimitter) Close the document This testcase is also implemented in an autotest
INDEX() function supports return of an entire vector of a 2D array | Status: Standard | -
Open testdocument "INDEX-LINEST function.xls" Verify that the values in row 8 are the same as in row 13 Save the document to the current fileformat (e.g. *.ods) Close and reload the document Verify that the values in row 8 are the same as in row 13 Save the document to *.xls Close and reload the document Verify that the values in row 8 are the same as in row 13 Close the document
GCD() and LCM() results for non-integers | Status: Preliminary | -
Open testdocument "INDEX-LINEST function.xls" Verify that the values in columns F and J are "TRUE" Save the document to the current fileformat (e.g. *.ods) Close and reload the document Verify that the values in columns F and J are "TRUE" Save the document to *.xls Close and reload the document Verify that the values in columns F and J are "TRUE" Close the document

### References:

-

### Acronyms:

-

## testcase specification history

Functions and formulas
Date | Change | User
--- | --- | ---
09.06.2008 (11:43:46) | testcase details added | Oliver Craemer
09.06.2008 (11:42:23) | testcase added | Oliver Craemer
09.06.2008 (10:51:58) | status updated | Oliver Craemer
09.06.2008 (10:51:50) | testcase details added | Oliver Craemer
09.06.2008 (10:46:41) | testcase added | Oliver Craemer
22.02.2008 (09:52:55) | testcase deleted | Oliver Craemer
22.02.2008 (09:40:52) | testcase added | Oliver Craemer
28.11.2007 (12:23:45) | status updated | Frank Stecher
28.11.2007 (12:23:30) | status updated | Frank Stecher
28.11.2007 (12:23:24) | status updated | Frank Stecher
28.11.2007 (12:23:15) | status updated | Frank Stecher
28.11.2007 (12:23:08) | status updated | Frank Stecher
28.11.2007 (12:22:27) | testcase :Percent Sign updated | Frank Stecher
28.11.2007 (12:22:00) | testcase :Basic function test updated | Frank Stecher
28.11.2007 (12:19:05) | testcase details updated | Frank Stecher
28.11.2007 (12:18:12) | testcase :#DIV/0! Error code implemented for Excel files updated | Frank Stecher
28.11.2007 (12:17:05) | testcase :Excel Export for COT, ACOT, COTH, and ACOTH updated | Frank Stecher
28.11.2007 (12:08:10) | testcase details updated | Frank Stecher
28.11.2007 (10:54:43) | testcase details updated | Frank Stecher
27.11.2007 (16:41:19) | testcase details updated | Frank Stecher
27.11.2007 (16:37:37) | testcase details updated | Frank Stecher
27.11.2007 (16:25:34) | testcase deleted | Frank Stecher
27.11.2007 (16:19:24) | header updated | Frank Stecher
27.11.2007 (16:14:13) | testcase details updated | Frank Stecher
27.11.2007 (16:11:08) | testcase details updated | Frank Stecher
10.10.2007 (12:15:36) | testcase details updated | Jogi Sievers
10.10.2007 (12:13:13) | testcase details updated | Jogi Sievers
10.10.2007 (12:04:12) | testcase details updated | Jogi Sievers
10.10.2007 (12:03:40) | testcase details updated | Jogi Sievers
10.10.2007 (11:52:54) | testcase :#DIV/0! Error code implemented for StarOffice binary files updated | Jogi Sievers
10.10.2007 (11:49:43) | header updated | Jogi Sievers
10.10.2007 (11:34:44) | testcase details updated | Jogi Sievers
10.10.2007 (11:32:22) | testcase details updated | Jogi Sievers
10.10.2007 (11:26:33) | testcase details updated | Jogi Sievers
10.10.2007 (11:23:22) | testcase :#DIV/0! Error code implemented for StarOffice binary files updated | Jogi Sievers
10.10.2007 (11:22:57) | testcase :#DIV/0! Error code implemented for Excel files updated | Jogi Sievers
10.10.2007 (11:21:04) | testcase details added | Jogi Sievers
10.10.2007 (11:19:17) | testcase added | Jogi Sievers
03.07.2007 (13:19:06) | testcase details added | Oliver Craemer
03.07.2007 (13:11:09) | testcase added | Oliver Craemer
13.04.2007 (15:41:07) | version updated | Frank Stecher
13.04.2007 (15:40:44) | status updated | Frank Stecher
17.01.2007 (16:26:28) | status updated | Frank Stecher
17.01.2007 (16:26:16) | status updated | Frank Stecher
17.01.2007 (16:26:03) | status updated | Frank Stecher
17.01.2007 (16:25:52) | status updated | Frank Stecher
17.01.2007 (16:25:46) | status updated | Frank Stecher
17.01.2007 | Testcase specification created | Frank Stecher