> For the complete documentation index, see [llms.txt](https://docs.geodin.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.geodin.com/data-analysis/formulas-in-measurement-values.md). # Formulas in Measurement Values This page covers how GeoDin organizes measurement values, how the **Calculation** function recalculates a measurement table using predefined or single formulas, the full formula and condition syntax with its special-case constructions, and the import/export, diagram, and table-format options of the measurement editor. ## Measurement values GeoDin organizes objects spatially. These are point objects with or without a depth value. At these objects measurements can be made. In order to use GeoDin to collect such data, measurement points need to be defined in the general data. Usually filters and sample intervals are used as measurement points. Also the object itself can be defined as a measurement point. In the GeoDin object manager measurement points are shown by three blue spheres. GeoDin Demo Project Object All objects General borehole log Measurement point filters upper piezometer: (4.3-6.3m) lower piezometer (7.8-8.7m) samples BH01: (1-7m) BH01: (4-5m) ### Terminology The following hierarchy is used in the measurement point organization to relate a single measured value to a measurement object of the measurement point. There are the following different types: **Measurement point type** The measurement point type defines, what type of object it is. These can be either with or without a vertical component. An example of a measurement point with a vertical component is a borehole. A borehole can be the measurement point itself (e.g. where the whole length is sampled) or other measurement point types can be associated with it (discrete samples at various intervals over the length of the borehole). Examples of point samples (i.e. without a vertical component) are surface water or climate measuring stations. **Data type** Chemical investigations can be done for several objects for each type of measurement point. For these combinations data types are defined. For example at a groundwater well the water quality can be investigated or flow rates measured. For each case there is a data type. Each data type can be assigned to several measurement point types. So the data type "groundwater composition" can be entered for a groundwater measurement point as well as for a well. The results are combined in a data type table, although the data for each measurement point are distinct from one another. **Chemical group** Because the number of individual parameters within a data type can reach large amounts, the parameters are subdivided into chemical groups to allow a better overview. Each group is distinguished by a similarity in the chemical parameters or descriptive characteristics and may have up to 20 parameters. **Parameter** A parameter is an individual measurement described by a name, a field identification and a unit **Query** Queries are used within projects or databases to interrogate data. They define the amount and type of data from which the results are derived. ### Special values GeoDin organizes measurement values as numerical entries. Hence values below a detection limit cannot be saved as the character „<". In such cases a negative detection limit is entered (e.g. "-1"). These values are ignored by statistical analyses. If the detection limit is unknown (e.g. old data) the value"-88" is used. If the value is not detectable then"-99" should be entered: | Entry | Description | | ----- | ------------------------------------------------------- | | -XX | beneath detection limit (XX = detection limit) | | -88 | beneath detection limit (detection limit value unknown) | | -99 | not detectable | ## Formula As an alternative to presenting the measurement values in grid form you may view the current data set in a mask. At the top of the mask the general sample data (Name, Date, Time) and the group are displayed. Below the individual parameters for the current data set are listed in rows. For each parameter the name, measurement value, unit, detection limit and investigation method are shown. Name and unit are not editable.\ \ The contents of a data set can be saved as a simple text file (which can be subsequently loaded). By pressing the **OK** button the mask contents are saved to the data set - by pressing **Cancel** the contents are discarded. Optionally the short field name can be used for the parameter column. ## Calculation This function allows you to recalculate values for entire table in the measurement editor. You have two options: **-Available formulae-** 1. Execute one or more formulae from the predefined [Formulas](/data-analysis/formula-basics.md)of the data type, found in the system configuration. 2. If you check the box \[only activate formulae] you will only see formulae which are set to active in the [General formulas](/data-analysis/formula-basics.md) in the system configuration. Note: These formulae will always be executed for the selected data records when using the Calculate function. This is because an update of a data record triggers the calculation of active formulas. 3. Formulae are marked with a red symbol instead of a black one if the target field of the formulas is always meant to be overwritten. 4. Formulae can be sorted by clicking on the actual column title, e. g. name or target field. ***Note:*** *the execution order of the selected formulae will be the same as it has been set in the data type settings of the system configuration!* \*\*This is important, especially for interdependent formulae. **-Execute single formulas-** 1. To execute a single formula you may use and change one of the predefined formulas from the [General formulas](/data-analysis/formula-basics.md) of the system configuration or you define a new formula. 2. To accept a formula just mark it by clicking on the name (you do not have to check the box for this action) and click the button **Accept available marked forumla**. The fields *"Target field:", "Condition:"* and *"Formula:"* are automatically filled and can be edited. 3. Alternatively you can edit these fields without using a predefined formula but creating a new formula, which is applied to the target field. *\[\[Overwrite target]]{.underline}* For the following calculation you can set the configuration to overwrite existing values. By default the calculation won't overwrite existing fields but calculate results for those fields without values for the corresponding target field. *\[\[All parameters have values]]{.underline}* Furthermore you can specify only to execute the calculation if all parameters, which are used in the formula, contain values for the calculation. Therefore the calculation won't be executed for empty data fields (if they are used in the formula). ***-All visible data records-*** Choose here whether the calculation is done for all data records listed in the measurement data editor. ***-All selected data records-*** Choose this option to execute the calculation only for the data records (rows) selected in the measurement data editor. *** ## Reference: Definition of formulas A formula is defined as a string of characters (similar to a text macro definition) and contains mathematical operators for calculating a result. For example: $DAT.PAR1$ \* 100 The characters inside the $-signs relate to a GeoDin data field. The following operators can be used: (x) stands for the table column of GeoDin (e.g. $DAT:PAR1$) Empty spaces can be contained in the formulas. Fixed number values (100 in the example above), can be entered directly in the formula. ### Use of conditions ***Note:*** *The formula can be entered directly in the measurement editor after clicking the button* *or on the system tab under Data types-> Data type settings->Formulas* ([General formulas](/data-analysis/formula-basics.md)). **Example for simple conditionExample:** *Destination:* WAS:NA\_CALC *Condition:* $WAS:MG$>3 *Formula:* $WAS:NA$/2 The target parameter NA\_CALC is calculated if the parameter MG has a value of 3 or higher. **Example for multiple conditionExample:** *Destination:* WAS:NA\_CALC *Condition:* $WAS:MG$>3 AND $WAS:CA$<10 *Formula:* $WAS:NA$/2 The target parameter is calculated, if both the parameter MG has a value greater than three and the parameter CA has a value of less than 10. **Conditions for changing values** By using the formatting @O the original value of a data record BEFORE the last change can be recreated in the measurement editor. Hence checking for differences is possible. **Example:** *Condition:* $WAS:PAR1$ - $WAS:PAR1\@O$ >10 The condition is true, when the value of PAR1 in the cell is more than ten times the previously entered value. **Further condition examples** In a condition, the NULL operator can be used. It defines whether a parameter has a value. **Example:** *Destination:* WAS:NA\_CALC *Condition:* $WAS:MG$>3 AND $WAS:CA$=NULL *Formula:* $WAS:NA$/2 The target parameter NA\_CALC is calculated by taking half the value of the parameter NA, if the value of the parameter MG exceeds 3 and the parameter CA is empty. If strings are used in a condition, the text has to be included in inverted (or high) commas. Missing inverted commas and mis-spelling are interpreted as non-equal. The spelling of the condition is case sensitive. **Example:** *Destination:* WAS:NA\_CALC *Condition:* $BEARBEIT$='Müller' *Formula:* $WAS:NA$/2 The target parameter NA\_CALC is calculated as half of the parameter NA, if the author of the data record has the name Müller. ### Using special rules Additionally to the mathematical operators special syntax constructions can be used for the usage of values from the GeoDin tables to take into consideration numerous special cases. **Special cases in formula syntaxDetection Limits** Detection limits present a special case. These are by definition negative values (e.g. -1 for <1). If these values are used without care, false results may be produced, for example when building sums from individual parameters. To do this, a construction in the form of @B(x) within the $-signs must be used, where x is a factor with which the detection limit enters the calculation. For example a detection limit of 5 mg (entered as -5) using the factor 0.5 produces the result 2.5. **Example:** $WAS:BENZEN\@B(0,5)$+$WAS:TOLUEN\@B(0,5)$+$WAS:XYLEN\@B(0,5)$ In the case above, where values for individual parameters of -5 or -1 are found, the sum calculation uses half of these values. **Default values** For certain calculations it may be necessary to work with predefined settings or defaults. When a parameter is either not present or has not been analyzed in a data set, a standard value can be assumed and used for calculation. This is realized by using the construct @D(x) inside the $ signs, whereby x is the predefined default value used when no value is present in the field. **Example:** VALUE=$ORGANIC\@D(10)$/$CLAY\@D(25) The calculated value has the quotients from the organic substances and clay in a soil sample. If no values are present in these fields the default values are used. **Mean Value** A mean value is calculated by using the symbols "@M" inside the dollar symbols of a formula. The individual values are separated by ";" and only filled fields can be used. **Example:** UWDRYMIN=$UWDRYMIN1;UWDRYMIN2;UWDRYMIN3\@M$ The result is an average of UWDRYMIN1 to UWDRYMIN3. **Using a number from a dictionary** If a dictionary is used, which produces a number when entering a code, this can be used for a calculation. By using the "@R" sign a recode is carried out. **Example**: CU=($CONE\@R$)/SQRT($PEN1;PEN2;PEN3;PEN4;PEN5\@M$) First of all the entry for CONE is replaced by the value from the dictionary. For the values P1 to P5 an average is taken from which the square root is calculated. The value for CONE is then divided by this value. **Using values from another data type** Values from one data type can be used in calculating values in other data types. To do this, the code of a data type is followed by a colon. The relationship to a data record in another data type is defined by time. To compare values the date is used in a number of different ways: | Operator | Meaning | | ---------------------------- | ------------------------------------- | | \[=SMPDATE] or no definition | The date must be the same | | \[<=SMPDATE] | The date can be the same or less than | | \[\=SMPDATE] | The date can be the same or more than | | \[\$ ***Attention:*** *Only parameters of the same table (data type) or object type parameters can be evaluated. The parameter of the current table must be specified here without the table abbreviation. See example.* ## Reference: Import/Export In the GeoDin object manager at the level of a measurement point or a group of measurements the methods **"Export measurement values"** and ![import measurement values](/files/HwXA4uuDjHh6Owf9L6Jj) **"Import measurement values"** can be selected. By starting this method a dialogue appears where all import or export settings can be made: [Import](/importing-data/import.md) [Export](/exporting-data/export.md) ### Diagrams and analysis If the checkbox *\[diagrams and analysis]* is activated, additional information for the current data pool is shown below the data entry grid. **Column chart** The values of the current column are graphically represented in the order that the data sets are displayed in the data entry grid. To represent a particular parameter in the chart (to fill the chart) one data set of the appropriate column has to be selected (e.g. a data record of the column CHLORIDE). The current data record will be displayed as a filled rectangle in the chart. With a click on the rectangle (filled or not filled) it is possible to navigate to the data record in the data entry grid. **Row chart** Graphical representation of the values of the current row (data record) in the order that the columns are displayed in the data entry grid. To navigate to the column in the data entry grid click on the bar of the desired parameter in the row chart. **Plausibility control** This tab displays the plausibilities analysis of the currently checked row. A [Plausibility](/data-analysis/data-checks-and-validations.md) can be defined within the **Properties**. **Formulae** The last executed formulae will be displayed when a new data set is recorded. The [General formulas](/data-analysis/formula-basics.md) can be defined directly within the method **"Measurement data"** or within the **Properties**. **List comparison** The chosen list comparison of the current data record will be carried out and the result displayed. The **List group** can be created und managed within the **Properties**. **Ionic balance** The ionic balance will be displayed for the current data record. The [Ion balance](/data-analysis/geotechnical-analyses.md) is calculated and evaluated based on the DVWK 1992 recommendations. ### Format options GeoDin supports two general arrangements of tabular data to be imported. The format **-Table by row-** describes a table, which contains each sample in one row, the values of the parameters are stored in separate columns for each parameter. ``` NAME DATE NA MG NH3 ... Sample 1 12.07.2012 2,4 4,5 1,23 ... ... ... ... ... ... ... ``` The format **-Table by column-** describes a table, in which one measurement of one parameter builds one row. A sample can consist of a certain number of rows (as many as measured parameters) in this format. This format also allows additional information for each measured parameter to be imported and organised in GeoDin too. ``` SAMPLE DATE PARAM VALUE COMMENT Sample 1 12.07.2012 NA 2,4 verified Sample 1 12.07.2012 MG 4,5 unverified Sample 1 12.08.2012 NA 9,5 implausible ... ... ... ... ... ``` To import data from this type of table you have to first make further adjustments. At first choose the columns, which group the data records of one sample. Therefore tick the appropriate columns in the list of **"Grouping data fields"**; in the example above tick the columns SAMPLE and DATE. The result of this choice would be that the first two rows (Sample 1 from 12.07.2012) would generate one cumulative import row and the third row (Sample 2 from 12.08.2012) another. Choose from the drop-down list "**Data field with parameter name:"** the column, which contains the parameter name or id; PARAM in the example above. From the drop-down list "**Data field with measurement value:"** please choose the column, which contains the value of the parameter; in the example above VALUE. GeoDin now will transform the import table to the table format -table by rows- (please see above). The preview of the import data of our example now will be displayed as follows: ``` SAMPLE DATE NA MG ... Sample 1 12.07.2012 2,4 4,5 ... Sample 1 12.08.2012 9,5 ... ... ... ... ... ... ... ``` Please note that the information from the column COMMENT isn't lost. The import preview the cells of the measurement values are tagged at the right top corner with a red triangle. To display the additional information for the measurement value, please hold the mouse pointer over this corner. For the cell NA=2,4 the information 'COMMENT:verified' would be shown. This information can be imported using the [Additional measurement information](/workspace-and-data-management/working-with-measurement-data.md). There is also a special pre-formatting for text files in the Octoware format available. There are no further adjustments necessary for this format. Example fragment of an Octoware file: ``` OCT>12072240RE0003\10.08.2000 09:10\\\\\\\\\T2000-07949\\\\\\\1\1 EST>FI1 PPA>pH 0\\\\\7.24 PPA>LF 0\\\\\998 PPA>Temp 0\\\\\11.1 ``` ## Related topics * Shared measurement/data-type reference content now lives in the measurement-data editor reference ([Working with Measurement Data](/workspace-and-data-management/working-with-measurement-data.md)) --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.geodin.com/data-analysis/formulas-in-measurement-values.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.