Testname: Functions and formulas

• 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.

• 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]
  

• 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

• 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.
  

• 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)

• 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
  

• 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

• 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

• 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