LOD and LOQ from a calibration curve in Excel

The LOD and LOQ formulas

The ICH Q2(R2) guideline on validating analytical procedures describes an approach based on the calibration curve:

LOD = 3.3 × σ ÷ S and LOQ = 10 × σ ÷ S

S, the slope, converts a signal into a concentration, so the limits come out in concentration units. σ is the standard deviation of the response near zero. It can be estimated from the residuals of a calibration line or from replicate blanks. The factor 3.3 is roughly 2 × 1.645, which keeps both false positives and false negatives near 5% at the LOD. The factor 10 means a result at the LOQ has a relative standard deviation of about 10%.

Step 1: fit the calibration curve

Measure standards of known concentration, ideally at the low end of the range, near the expected limits. The example uses seven standards from 0 to 10 µg/L. In Excel, with signals in C2:C8 and concentrations in B2:B8 (y first):

• =SLOPE(C2:C8,B2:B8) gives S = 2.089 mAU per µg/L
• =INTERCEPT(C2:C8,B2:B8) gives 1.020 mAU, the signal of a blank
• =RSQ(C2:C8,B2:B8) gives R² = 0.9994

Check the residuals, the measured minus the predicted signal, in column D. They should scatter around 0 without a curve or a funnel shape. Plot the standards as an XY scatter chart with a linear trendline, never as a line chart, which would space the concentrations evenly.

Step 2: σ from the residual standard deviation

Excel's STEYX(known_y, known_x) returns the residual standard deviation s_y/x of the line, the typical scatter of the points around it. Here it is 0.202 mAU. Then:

LOD = 3.3 × 0.202 ÷ 2.089 = 0.32 µg/L and LOQ = 10 × 0.202 ÷ 2.089 = 0.97 µg/L.

Use a calibration close to the limit for this. The scatter of a curve that runs up to much higher concentrations is usually larger in absolute terms, and it would overstate the LOD.

Worked example: a low-level nitrate calibration

Standard concentrations, signals, the blank standard deviation and the sample signals are yellow inputs. Slope, intercept, R², s_y/x, both pairs of limits and the sample reports are formulas.

Worked example: a low-level nitrate calibration

	A	B	C	D
1	Standard	Conc (µg/L)	Signal (mAU)	Residual (mAU)
2	S0	0	0.9	-0.120
3	S1	1	3.3	0.191
4	S2	2	5.0	-0.198
5	S3	4	9.6	0.224
6	S4	6	13.4	-0.154
7	S5	8	17.9	0.168
8	S6	10	21.8	-0.111
9
10	Slope S (mAU per µg/L)	2.089
11	Intercept (mAU)	1.020
12	R²	0.9994
13	σ = residual SD s_y/x (mAU)	0.202
14	LOD = 3.3σ ÷ S (µg/L)	0.32
15	LOQ = 10σ ÷ S (µg/L)	0.97
16	σ from 8 blanks, STDEV.S (mAU)	0.293
17	LOD from blanks (µg/L)	0.46
18	LOQ from blanks (µg/L)	1.40
19
20	Sample	Signal (mAU)	Conc (µg/L)	Report
21	Well A	1.5	0.23	ND
22	Spring B	2.6	0.76	DNQ
23	Tap C	5.9	2.34	2.3

Fictional data for teaching. This covers the statistics only, not lab procedures or the full validation a method needs.

Alternative: σ from blank measurements

The second common estimate of σ is the standard deviation of repeated blank measurements, =STDEV.S(blanks). Eight blanks with a standard deviation of 0.2925 mAU give an LOD of 0.46 µg/L and an LOQ of 1.40 µg/L with the same slope.

The two methods rarely agree exactly. Neither is wrong: report which one you used, and confirm the chosen limit by measuring samples spiked at or near it. A calculated limit is an estimate until real samples show that it works.

Step 3: report results near the limits

Convert each sample's signal into a concentration with the calibration line, =(signal − intercept) ÷ slope, then compare it with the limits:

• below the LOD: ND, not detected (often written as < LOD)
• from the LOD to below the LOQ: DNQ, detected but not quantified
• at or above the LOQ: report the number

In the example, Well A (0.23 µg/L) is ND, Spring B (0.76 µg/L) is DNQ, and Tap C is reported as 2.3 µg/L. In Excel: =IF(C21<$B$14,"ND",IF(C21<$B$15,"DNQ",ROUND(C21,1))).

Common mistakes

• Using a calibration over a wide range. High standards inflate s_y/x. Estimate the limits from standards near the expected LOD.
• Reading R² as a detection limit. A curve with R² = 0.999 can still have a high LOD. Only σ and the slope set the limits.
• Mixing units. With the slope in mAU per µg/L the limits come out in µg/L. A slope per mg/L gives mg/L.
• Using STDEV.P for a few blanks. Replicates are a sample, so use STDEV.S, and measure enough blanks to estimate σ reliably.
• Reporting numbers below the LOQ. Results between LOD and LOQ are detected but too imprecise to quote as a value.
• Never confirming the limit. Analyse samples spiked near the calculated LOD and LOQ to show that the method really reaches them.