Katsumi WAKABAYASHI, Ph. D.
Prof. Emer. Gunma University
Technical Consultant, Shibayagi Co., Ltd.
In usual step for calculation of the assay value of ELISA is to draw a standard curve, absorbance on Y-axis against concentration on X-axis, then to estimate assay value from the absorbance of the sample.
EXCEL is really an excellent tool, however, it does not give X value from Y. so, the usual standard curve by EXCEL is not useful for assay value calculation.
I suggest a method to calculate assay value by using a reverse standard curve where absorbance on X and concentration on Y. The procedure will be shown step by step.
As in ELISA, the standard curve is nearly linear and excellent fitness is easily obtained by logarithmic transformation of both absorbance and concentration, the method starts from logarithmic transformation of the data.
Procedure of calculation step-by-step with an example of our insulin assay data Input of data in EXCEL spread sheet.
Standard points of rat insulin: 0, 0.1, 0.25, 0.5, 1.0, 2.5, 5.0, and 10.0 ng/ml
First make up a table for standard concentration and absorbance as shown below. The example shown here is a duplicate assay, and as TMB is used as chromogenic substrate, we measured absorbance at 450nm. If possible, as absorbance, difference of absorbance at 450nm and 620nm is preferable. Subtract the mean of blank absorption from each mean absorbance to make ΔBlank.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Insulin | Abs.450(Δ620)nm | Abs.450(Δ620)nm | Mean | ΔBlank |
| 2 | 10 | 2.316 | 2.214 | 2.265 | 2.233 |
| 3 | 5 | 1.312 | 1.227 | 1.270 | 1.238 |
| 4 | 2.5 | 0.614 | 0.641 | 0.628 | 0.596 |
| 5 | 1 | 0.217 | 0.209 | 0.213 | 0.181 |
| 6 | 0.5 | 0.112 | 0.108 | 0.110 | 0.078 |
| 7 | 0.25 | 0.064 | 0.061 | 0.063 | 0.031 |
| 8 | 0.1 | 0.045 | 0.044 | 0.045 | 0.013 |
| 9 | 0 | 0.031 | 0.032 | 0.032 |
Add two columns for logarithmic transformation.
For transformation, natural logarithm is more convenient.
In B2 cell, write” ＝LN(A2) “ (do not include quotation marks), the logarithmic value will appear in B2, and using fill-handle, transform other concentrations into (B3-B8)
Then transform ΔBlk (F column) in the same way to fill column G.
The result will be:
By using this table , prepare “a reverse standard curve for calculation” with following steps.
Preparation of reversed graph and regression equation
１．Click “Graph wizard” in tool bar.
２．Choose “scatter diagram”
３．Choose default, i.e.” plotting only” then next.
４．Indicate the column of “Ln(conc)” (in our example, B2-B8 as date area.
Click” column” as date series
５．Click “series” and choose data area of X.
６．Indicate the column of Ln(⊿Blank) (in our example G2-G8) as date area of X, then “next.”
７．Click “Title and label” tag, and write the graph title, names of X and Y axis. Click “legend” tag and uncheck “show legend”
８．Click ”finish” to show a graph on the data sheet.
As the graph appears as above is not good looking, so we should move X and Y axis.
To move X axis, double click the figure on X-axis to show X-axis setting window, and at “scale” tag uncheck the automatic checking of “intersection with Y”, and write the intersection wanted (in our example,” -5”, then OK. Y-axis is move in the similar way to X-axis. Write “-3” in our case.
Then the graph appears as shown below.
Now let’s get regression equation
９．Click one data point on the graph to make color change, and click “graph” in the task bar, then choose “Add regression curve”
１０．Choose “multimember regression”, and set the order “3”. Click “option” tag, and check “show equation” and “show R-2”., then “OK”.
Now, we get the reverse standard curve with equation and R2. From R2, we can estimate fitness of the curve.
The R2 value, 0.9998, obtained indicates that the fitness of the third order regression curve in our example seems to be excellent.
Let’s check fitness of the equation by calculation the assay value from standard data.
Using the equation we can calculate the assay value of samples and also fitness of the regression equation to the standard curve for each standard point. Before calculation of sample assay values, I recommend to check the fitness.
In order to examine fitness, we add two columns next to the column Ln(ΔBlk) (columns H and I). For column H, we will fill calculated logarithmic concentrations, i.e. Cal.Ln(conc), then we tranform them to normal value, and write them to the column I as Calc.conc. We will explain the step using our example.
To the cell H2, copy a part of the third order equation shown in graph , by inserting * and replacing x for G2 like;
=0.0222＊G2^3‐0.1324＊G2^2+0.997＊G2+1.4003 and push Enter key. Then 2.298192 will be written into H2 cell. The again point H2 to activate H2, and drag the fill handle of the cell until H8 to calculate all the standard data. The next step is to write “＝EXP(H2)” to the I2, and push Enter key. Then the value 9.956164 will be written in I2. After activation of the cell I2, drag the fill handle until I8. Then all the logarithmic concentration will be transformed into normal value.
You can compare those value in the column I with those in column A, you can examine the fitness.
The results of our example calculation are shown in the table below.
Calculation of sample assay values
Prepare a table of [sample absorbance-blank absorbance] for each well, and transform them into logarithmic values. Then fill the logarithmic assay values in the columns “LN(AV)1” and “LN(AV)2,” using the equation as in the fitness examination. Then fill the columns “AV1” and “AV2” with the assay values transformed from the logarithmic assay values. It would be convenient to calculate mean assay values, SD, and CV as shown in the example table below. (AV: assay value)
In our example, results with 10 samples are shown. More samples can be treated.
Preparation of a template for calculation
It would be convenient to prepare a template for ELISA calculation and store the file. What you should do is only to take out the file and fill the table with absorbance of standard and samples, and store the results table with a new file name. Duplicate assay is intended. The figures 1and 2 indicate well1 and well2, respectively.
Preparation of a template for standard curve.
Procedure
First, prepare a table in EXCEL．Let us call the cells A-1~H-8.
Cells in line 1 are used only for identification. Just write names of column as are shown in the model table.
In the cell C2, write “=LN(A2)”
In the cell E2 write “=(C2+D2)/2
Leave F2 untouched. To the cell F2 write “=E2-average absorbance of blank” after assay.
In the cell G2 write “=LN(F2)”
Leave the H2 untouched. (After assay write the equation of reversed regression curve)
In the cell I2 write “=EXP(H2)”
Then you will get a table as shown below.
Store the template and use a copy.
b. Activate B2 cell and drag the fill handle until B8 to complete transformation.
c. Write pairs of absorbance of wells in columns C and D.
d. Point E2 to activate, and drag fill handle until E8 to obtain means.
e. Write “=E2-blank absorbance(calculated above)” in F2, then drag fill handle to F8 for ΔBlank.
f. Activate G2, then drag the fill handle until G8 to obtain logarithm of ΔBlank.
g. Get the reversed regression curve as stated above.
h. Write the equation of 3rd order regression curve in H2 as described above, then click.
i. Activate H2, and drag the fill handle until H8.
j. Point I2, then drag I2 fill handle until I8.
k. Compare the assay values in the column I with the concentrations in A for fitness.
The equation written in H2 can be used for sample calculation, by copying.
Preparation of a template for sample calcualtion
First, prepare a table in EXCEL．Let us call the cells A-1~N11 (The template uses only A-1~N2).
Cells in line 1 are used only for identification. Just write as are shown in the model table.
Ab: absorbance of sample before subtraction of blank absorbance
ΔBk: absorbance of samples subtracted blank absorbance
LN( ): natural logarithm of ΔBk
Cal:natural logarithm of calculated sample assay value
Av: Assay value of sample, transformed to normal number
Mean: acerage of well 1 and well 2, SD: standard deviation, CV: Coefficient of variation
Procedure
In the cells in the column A, write sample number.
The cell D2 is left empty until use. After assay write “=B2-Blank”
Blank: mean blank absorbance value
The cell E2 is left empty until use.
In the cell F2, write “=LN(D2)”
Leave the cell H2 untouched. (After assay write the regression equation)
The cell I2 is left untouched
In the cell J2, write “=EXP(H2)”
In the cell L2 write “=(J2+K2)/2
After assay fill M2 with SD using function STDEV
In the cell N2, write “=M2/L2*100”
(Do not include quotation marks in writing.)
Then you will get a template table as shown below.
Store the template until use, and use it after making a copy.
How to use the template for sample calculation
After preparation of this template table, store the file. After an assay, take out the file and once store with proper naming. then input the first pair of absorbance in the cells B2 and C2, and “=D2-average blank absorbance” in D2, and regression equation in H2, as shown in the previous page. In this case, By dragging of the fill handle of D2 to E2, the function will be copied to E2, and the cell numbers are changed automatically. The situation is the same with G2, I2, and K2. The results of calculation appear in those cell of the line 2. By dragging each fill handle down to the last cell, results of calculation will appear when each pair of sample absorbance are input. The calculation will be completed after the input of absorbance of the last sample.
Number of samples is not limited.
Download Full Article How to calculate ELISA assay values by EXCEL
