# Line Graph Template Blank by qyp20863

VIEWS: 144 PAGES: 8

• pg 1
```									Conc.    Ln(conc)      Abs 1      Abs 2       Mean       Dblank Ln(Dblank) alLn(conc)
C                  Fitness
#NUM!                                      0         0 #NUM!      #NUM!           #NUM!
#NUM!                                      0         0 #NUM!      #NUM!           #NUM!
#NUM!                                      0         0 #NUM!      #NUM!           #NUM!
#NUM!                                      0         0 #NUM!      #NUM!           #NUM!
#NUM!                                      0         0 #NUM!      #NUM!           #NUM!
#NUM!                                      0         0 #NUM!      #NUM!           #NUM!
#NUM!                                      0         0 #NUM!      #NUM!           #NUM!
Blank                                                0

How to use the template for the standard curve preparation for duplicate assay.

Using the data for standard solutions, first finish calculation until the column G.
Write blank absorbance. in cells C9 and D9.
a. In the column A write concentrations of standard solutions starting from the highest. Then cells
b. Write pairs of absorbance of wells in columns C and D.
c. Columns from E to G will be completed.
d. Preparation of reversed graph and regression equation
In EXCEL 2000-2003
1. Click “Graph wizard” in tool bar.
2. Choose “scatter diagram”
3. Choose default, i.e.” plotting only” then next.
4. Indicate the column of “Ln(conc)” (in our example, B2-B8 as date area.
Click” column” as date series
5. Click “series” and choose data area of X.
6. Indicate the column of Ln(⊿Blank) (in our example G2-G8) as date area of X, then “next.”
7. Click “Title and label” tag, and write the graph title, names of X and Y axis. Click “legend” tag and un
8. Click ”finish” to show a graph on the data sheet.
9. Click one data point on the graph to make color change, and click “graph” in the task bar, then choo
10. choose “multimember regression”, and set the order “3”. Click “option” tag, and check “show equa
Now, we get the reverse standard curve with equation and R2. From R2, we can estimate fitness of the curve
In case EXCEL 2007
1. Indicate B2-B8 and G2-G8, and click "Insert" tag. Then choose "Scatter diagram" to obtain a graph
Y:Ln(D Blank)ce).
2. Click "Design" in graph tool , then click "choice of data". Chenge X and Y series in "edit of legend".
Choose G2-G8 for X series, and B2-B8 fot Y series.
3. Click "Layout" tag. Choose regression curve, and choose 3rd order multimember regression
then "OK".
d. If the equation is y = ax3+bx2+cx+d , write the values of a, b, c and d in cells K11, K12, K13, K14. Then the the table
e. Compare the assay values in the column I with the concentrations in A for fitness.
f. Trueness is shown in column J.

Look the sheet "Calc" for sample calculation
Look the sheet "Calc" for sample calculation
Trueness
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!

a (coeff. X3)
b (coeff. X2)
c (coeff. X)
d (const)

hest. Then cells in B columnl will be filled with the logarithmic values.

date area of X, then “next.”
X and Y axis. Click “legend” tag and uncheck “show legend”

ck “option” tag, and check “show equation” and “show R-2”., then “OK”.
, we can estimate fitness of the curve.

"Scatter diagram" to obtain a graph with x: Ln(conc) and

nge X and Y series in "edit of legend".

order multimember regression. Check "show equation" and "show R-2",

11, K12, K13, K14. Then the the table will be completed.
No.          Abs 1     Abs 2       DBk1         DBk2       Ln(1)     Ln(2)       Cal. Ln 1 Cal. Ln 2 AsVal 1
1                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
2                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
3                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
4                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
5                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
6                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
7                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
8                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
9                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
10                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
11                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
12                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
13                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
14                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
15                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
16                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
17                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
18                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
19                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
20                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
21                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
22                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
23                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
24                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
25                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
26                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
27                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
28                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
29                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
30                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
31                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
32                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
33                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
34                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
35                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
36                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
37                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
38                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
39                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!
40                                  0          0    #NUM!     #NUM!       #NUM!     #NUM!     #NUM!

How to use the template for sample calculation.
First, write average of blank absorbace (E9 in the std Curve sheet) in P9, and copy a, b, c, d of the regression formula (K
then paste to P11-P14 cells.
Input the pairs of absorbance of all the samples into the columns B and C, then the first line will be completed.
Results of calculation will appear when you finish input of every pair.
AsVal 2    Mean       SD         CV
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!    Blank abs.
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!        a
#NUM!      #NUM!      #NUM!      #NUM!        b
#NUM!      #NUM!      #NUM!      #NUM!        c
#NUM!      #NUM!      #NUM!      #NUM!        d
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!
#NUM!      #NUM!      #NUM!      #NUM!

f the regression formula (K11-K14 in Std Curve sheet,

be completed.
Conc.          Ln(conc) Abs 1     Abs 2     Mean      Dblank           Ln(Dblank) CalLn(conc)
Fitness   Trueness
10    2.302585    2.316     2.214    2.265     2.2335          0.80357 2.299079 9.964999     99.64999
5    1.609438    1.312     1.227   1.2695      1.238         0.213497 1.617718 5.041571     100.8314
2.5    0.916291    0.614     0.641   0.6275      0.596         -0.51751 0.915131 2.497101     99.88406
1           0    0.217     0.209    0.213     0.1815          -1.7065 -0.02661 0.973745      97.3745
0.5    -0.69315    0.112     0.108      0.11    0.0785         -2.54466 -0.65005 0.522021     104.4041
0.25    -1.38629    0.064     0.061   0.0625      0.031         -3.47377 -1.41518 0.242881     97.15247
0.1    -2.30259    0.045     0.044   0.0445      0.013         -4.34281 -2.2973 0.10053       100.5303
0                0.031     0.032   0.0315

a
3                                                                                        b
y = 0.0233x3 + 0.1349x2 + 0.9974x + 1.3984                                          c
2
R² = 0.9998                                                         d

1

0

-1

-2

-3
-5      -4        -3        -2        -1      0       1       2
0.0233
0.1349
0.9974
1.3984

```
To top