Instrumental Beer's Law Deviations Version 3

Wavelength
Slit width
Absorber Maximum Absorption Unabsorbed Random Press f9 to Linear least-squares fit to calibrat
setting width Absorptivity Path length stray light noise re-calculate 0.010
300 10 100 1 1 ### ✘ Photon
TRUE
✘ Detector
TRUE 0.009 f(x) = 0.8726x + 0.0007
R² = 0.9726
Click the arrows above to change the values Concentratio Measured 0.008
1.20 12.00
n quantity
Incident intensity 0.0010 ### 0.007
Absorptivity 0.0020 ###

Measured quantity (y)

1.00 10.00 0.0030 ### 0.006
0.0040 ###
0.005
0.80 8.00 0.0050 =Sheet2.I51
0.0060 ### 0.004
0.0070 ###
0.60 6.00 0.0080 ### 0.003
0.0090 ###
0.40 4.00 0.0100 ### 0.002

Measured quantity 0.001

0.20 2.00 1 ● Absorbance (log(Io/I)
Transmission (I/Io) 0.000
Io-I 0.00 0.00 0.00 0.01 0.01
0.00 0.00 Incident intensity (Io)
200 250 300 350 400 Concentration (x)
Transmitted intensity (I)
10
Wavelength, nm
Concentration prediction error as % of max con
15.000%
Blue text indicates Absorption peak shape 10.000%
user input cells. 1 ● Gaussian 5.000%

% error
Lorentzian 0.000%
-5.000%
σ of errors ### -10.000%
Max. error ### 0.00 0.00 0.00 0.01 0.0

Concentration

Seite 1
Named variables
W Wavelength
amax Maximum absorptivity
aw Absorber width (half-width of the absorption band)
b Absorption path length
Izero Incident intensity
sl Stray light (fraction of incident light)
sw Source width (spectral bandpass of monochromator)
slope Slope of straight-line least squares fit to calibration curve
intercept Intercept of straight-line least squares fit to calibration curve
© T. C. O'Haver, Version 3, Oct. 6, 2008
This version adds photon and detector noise to version 2

Seite 2
 Linear least-squares fit to calibration curve
0.010

0.009 f(x) = 0.8726x + 0.0007

R² = 0.9726
0.008

0.007
Measured quantity (y)

0.006

0.005

0.004

0.003

0.002

0.001

0.000
0.00 0.00 0.00 0.01 0.01 0.01 0.01

Concentration (x)

Concentration prediction error as % of max concentration

15.000%
10.000%
5.000%
% error

0.000%
-5.000%
-10.000%
0.00 0.00 0.00 0.01 0.01 0.01 0.01

Concentration

Seite 3
Wavelength Incident intensity
200 =1-200*ABS((A3-W)/(100))/(2*sw) =sw*IF(B3<0,0,B3)
=A3+5 =1-200*ABS((A4-W)/(100))/(2*sw) =sw*IF(B4<0,0,B4)
=A4+5 =1-200*ABS((A5-W)/(100))/(2*sw) =sw*IF(B5<0,0,B5)
=A5+5 =1-200*ABS((A6-W)/(100))/(2*sw) =sw*IF(B6<0,0,B6)
=A6+5 =1-200*ABS((A7-W)/(100))/(2*sw) =sw*IF(B7<0,0,B7)
=A7+5 =1-200*ABS((A8-W)/(100))/(2*sw) =sw*IF(B8<0,0,B8)
=A8+5 =1-200*ABS((A9-W)/(100))/(2*sw) =sw*IF(B9<0,0,B9)
=A9+5 =1-200*ABS((A10-W)/(100))/(2*sw) =sw*IF(B10<0,0,B10)
=A10+5 =1-200*ABS((A11-W)/(100))/(2*sw) =sw*IF(B11<0,0,B11)
=A11+5 =1-200*ABS((A12-W)/(100))/(2*sw) =sw*IF(B12<0,0,B12)
=A12+5 =1-200*ABS((A13-W)/(100))/(2*sw) =sw*IF(B13<0,0,B13)
=A13+5 =1-200*ABS((A14-W)/(100))/(2*sw) =sw*IF(B14<0,0,B14)
=A14+5 =1-200*ABS((A15-W)/(100))/(2*sw) =sw*IF(B15<0,0,B15)
=A15+5 =1-200*ABS((A16-W)/(100))/(2*sw) =sw*IF(B16<0,0,B16)
=A16+5 =1-200*ABS((A17-W)/(100))/(2*sw) =sw*IF(B17<0,0,B17)
=A17+5 =1-200*ABS((A18-W)/(100))/(2*sw) =sw*IF(B18<0,0,B18)
=A18+5 =1-200*ABS((A19-W)/(100))/(2*sw) =sw*IF(B19<0,0,B19)
=A19+5 =1-200*ABS((A20-W)/(100))/(2*sw) =sw*IF(B20<0,0,B20)
=A20+5 =1-200*ABS((A21-W)/(100))/(2*sw) =sw*IF(B21<0,0,B21)
=A21+5 =1-200*ABS((A22-W)/(100))/(2*sw) =sw*IF(B22<0,0,B22)
=A22+5 =1-200*ABS((A23-W)/(100))/(2*sw) =sw*IF(B23<0,0,B23)
=A23+5 =1-200*ABS((A24-W)/(100))/(2*sw) =sw*IF(B24<0,0,B24)
=A24+5 =1-200*ABS((A25-W)/(100))/(2*sw) =sw*IF(B25<0,0,B25)
=A25+5 =1-200*ABS((A26-W)/(100))/(2*sw) =sw*IF(B26<0,0,B26)
=A26+5 =1-200*ABS((A27-W)/(100))/(2*sw) =sw*IF(B27<0,0,B27)
=A27+5 =1-200*ABS((A28-W)/(100))/(2*sw) =sw*IF(B28<0,0,B28)
=A28+5 =1-200*ABS((A29-W)/(100))/(2*sw) =sw*IF(B29<0,0,B29)
=A29+5 =1-200*ABS((A30-W)/(100))/(2*sw) =sw*IF(B30<0,0,B30)
=A30+5 =1-200*ABS((A31-W)/(100))/(2*sw) =sw*IF(B31<0,0,B31)
=A31+5 =1-200*ABS((A32-W)/(100))/(2*sw) =sw*IF(B32<0,0,B32)
=A32+5 =1-200*ABS((A33-W)/(100))/(2*sw) =sw*IF(B33<0,0,B33)
=A33+5 =1-200*ABS((A34-W)/(100))/(2*sw) =sw*IF(B34<0,0,B34)
=A34+5 =1-200*ABS((A35-W)/(100))/(2*sw) =sw*IF(B35<0,0,B35)
=A35+5 =1-200*ABS((A36-W)/(100))/(2*sw) =sw*IF(B36<0,0,B36)
=A36+5 =1-200*ABS((A37-W)/(100))/(2*sw) =sw*IF(B37<0,0,B37)

Seite 4
 =A37+5 =1-200*ABS((A38-W)/(100))/(2*sw) =sw*IF(B38<0,0,B38)
=A38+5 =1-200*ABS((A39-W)/(100))/(2*sw) =sw*IF(B39<0,0,B39)
=A39+5 =1-200*ABS((A40-W)/(100))/(2*sw) =sw*IF(B40<0,0,B40)
=A40+5 =1-200*ABS((A41-W)/(100))/(2*sw) =sw*IF(B41<0,0,B41)
=A41+5 =1-200*ABS((A42-W)/(100))/(2*sw) =sw*IF(B42<0,0,B42)
=A42+5 =1-200*ABS((A43-W)/(100))/(2*sw) =sw*IF(B43<0,0,B43)
Izero
Total intensity =SUM(C3:C43)+sl*SUM(C3:C43)

Measured absorbance (y)

Theoretical absorbance

Noise calculations
Photon Noise

Detector noise

Seite 5
Absorptivity =Sheet1.H6
### =$C3*10^(-$D3*b*E$2)
### =$C4*10^(-$D4*b*E$2)
### =$C5*10^(-$D5*b*E$2)
### =$C6*10^(-$D6*b*E$2)
### =$C7*10^(-$D7*b*E$2)
### =$C8*10^(-$D8*b*E$2)
### =$C9*10^(-$D9*b*E$2)
### =$C10*10^(-$D10*b*E$2)
### =$C11*10^(-$D11*b*E$2)
### =$C12*10^(-$D12*b*E$2)
### =$C13*10^(-$D13*b*E$2)
### =$C14*10^(-$D14*b*E$2)
### =$C15*10^(-$D15*b*E$2)
### =$C16*10^(-$D16*b*E$2)
### =$C17*10^(-$D17*b*E$2)
### =$C18*10^(-$D18*b*E$2)
### =$C19*10^(-$D19*b*E$2)
### =$C20*10^(-$D20*b*E$2)
### =$C21*10^(-$D21*b*E$2)
### =$C22*10^(-$D22*b*E$2)
### =$C23*10^(-$D23*b*E$2)
### =$C24*10^(-$D24*b*E$2)
### =$C25*10^(-$D25*b*E$2)
### =$C26*10^(-$D26*b*E$2)
### =$C27*10^(-$D27*b*E$2)
### =$C28*10^(-$D28*b*E$2)
### =$C29*10^(-$D29*b*E$2)
### =$C30*10^(-$D30*b*E$2)
### =$C31*10^(-$D31*b*E$2)
### =$C32*10^(-$D32*b*E$2)
### =$C33*10^(-$D33*b*E$2)
### =$C34*10^(-$D34*b*E$2)
### =$C35*10^(-$D35*b*E$2)
### =$C36*10^(-$D36*b*E$2)
### =$C37*10^(-$D37*b*E$2)

Seite 6
 ### =$C38*10^(-$D38*b*E$2)
### =$C39*10^(-$D39*b*E$2)
### =$C40*10^(-$D40*b*E$2)
### =$C41*10^(-$D41*b*E$2)
### =$C42*10^(-$D42*b*E$2)
### =$C43*10^(-$D43*b*E$2)

Total transmitted intensity =SUM(E3:E43)+sl*$C$45

Measured quantity = I ###
Measured quantity = Io ###
Measured quantity = Io - I ###
Measured quantity = absorbance ###
Measured quantity = transmission ###
Selected measured quantity = =SUM(E46:E50)
amax*b*c= =E2*MAX($D3:$D43)*b
Predicted concentration = =(E51-intercept)/slope
Concentration error (real-predicted) =E2-E53
Concentration error as % of max concentration =E54/MAX(E2:N2)
Standard deviation of errors =STDEV(E55:N55)

Random Noise calculations Photon noise in Izero and I, respectively (if “Photo”
Photon noise ###
=SQRT(Izero)/1000 ###

Detector noise Detector noise in Izero and I, respectively (if “Detec

2.00E-02 ###
###

Seite 7
 Concentration
=Sheet1.H7 =Sheet1.H8 =Sheet1.H9 =Sheet1.H10 =Sheet1.H11 =Sheet1.H12 =Sheet1.H13 =Sheet1.H14
=$C3*10^(-$D3*b*F$2) =$C3*10^(-$D3*b*G$2) ### ### ### ### ### ###
=$C4*10^(-$D4*b*F$2) =$C4*10^(-$D4*b*G$2) ### ### ### ### ### ###
=$C5*10^(-$D5*b*F$2) =$C5*10^(-$D5*b*G$2) ### ### ### ### ### ###
=$C6*10^(-$D6*b*F$2) =$C6*10^(-$D6*b*G$2) ### ### ### ### ### ###
=$C7*10^(-$D7*b*F$2) =$C7*10^(-$D7*b*G$2) ### ### ### ### ### ###
=$C8*10^(-$D8*b*F$2) =$C8*10^(-$D8*b*G$2) ### ### ### ### ### ###
=$C9*10^(-$D9*b*F$2) =$C9*10^(-$D9*b*G$2) ### ### ### ### ### ###
=$C10*10^(-$D10*b*F$2) =$C10*10^(-$D10*b*G$2) ### ### ### ### ### ###
=$C11*10^(-$D11*b*F$2) =$C11*10^(-$D11*b*G$2) ### ### ### ### ### ###
=$C12*10^(-$D12*b*F$2) =$C12*10^(-$D12*b*G$2) ### ### ### ### ### ###
=$C13*10^(-$D13*b*F$2) =$C13*10^(-$D13*b*G$2) ### ### ### ### ### ###
=$C14*10^(-$D14*b*F$2) =$C14*10^(-$D14*b*G$2) ### ### ### ### ### ###
=$C15*10^(-$D15*b*F$2) =$C15*10^(-$D15*b*G$2) ### ### ### ### ### ###
=$C16*10^(-$D16*b*F$2) =$C16*10^(-$D16*b*G$2) ### ### ### ### ### ###
=$C17*10^(-$D17*b*F$2) =$C17*10^(-$D17*b*G$2) ### ### ### ### ### ###
=$C18*10^(-$D18*b*F$2) =$C18*10^(-$D18*b*G$2) ### ### ### ### ### ###
=$C19*10^(-$D19*b*F$2) =$C19*10^(-$D19*b*G$2) ### ### ### ### ### ###
=$C20*10^(-$D20*b*F$2) =$C20*10^(-$D20*b*G$2) ### ### ### ### ### ###
=$C21*10^(-$D21*b*F$2) =$C21*10^(-$D21*b*G$2) ### ### ### ### ### ###
=$C22*10^(-$D22*b*F$2) =$C22*10^(-$D22*b*G$2) ### ### ### ### ### ###
=$C23*10^(-$D23*b*F$2) =$C23*10^(-$D23*b*G$2) ### ### ### ### ### ###
=$C24*10^(-$D24*b*F$2) =$C24*10^(-$D24*b*G$2) ### ### ### ### ### ###
=$C25*10^(-$D25*b*F$2) =$C25*10^(-$D25*b*G$2) ### ### ### ### ### ###
=$C26*10^(-$D26*b*F$2) =$C26*10^(-$D26*b*G$2) ### ### ### ### ### ###
=$C27*10^(-$D27*b*F$2) =$C27*10^(-$D27*b*G$2) ### ### ### ### ### ###
=$C28*10^(-$D28*b*F$2) =$C28*10^(-$D28*b*G$2) ### ### ### ### ### ###
=$C29*10^(-$D29*b*F$2) =$C29*10^(-$D29*b*G$2) ### ### ### ### ### ###
=$C30*10^(-$D30*b*F$2) =$C30*10^(-$D30*b*G$2) ### ### ### ### ### ###
=$C31*10^(-$D31*b*F$2) =$C31*10^(-$D31*b*G$2) ### ### ### ### ### ###
=$C32*10^(-$D32*b*F$2) =$C32*10^(-$D32*b*G$2) ### ### ### ### ### ###
=$C33*10^(-$D33*b*F$2) =$C33*10^(-$D33*b*G$2) ### ### ### ### ### ###
=$C34*10^(-$D34*b*F$2) =$C34*10^(-$D34*b*G$2) ### ### ### ### ### ###
=$C35*10^(-$D35*b*F$2) =$C35*10^(-$D35*b*G$2) ### ### ### ### ### ###
=$C36*10^(-$D36*b*F$2) =$C36*10^(-$D36*b*G$2) ### ### ### ### ### ###
=$C37*10^(-$D37*b*F$2) =$C37*10^(-$D37*b*G$2) ### ### ### ### ### ###

Seite 8
 =$C38*10^(-$D38*b*F$2) =$C38*10^(-$D38*b*G$2) ### ### ### ### ### ###
=$C39*10^(-$D39*b*F$2) =$C39*10^(-$D39*b*G$2) ### ### ### ### ### ###
=$C40*10^(-$D40*b*F$2) =$C40*10^(-$D40*b*G$2) ### ### ### ### ### ###
=$C41*10^(-$D41*b*F$2) =$C41*10^(-$D41*b*G$2) ### ### ### ### ### ###
=$C42*10^(-$D42*b*F$2) =$C42*10^(-$D42*b*G$2) ### ### ### ### ### ###
=$C43*10^(-$D43*b*F$2) =$C43*10^(-$D43*b*G$2) ### ### ### ### ### ###

### =SUM(G3:G43)+sl*$C$45 ### ### ### ### ### ###

### ### ### ### ### ### ### ###
### ### ### ### ### ### ### ###
### ### ### ### ### ### ### ###
### ### ### ### ### ### ### ###
### ### ### ### ### ### ### ###
=SUM(F46:F50) =SUM(G46:G50) ### ### ### ### ### ###
=F2*MAX($D3:$D43)*b =G2*MAX($D3:$D43)*b ### ### ### ### ### ###
=(F51-intercept)/slope =(G51-intercept)/slope ### ### ### ### ### ###
=F2-F53 =G2-G53 =H2-H53 =I2-I53 =J2-J53 =K2-K53 =L2-L53 =M2-M53
=F54/MAX(F2:O2) =G54/MAX(G2:P2) ### ### ### ### ### ###
slope= =SLOPE(E$51:N$51,E$2:N$2)
Intercept= =INTERCEPT(E$51:N$51,E$2:N$2)
in Izero and I, respectively (if “Photo” is checked under “Random Noise”)
### ### ### ### ### ### ### ###
### ### ### ### ### ### ### ###

e in Izero and I, respectively (if “Detector” is checked under “Random Noise”)

### ### ### ### ### ### ### ###
### ### ### ### ### ### ### ###

Seite 9
=Sheet1.H15
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###

Seite 10
 ###
###
###
###
###
###

###
###
###
###
###
###
###
###
###
=N2-N53
###

###
###

###
###

Seite 11