LIMS.eu

In this example, it is assumed that plates with 1 - 300 CFU each are evaluated. One plate is available per dilution step. The dilution series consists of 3 dilution steps of 1:10 dilutions each (V1, V2, V3).

An exclusion criterion for calculating the Result (mean value of CFU) is that the entered CFU count of the next higher dilution step is lower than that of the previous one. This applies if each evaluated plate has at least one colony.
In mathematical notation, the requirement is as follows:

V0 >= 1``V1 >= 1``V0 < V1 = Result
280 >= 1``400 >= 1``280 < 400``= 'invalid DS'
280 >= 1``40 >= 1``280 < 40``= 345

If no colonies were counted and the value 0 was entered, then the Result will be the minimum CFU count corresponding to the respective dilution step, preceded by <.
For example, the value 0 applies to V1:
V1 = 0, Result = < 10

If the upper limit was exceeded and a value greater than 299 was determined, the Result will be the CFU count corresponding to the respective dilution step, preceded by >.
A value greater than 299 was determined, for example, for V1:
V1 = 300, Result = > 3000

If the exclusion criteria do not apply, it is checked which dilution step contains at least one CFU and whether the next higher dilution steps contain at least 10 CFU.
The first dilution step in the dilution series with a value greater than 0 is determined. Here, for example, V0.
V0 > 0, V0 = 280

All subsequent dilution steps are checked to see whether a value greater than or equal to 10 has been entered.
V1 >= 10``V2 >= 10``V3 >= 10
V1 = 40, V2 = 8, V3 = 1

The corresponding CFU for each dilution step is calculated using the power function. For this, the power to base 10 with the exponent corresponding to the dilution step is determined and multiplied by the entered CFU value.
V0 = V0 * 0th power of 10
V0 = 290 * 1 = 290

V1 = V1 * 1st power of 10
V1 = 40 * 10 = 400

The mean value is calculated from all valid CFU values and given as the Result.
(V0 + V1 + V2 + V3) / 5 = Result

Since V2 and V3 do not meet the criterion "at least 10 CFU", they are not included in the mean value calculation.
(V0 + V1) / 2 = Result
(290 + 400) / 2 = 345

These components of the mathematical formula notation are combined with the Labordatenbank notation. In the Labordatenbank notation, the abbreviation of the parameter whose value is used for the calculation and the number of the column in which the respective measured value is located are specified.

Result = ['gkz']['0']
V0 = ['gkz']['1']
V1 = ['gkz']['2']
V2 = ['gkz']['3']
V3 = ['gkz']['4']

The conditions for a valid Result are combined with the calculations using an If-then query. The If-then query follows the principle if(condition, then, else). The content of a query is indicated by if() and the consequences of a condition are separated by ,. The components of a query or nested queries are linked by && (=and) or || (=or) and , (=neutral concatenation). Every query and calculation function that started with a ( must also be closed with a ). An overarching If-then query is closed after the ultimate consequence. A variable number or string, depending on the entry, is indicated by ''.

This results in the following formulas in Labordatenbank notation:

For checking whether the exclusion criteria of the first condition apply:

