Calibration curve and unknown concentration calculator (Beer-Lambert)

Least-squares calibration line, r squared, LOD and LOQ, and unknown concentrations from absorbance.

Downloads are included in the $19.85 yearly membership. Sign in

XLSXCSVODSXMLNumbers

	A	B	C	D	E	F	G	H	I	J	K	L
1	Calibration curve and detection limits (Beer-Lambert)
2	Example data — replace the blue input cells with your own.
3
4	SLOPE (ABSORBANCE PER UNIT)	R SQUARED	LOD (UNIT)
5	0.0498	1.0000	0.056
6
7	Method
8	Analyte	Example analyte	Label only
9	Wavelength (nm)	540	Wavelength of the absorbance reading
10	Path length (cm)	1.000	Cuvette path length
11	Concentration unit	mg/L	Label used in the results
12
13	Standards (capacity 12; six example standards). Replicate absorbances are inputs.									Fit helper columns
14	Standard	Concentration	Absorbance 1	Absorbance 2	Absorbance 3	Mean absorbance	Std dev of replicates	Predicted absorbance	Residual	Used (1 or 0)	x used	y used
15	S0	0.0	0.003	0.002	0.004	0.0030	0.0010	0.0020	0.0010	1	0.0	0.0030
16	S1	2.0	0.104	0.098	0.101	0.1010	0.0030	0.1017	-0.0007	1	2.0	0.1010

Showing the first 16 of 43 rows and 12 of 12 columns. Cells with formulas show the formula on hover.

	A	B	C	D	E	F	G
1	Unknown concentrations from absorbance
2	Reads each sample from the calibration line on the Curve tab and applies its dilution factor.
3
4	SAMPLES READ	ABOVE HIGHEST STANDARD		BELOW LOQ
5	8	2		1
6
7	Curve used (from the Curve tab)
8	Slope (absorbance per unit)	0.0498
9	Intercept (absorbance)	0.0020
10	Highest standard (unit)	10.0	Samples above this value are flagged, not extrapolated
11	LOQ (unit)	0.171
12	Concentration unit	mg/L
13
14	Samples (capacity 50; eight example samples)
15	Sample ID	Absorbance	Dilution factor	Concentration in measured solution	Concentration in original sample	Flag
16	U-01	0.1520	1.0	3.010	3.010	OK	Concentration = (A - intercept) / slope; original = measured x dilution factor

Showing the first 16 of 65 rows and 7 of 7 columns. Cells with formulas show the formula on hover.

	A
1	Calibration curve and unknown concentration calculator (Beer-Lambert)
2	Notes: what this workbook does, how to use it and the method behind it.
3
4	What it does
5	Fits a straight line to standards of known concentration and their absorbance, then converts the absorbance of unknown samples to concentration.
6	Reports the slope, intercept, r squared, the residual standard deviation, and the limit of detection and quantitation from the fit.
7
8	How to use it
9	1. On the Curve tab, enter the analyte, wavelength, path length and the concentration unit.
10	2. Enter each standard's concentration and up to three absorbance replicates. Use up to 12 standards.
11	3. Read the slope, r squared and LOD in the tiles. Check the fit message in the fit block.
12	4. On the Unknowns tab, enter each sample's absorbance and dilution factor. Use up to 50 rows.
13	5. Read the concentration in the measured solution and in the original sample, and check each flag.
14
15	Formulas and method
16	Mean absorbance = average of the replicates. Standard deviation of replicates = SQRT(sum of squared deviations / (count - 1)).

Showing the first 16 of 32 rows and 1 of 1 columns. Cells with formulas show the formula on hover.

What does this template do?

This workbook fits a calibration line to absorbance measurements of standards and reads unknown samples from it, following the Beer-Lambert relationship between absorbance and concentration. Enter up to twelve standards with three absorbance replicates each. The Curve tab averages the replicates, computes their standard deviation and fits a least-squares line to the mean absorbance.

The fit uses SUMPRODUCT formulas, so no SLOPE or INTERCEPT function is needed. The sheet reports the slope, intercept, r squared and the residual standard deviation s y. It also gives the limit of detection as 3.3 times s y over the slope, the limit of quantitation as 10 times s y over the slope, and the absorptivity as the slope over the path length.

The Unknowns tab takes up to fifty sample absorbances with dilution factors. It converts each one to the concentration in the measured solution and in the original sample, and it flags any value above the highest standard or below the LOQ. The standards and samples are example data for a fictional analyte at 540 nm.

What’s inside

Least-squares slope and intercept from SUMPRODUCT, with no SLOPE or INTERCEPT function
Replicate means and standard deviations for every standard
LOD = 3.3 s y / slope and LOQ = 10 s y / slope from the residual scatter
Unknown concentrations corrected for dilution, with flags above the range or below the LOQ
Room for twelve standards and fifty unknown samples

Which tabs does the workbook have?

Tab	What it holds
Curve	Method inputs, standards with replicates, least-squares fit, r squared, LOD and LOQ.
Unknowns	Sample absorbances and dilution factors converted to concentrations, with range flags.
Notes	What the workbook does, the method, assumptions and limits.

What formulas does this template use?

This template holds 260 formulas in 419 cells across 3 tabs, so 62% of its cells calculate. They use 12 distinct functions; the longest formula is 130 characters and 5 of them read from another tab.

Function	Uses	What it does
`IF`	448	one result when a test is true, another when false
`ISNUMBER`	126	true for a number
`OR`	114	true when any test is true
`NOT`	50	reverses true and false
`COUNT`	37	counts numeric cells
`AVERAGE`	24	mean of numbers
`SUMPRODUCT`	16	multiplies matching entries, then adds them
`AND`	13	true when every test is true
`SQRT`	13	square root
`SUM`	3	adds numbers
`COUNTIF`	2	counts cells meeting one condition
`MAX`	1	largest value

Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

On the Curve tab, enter the analyte, wavelength, path length and concentration unit.
Enter each standard's concentration and up to three absorbance replicates, using up to twelve rows.
Read the slope, r squared and LOD in the tiles, and check the fit message.
On the Unknowns tab, enter each sample's absorbance and dilution factor.
Read the concentration in the measured solution and in the original sample, and act on each flag.

What is it good for?

Quantifying a dye or metal in water by UV-visible absorbance
Reporting detection and quantitation limits from a calibration run
Checking a calibration line before reading samples
Diluting samples that read above the highest standard

Questions about this sheet

Why is a sample flagged as above the highest standard?

Its measured-solution concentration is higher than the top standard, so the line is not extrapolated. Dilute the sample, read it again and multiply by the new dilution factor.

Does the LOD come from a formal validation?

No. It is estimated from the scatter of the standards around the fitted line. A formal method validation is needed before reporting detection limits.

How is the unknown concentration calculated?

Concentration equals absorbance minus the intercept, divided by the slope. Multiply by the dilution factor to get the concentration in the original sample.