Calculation Formula Examples
This article contains worked calculation formula examples for common laboratory test method outputs. Use these as starting points and update the input numbers, limits, constants, units, and wording to match the test method.
Each formula is shown in its own code block so it can be selected and copied without selecting surrounding text.
Contents
Copying Values, Text, and Pass/Fail Results
Averages, Variation, And Correlation
Limits, LOD, LOQ, And Guarded Calculations
Microbiology And Colony Counts
Assay, Dissolution, And Dilution Calculations
Physical, Material, And Instrument Calculations
Copying Values, Text, And Pass/Fail Results
Report input 1 exactly
$1
Report fixed text
"Not detected"
Report fixed supporting text
"Reported from instrument output"
Report Complies only when three text inputs comply
$1 == "Complies" && $2 == "Complies" && $3 == "Complies" ? "Complies" : "Does not comply"
Report Pass or Fail from a numeric limit
$1 <= 10 ? "Pass" : "Fail"
Report Present unless a microbiology input is Absent
$1 == "Absent" ? "Absent" : "Present"
Basic Numeric Calculations
Difference between two weights
$1 - $2
Percent difference
($1 - $2) / $2 * 100
Percent recovery
$3 * 100 / AVG($1, $2)
Percent label claim
AVG($1, $2) / $3 * 100
Convert grams to milligrams
$1 * 1000
Average net weight from gross and tare values
AVG(($1 - $2), ($3 - $4), ($5 - $6))
Moisture or loss on drying percentage
($1 - $2) / $1 * 100
Bulk density
$2 / $1
Averages, Variation, And Correlation
Average inputs 1 to 6
AVG($1::$6)
Minimum inputs 1 to 6
MIN($1::$6)
Maximum inputs 1 to 6
MAX($1::$6)
Range as a number
MAX($1::$6) - MIN($1::$6)
Relative standard deviation, RSD, or CV
STDEV($1::$6) / AVG($1::$6) * 100
Population standard deviation
STDEVP($1::$6)
Correlation between two three-point series
The first half of the values are the x-values. The second half are the y-values. Assay, Dissolution, And Dilution Calculations
CORREL($1, $2, $3, $4, $5, $6)
Absolute difference from a target
ABS($1 - $2)
Average rounded away from zero
ROUND(AVG($1::$6), 0, MidpointRounding.AwayFromZero)
Limits, LOD, LOQ, And Guarded Calculations
Report a less-than value below a reporting limit
$1 < 0.05 ? "<0.05" : STR($1)
Report LOD, LOQ, or the numeric value
$1 < 0.005 ? "<LOD" : ($1 < 0.02 ? "<LOQ" : STR($1))
Treat BDL as zero before calculating
$1 == "BDL" ? 0 : NUM($1) * $2
Skip a calculation for NQ
$1 == "NQ" ? "NQ" : STR(NUM($1) / $2)
Report over-range values
$1 > 1000 ? ">1000" : STR($1)
Total impurities, treating values below the disregard limit as zero
($1 < 0.05 ? 0 : $1) + ($2 < 0.05 ? 0 : $2) + ($3 < 0.05 ? 0 : $3)
Microbiology And Colony Counts
Average two plate counts
AVG($1, $2)
Colony count using a dilution factor
AVG($2, $3) * $1
Total microbial count with a multiplier
AVG($2, $3) * $1 * 10
Use a default multiplier when input 1 is zero
$1 == 0 ? AVG($2, $3) * 2 : AVG($2, $3) * $1 * 2
Report low average colony counts as a whole number
AVG($1, $2) < 1 ? FLOOR(AVG($1, $2)) : AVG($1, $2)
Presence or absence result
$1 == "Absent" ? "Absent" : "Present"
Assay, Dissolution, And Dilution Calculations
Result using fixed method constants
($1 * $2) / ($3 * $4)
Dissolution vessel result with correction factors
($1 * $2 * $3 * 1000) / ($4 * $5 * $6)
Mean dissolution from six vessels
AVG($1::$6)
Minimum dissolution from six vessels
MIN($1::$6)
Number of results below a specification limit
($1 < $7 ? 1 : 0) + ($2 < $7 ? 1 : 0) + ($3 < $7 ? 1 : 0) + ($4 < $7 ? 1 : 0) + ($5 < $7 ? 1 : 0) + ($6 < $7 ? 1 : 0)
Content uniformity range as text
STR(ROUND(MIN($1::$10), 1)) + " - " + STR(ROUND(MAX($1::$10), 1))
Acceptance value style calculation
AVG($1::$10) < 98.5 ? 98.5 - AVG($1::$10) + (2.4 * STDEV($1::$10)) : (AVG($1::$10) > 101.5 ? AVG($1::$10) - 101.5 + (2.4 * STDEV($1::$10)) : 2.4 * STDEV($1::$10))
Physical, Material, And Instrument Calculations
Particle size percentage retained
($2 / ($1 + $2 + $3 + $4)) * 100
Percent passing a sieve
100 - $1Number of results below a specification limit ($1 < $7 ? 1 : 0) + ($2 < $7 ? 1 : 0) + ($3 < $7 ? 1 : 0) + ($4 < $7 ? 1 : 0) + ($5 < $7 ? 1 : 0) + ($6 < $7 ? 1 : 0)
Burn rate
($1 / $2) * 60
Shrinkage percentage
($2 - $1) / $1 * 100
Density from mass and volume
$1 / $2
Corrected concentration
($1 * $2 * $3) / ($4 * $5)
Colour difference using three channels
SQRT(POW($1 - $4, 2) + POW($2 - $5, 2) + POW($3 - $6, 2))
Specific surface style calculation
($1 / $4) * SQRT(POW($2, 3)) / (1 - $2) * SQRT($3) / SQRT(10 * $5)
Average dimensions as width x length
STR(ROUND(AVG($1::$5), 1)) + " x " + STR(ROUND(AVG($6::$10), 1))
Display Result Calculations
Display result calculations use $R , the value produced by the result calculation. Use display calculations when the result should be shown with text, rounding, units, grading, or pass/fail wording.
Display the result unchanged
$R
Round the result to two decimal places
ROUND($R, 2)
Add units to a rounded result
STR(ROUND($R, 2)) + " mg/L"
Report a less-than value below a display limit
$R < 0.05 ? "<0.05" : STR($R)
Pass/fail display from a limit
$R <= 10 ? "Pass" : "Fail"
Display below limit, not quantifiable, over range, or the result
$R < 0.15 ? "Below limit" : ($R < 0.40 ? "Not quantifiable" : ($R > 195 ? "Over range" : STR($R)))
Convert a numeric score into grade text
$R < 1.25 ? "Grade 1" : ($R < 1.75 ? "Grade 2" : ($R < 2.25 ? "Grade 3" : "Grade 4"))
Combine a numeric result with a grade
STR($R) + " [" + ($R < 1.25 ? "Grade 1" : ($R < 1.75 ? "Grade 2" : "Grade 3")) + "]"
Map a numeric code to text
$R == 1 ? "No defects" : ($R == 2 ? "Slight wear" : ($R == 3 ? "Moderate wear" : "Fail"))
Rounding Examples
Round using the default midpoint rounding option
ROUND($1)
Round to two decimal places
ROUND($1, 2)
Round midpoint values away from zero
ROUND($1, 0, MidpointRounding.AwayFromZero)
Round toward zero
ROUND($1, 1, MidpointRounding.ToZero)
Round upward
ROUND($1, MidpointRounding.ToPositiveInfinity)
Round downward
ROUND($1, MidpointRounding.ToNegativeInfinity)
Notes
- Replace input numbers with the input references used by the test method.
- Replace constants, limits, and units with values from the validated method.
- Use display calculations for formatting text shown on reports.
- Test each formula before publishing the test method.