if(	(m['gkz']['1'] >= 1 && m['gkz']['2'] >= 1 && m['gkz']['1'] < m['gkz']['2']) ||
		(m['gkz']['2'] >= 1 && m['gkz']['3'] >= 1 && m['gkz']['2'] < m['gkz']['3']) ||
		(m['gkz']['3'] >= 1 && m['gkz']['4'] >= 1 && m['gkz']['3'] < m['gkz']['4']),
'invalid DS',

The m stands for matrix notation for using the parameter columns.

For determining which value was entered:

if(	(m['gkz']['1'] == '' || m['gkz']['1'] === '0') &&
		(m['gkz']['2'] == '' || m['gkz']['2'] === '0') &&
		(m['gkz']['3'] == '' || m['gkz']['3'] === '0') &&
		(m['gkz']['4'] == '' || m['gkz']['4'] === '0'),

If 0 was entered, the Result is indicated accordingly:

	if(	m['gkz']['1'] === '0', '< 1',
	if(	m['gkz']['2'] === '0', '< 10',
	if(	m['gkz']['3'] === '0', '< 100',
	if(	m['gkz']['4'] === '0', '< 1000')))),

Then it is checked whether the upper limit was exceeded:

if(	(m['gkz']['1'] == '' || m['gkz']['1'] >= 300) &&
		(m['gkz']['2'] == '' || m['gkz']['2'] >= 300) &&
		(m['gkz']['3'] == '' || m['gkz']['3'] >= 300) &&
		(m['gkz']['4'] == '' || m['gkz']['4'] >= 300),

If a value greater than 299 was entered, the Result is indicated accordingly.

if(m['gkz']['1'] >= 300, '> 300',
              if( m['gkz']['2'] >= 300, '> 3000',
             if(m['gkz']['3'] >= 300, '> 30000',
             if(m['gkz']['4'] >= 300, '> 300000')))),

If not, the mean value of those values that meet the criterion "at least 1 or 10 CFU" is calculated. Mean value calculation and power function are linked in this example. Base and exponent of the respective power calculation are indicated with power(), are within the parentheses, and are separated by ,. The values underlying a mean value are indicated and calculated with the function mean() or avg(). They are placed between the parentheses and separated by ,.

Since V0 is the first dilution step with a value greater than 0, the following formula applies:

if(m['gkz']['1'] > 0,
	mean(
	m['gkz']['1']*power(10, 0),
	if(m['gkz']['2'] >= 10, m['gkz']['2']*power(10, 1)),
	if(m['gkz']['3'] >= 10, m['gkz']['3']*power(10, 2)),
	if(m['gkz']['4'] >= 10, m['gkz']['4']*power(10, 3))),

If an alternative dilution step (V1, V2 or V3) is the first with a value greater than 0, the corresponding formula is applied. See below, formula / illustration for the formula field.

The complete formula is as follows:

wenn(	(m['gkz']['1'] >= 1 && m['gkz']['2'] >= 1 && m['gkz']['1'] < m['gkz']['2']) ||
		(m['gkz']['2'] >= 1 && m['gkz']['3'] >= 1 && m['gkz']['2'] < m['gkz']['3']) ||
		(m['gkz']['3'] >= 1 && m['gkz']['4'] >= 1 && m['gkz']['3'] < m['gkz']['4']), 
		'ungültige VS',

wenn(	(m['gkz']['1'] == '' || m['gkz']['1'] === '0') && 
		(m['gkz']['2'] == '' || m['gkz']['2'] === '0') && 
		(m['gkz']['3'] == '' || m['gkz']['3'] === '0') && 
		(m['gkz']['4'] == '' || m['gkz']['4'] === '0'),
		
	wenn(	m['gkz']['1'] === '0', '< 1',
	wenn(	m['gkz']['2'] === '0', '< 10',
	wenn(	m['gkz']['3'] === '0', '< 100',
	wenn(	m['gkz']['4'] === '0', '< 1000')))),

wenn(	(m['gkz']['1'] == '' || m['gkz']['1'] >= 300) && 
		(m['gkz']['2'] == '' || m['gkz']['2'] >= 300) && 
		(m['gkz']['3'] == '' || m['gkz']['3'] >= 300) && 
		(m['gkz']['4'] == '' || m['gkz']['4'] >= 300),
		
	wenn(	m['gkz']['1'] >= 300, '> 300',
        wenn(	m['gkz']['2'] >= 300, '> 3000',
        wenn(	m['gkz']['3'] >= 300, '> 30000',
        wenn(	m['gkz']['4'] >= 300, '> 300000')))),	

wenn(m['gkz']['1'] > 0,
	mittelwert(
	m['gkz']['1']*potenz(10, 0),
	wenn(m['gkz']['2'] >= 10, m['gkz']['2']*potenz(10, 1)),
	wenn(m['gkz']['3'] >= 10, m['gkz']['3']*potenz(10, 2)),
	wenn(m['gkz']['4'] >= 10, m['gkz']['4']*potenz(10, 3))),

wenn(m['gkz']['2'] > 0,
	mittelwert(
	m['gkz']['2']*10,
	wenn(m['gkz']['3'] >= 10, m['gkz']['3']*potenz(10, 2)),
	wenn(m['gkz']['4'] >= 10, m['gkz']['4']*potenz(10, 3))),

wenn(m['gkz']['3'] > 0,
	mittelwert(
	m['gkz']['3']*potenz(10, 2),
	wenn(m['gkz']['4'] >= 10, m['gkz']['4']*potenz(10, 3))),

wenn(m['gkz']['4'] > 0,
	mittelwert(
	m['gkz']['4']*potenz(10, 3)),
	'error'     )))))))

The formulas are stored in the formula field of the column where the Result should appear.
To access the mask for storing formulas, click on the blue-written name of the parameter, e.g., "gkz - aerobic mesophilic total count".

Further details can be found under Deposit formulas with parameters.