Using Formulas to Calculate Results
Overview
A scheme version analyte has a spreadsheet formula so that its result is determined from one or more dependencies, and not entered as a numeric result. Refer to Maintaining Scheme Version Analytes to configure an analyte to have a spreadsheet formula.
A spreadsheet formula can reference:
- The scheme or analyte of the same or another analyte in the sample. The code reference must be in upper case.
- The property or parameter of the same or another analyte in the sample. The reference must be in camel case, for example dilutionFactor, weight, volume, and compositeCorrectionFactor.
- The value of another sample for the same scheme and analyte. The reference must exactly match, including on case, the sample name.
A spreadsheet formula can also use functions (including script functions) and logical operators. Script functions are contained within scripts that are defined using the CCSCRT—Script application.
Process
Regardless whether the results are manually entered, pasted from a copy, or captured interactively or non-interactively, where the dependency's value is updated, then the related sample scheme analyte's result is re-calculated.
Formulas are configured on scheme version analytes using the CCSCHM—Scheme application, which is accessed using the Analysis Setup » Schemes menu option. Refer to Maintaining Scheme Version Analytes. A sample scheme analyte's Spreadsheet Formula defaults to the scheme version analyte's Spreadsheet Formula and may be updated.
Refer to Appendix—Workbook Formulas and Function.
Existence of a Formula on a Workbook Cell
If a sample scheme analyte's Spreadsheet Formula is defined, when a workbook cell is loaded with the sample scheme analyte, then the cell displays with a light blue background.
#N/A is displayed in any workbook cell if it contains a formula that cannot be resolved. The calculated result is displayed in any workbook cell if it contains a formula that can be resolved.
Updating Formulas on Cells
Copy/cut/paste actions can be used for entering and clearing formulas.
When focus is placed on the workbook cell, then the formula displays in the Workbook Cell Content Bar.
Note: Where a sample scheme analyte's Spreadsheet Formula is a multi-line formula, as it is loaded into the workbook cell, any CRLF, CR and LF characters are removed from it so that, if required, it can be copied from the workbook cell and pasted into another cell without any truncation of formula content.
Enter or clear formulas in workbook cells
Formulas are updatable in workbook cells only where the workbook is opened in final results mode, that is, formulas cannot be entered into cells when the workbook is opened in dilution, weight/volume or raw result mode.
If a non-formula numeric value is entered into the formula bar, and the decimal point key on the number pad is used to enter the number, then the non-formula number that is displayed in the formula bar and in the cell respects the browser's locale. For example, if the locale is FR_FR and "5.5" is entered, then "5,5" is displayed instead of "5.5".
If a formula value containing a number is entered into the formula bar, and the decimal point key on the number pad is used to enter the number, then the number in the formula displayed in the formula that is displayed in the formula bar and in the cell retains the "." (dot). For example, if the locale is FR_FR and "= 1.2 + 3.5" is entered, then the formula still contains dots for decimal points, however, the calculated value is displayed as "4,7" instead of "4.7".
Evaluation of Formulas
When a dependency in a sample scheme analyte's Spreadsheet Formula has its value updated, and the sample scheme analyte's Workflow Status is something other than Listed Not Received, then the formula is applied to produce the analytical result, which can impact QC assessment and workflow outcomes.
The dependent and dependency sample scheme analyte do not have to be displayed in the same workbook for formulas to work. When a workbook is opened using the CCWKOP—Open Workbook application, and values are entered, if that value is a dependency of another sample scheme analyte's Spreadsheet Formula, then an attempt is made by the system to resolve the formula. This is done immediately after the result is entered if the dependent sample scheme analyte is loaded in the same workbook session, or when the result is persisted if the dependent sample scheme analyte is not loaded in the same workbook session.
When a formula contains a reference to the code, property or parameter of an analyte that has an Analytical Type of Prep Other, then the formula is not resolved immediately, but is resolved on submit.
For a workbook session that is opened with multiple schemes where one scheme has a Scheme Type of Analytical Prep and another scheme has a Scheme Type of Analytical, then those cells that related to an analyte that has a Analytical Type of Prep Other has its formula calculated only on submit, and those cells that relate to an analyte that has an Analytical Type of Analytical calculate immediately.
- Conditions for Auto-calculation of Cell Formulas on Workbook Load
- Conditions for Executing Workbook Scripts on Result Entry
Formula Errors
The application of a formula can produce an error. Your system may be configured to display or hide formula error messages in the Formula Error Bar.
The following table indicates the situations in which a particular formula error message may be returned.
|
Outcome of a formula execution |
Cell display |
|---|---|
|
Division by zero (for example, caused by a reference to a blank cell or a cell containing a zero value) |
#DIV/0! |
|
No value is available (for example, caused by inappropriate values in the formula or a reference to a cell containing a #N/A value) |
#N/A |
|
No name is available, that is, a name is not recognised (for example, caused by a user-defined name that is not defined) |
#NAME? |
|
Number problem (for example, caused by inappropriate numbers in functions, an iteration that cannot resolve for a value, or a value or formula result that is too large to be stored) |
#NUM! |
|
Incorrect argument type (for example, caused by entering text where a number was expected, or supplying a range to an operator or function that was expecting a single value.) |
#VALUE! |
|
Script execution error, when a cell formula containing a Script function is being resolved, and an error arises when calculating a result for the function (for example, the function is found, arguments are valid yet the function cannot be executed, return value type is not as expected) |
#N/A |
Note that for any of these errors, the formula is still visible in the formula bar.
When focus is placed on the workbook cell that contains the formula, then any error message displays below the bottom-right corner of the workbook grid.
Using Analytes in Aggregate Formula Functions
The Include in Aggregate Functions and Exclude from Aggregate Functions native workbook commands (accessed via a workbook menu) are used to include and exclude, respectively, analytes in cross-sample aggregate formula functions, that is, CAVERAGE, CMEDIAN, CRSD, CSTDEV, CRSDP, and CSTDEVP.
Include/Exclude an Analyte for use in Aggregate Formula Functions
Note: Aggregate functions do not recalculate automatically upon this flag being changed, nor after submit. The specific aggregate formula functions need to be manually triggered to recalculate.
Where a sample scheme analyte has the isUsedInAggregate attribute set to false or contains a space, when the sample scheme analyte is displayed in a workbook cell, then the cell has a small black triangle displayed in the bottom-right corner of the cell to show that the result are not used in aggregate function calculations.
Related processes...