FAQ: How to solve an implicit equation for the calculation of the detection limit with Excel
2 posters
Triskem :: Analytical chemistry / Chimie analytique / Analytische Chemie :: Sample preparation and measurement / Preparation des sources et mesure / Messprobenvorbereitung und Messung
Page 1 of 1
FAQ: How to solve an implicit equation for the calculation of the detection limit with Excel
When Bayesian statistical methods are used to specify the characteristic limits, one usually obtains an implicit equation for the calculation of the detection limit.
Example
Mathematical model of the evaluation / Modèle mathématique d'évaluation / Mathematisches Modell der Auswertung
Uncertainty / Incertitude / Unsicherheit
Decision threshold / Seuil de décision / Erkennungsgrenze
Detection limit / Limite de détection / Nachweisgrenze
This implicit equation can be solved by iteration. The iteration can be automatically executed using Microsoft® Excel. But first, the iteration option has to be switched on.
English version
French version
German version
Now, we can lay out an Excel table for above example.
We start with entering the input data.
Column A: Sample name
Column B: Calibration factor phi
Column C: Uncertainty of phi
Column D: Count rate
Column E: Background count rate
Column F: Measuring time
Column G: Background measuring time
Column H: Mathematical model of the evaluation
Column I: Uncertainty
Column J: Decision threshold
Here comes the tricky part:
Column K: Detection limit
Column L: Critical test
Finally, the table should look like this:
English version
French version
German version
I hope you find this hint helpful.
Please feel free to make any comments!
Example
Mathematical model of the evaluation / Modèle mathématique d'évaluation / Mathematisches Modell der Auswertung
Uncertainty / Incertitude / Unsicherheit
Decision threshold / Seuil de décision / Erkennungsgrenze
Detection limit / Limite de détection / Nachweisgrenze
This implicit equation can be solved by iteration. The iteration can be automatically executed using Microsoft® Excel. But first, the iteration option has to be switched on.
English version
- On the Tools menu, click Options, and then click the Calculation tab.
- Select the Iteration check box.
- To set the maximum number of times Microsoft® Excel will recalculate, type the number of iterations in the Maximum iterations box. The higher the number of iterations, the more time Excel needs to calculate a worksheet.
- To set the maximum amount of change you will accept between calculation results, type the amount in the Maximum change box. The smaller the number, the more accurate the result and the more time Excel needs to calculate a worksheet.1.
French version
- Dans le menu Outils, cliquez sur Options, puis sur l'onglet Calcul.
- Activez la case à cocher Itération.
- Pour définir le nombre maximal de fois où Microsoft® Excel recalcule, tapez le nombre d'itérations dans la zone Nb maximal d'itérations. Plus le nombre d'itérations est élevé, plus le temps nécessaire au calcul d'une feuille est important.
- Pour définir l'écart maximal entre les résultats du calcul, tapez le chiffre dans la zone Écart maximal. Plus la valeur est petite, plus le résultat est précis et plus le temps nécessaire au calcul d'une feuille est important.
German version
- Klicken Sie im Menü Extras auf Optionen und dann auf die Registerkarte Berechnung.
- Aktivieren Sie das Kontrollkästchen Iteration.
- Geben Sie im Feld Maximale Iterationszahl an, wie viele Iterationsschritte Microsoft® Excel maximal durchführen soll. Je höher die Zahl der Iterationsschritte ist, desto länger dauert die Berechnung des Arbeitsblattes.
- Geben Sie im Feld Maximale Änderung an, um welchen Betrag sich die Ergebnisse zweier aufeinander folgender Berechnungen höchstens unterscheiden dürfen. Je kleiner die Zahl ist, desto genauer wird das Ergebnis und desto länger dauert die Berechnung des Arbeitsblattes.
Now, we can lay out an Excel table for above example.
We start with entering the input data.
Column A: Sample name
Column B: Calibration factor phi
Column C: Uncertainty of phi
Column D: Count rate
Column E: Background count rate
Column F: Measuring time
Column G: Background measuring time
Column H: Mathematical model of the evaluation
- Code:
H2=B2*(D2-E2)
Column I: Uncertainty
- Code:
I2=B2*SQRT(C2^2/B2^2*(D2-E2)^2+D2/F2+E2/G2)
I2=B2*RACINE(C2^2/B2^2*(D2-E2)^2+D2/F2+E2/G2)
I2=B2*WURZEL(C2^2/B2^2*(D2-E2)^2+D2/F2+E2/G2)
Column J: Decision threshold
- Code:
J2=1.645*B2*SQRT(E2*(1/F2+1/G2))
J2=1.645*B2*RACINE(E2*(1/F2+1/G2))
J2=1.645*B2*WURZEL(E2*(1/F2+1/G2))
Here comes the tricky part:
Column K: Detection limit
- Code:
K2=J2+1.645*B2*SQRT(C2^2/B2^4*K2^2+(K2/B2+E2)/F2+E2/G2)
K2=J2+1.645*B2*RACINE(C2^2/B2^4*K2^2+(K2/B2+E2)/F2+E2/G2)
K2=J2+1.645*B2*WURZEL(C2^2/B2^4*K2^2+(K2/B2+E2)/F2+E2/G2)
Column L: Critical test
- Code:
L2=H2>J2
Finally, the table should look like this:
English version
French version
German version
I hope you find this hint helpful.
Please feel free to make any comments!
Sven- Number of posts : 8
Localisation : Offenbach, Germany
Registration date : 2008-07-16
Re: FAQ: How to solve an implicit equation for the calculation of the detection limit with Excel
Sven wrote:
I hope you find this hint helpful.
Please feel free to make any comments!
Great, better than sex.
Now I dont have any questions anymore. Thanks a lot.........
Andreas- Number of posts : 8
Registration date : 2008-07-16
Triskem :: Analytical chemistry / Chimie analytique / Analytische Chemie :: Sample preparation and measurement / Preparation des sources et mesure / Messprobenvorbereitung und Messung
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|