Skip to Content
Last modified by Mengting Qiu on 2024/09/02 17:23
From version 11.1
edited by Mengting Qiu
on 2024/09/02 16:12
Change comment: Uploaded new attachment "image-20240902161202-3.png", version {1}
To version 22.1
edited by Mengting Qiu
on 2024/09/02 16:52
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -51,12 +51,12 @@
51 51  
52 52  A more general formular:
53 53  
54 -(% style="color:#4f81bd" %)**Y=(y2-y1)/(x2-x1)* (x-x1) + y1**
54 +(% style="color:blue" %)**Y=(y2-y1)/(x2-x1)* (x-x1) + y1**
55 55  
56 56  
57 57  Calibration Curve Schematic：
58 58  
59 -[[image:image-20240902114541-1.png||height="479" width="851"]]
59 +[[image:image-20240902114541-1.png||height="336" width="596"]]
60 60  
61 61  
62 62  == 2.2 Performing linear calibration curves in Excel ==
... ... @@ -64,13 +64,82 @@
64 64  
65 65  In addition, we can also perform calibration curves in Excel and directly obtain linear equations by statistics of X and Y values.
66 66  
67 +Citing the same example above, (% style="color:#4f81bd" %)**X**(%%) and (% style="color:#4f81bd" %)**Y**(%%).
67 67  
68 68  
70 +=== Step 1: Create chart ===
69 69  
70 70  
73 +A simple spreadsheet with two columns: X values and Y values.
71 71  
75 +[[image:image-20240902160516-1.png||height="404" width="561"]]
72 72  
77 +* Start by selecting the data you want to plot in the chart.
78 +* First, select the X-Value column cell, then press the Ctrl key, and finally click the Y-value column cell.
73 73  
80 +[[image:image-20240902160755-2.png||height="394" width="562"]]
81 +
82 +* Go to the "(% style="color:#4f81bd" %)**Insert**(%%)" TAB, navigate to the "(% style="color:#4f81bd" %)**Chart**(%%)" menu, and then select the first option in the "(% style="color:#4f81bd" %)**Scatter**(%%)" drop-down list.
83 +* A chart will appear with the data points in the two columns.
84 +
85 +[[image:image-20240902161202-3.png||height="453" width="824"]]
86 +
87 +* Right-click on one of the blue dots and select the (% style="color:#4f81bd" %)**"Add Trendline" **(%%)option.
88 +
89 +[[image:image-20240902161711-4.png||height="490" width="681"]]
90 +
91 +* A straight line will appear on the chart.
92 +
93 +On the right side of the screen, the Format Trendline menu will appear. Check the boxes next to (% style="color:#4f81bd" %)**"Show formulas on chart" **(%%)and (% style="color:#4f81bd" %)**"Show R-squared values on chart"**(%%).
94 +
95 +The R-squared value is a statistic that tells you how well the line fits the data. The best R-squared value is **1.000**, which means that every data point touches the line.
96 +
97 +Because the ideal data example is used, the R-squared value in this case is 1.
98 +
99 +As the difference between the data points and the line increases, the R-squared value decreases, with **0.000** being the lowest possible value.
100 +
101 +The equation is of the form (% style="color:blue" %)**"y = kx + b"**(%%),(% style="color:blue" %)** **(%%)where (% style="color:blue" %)**k**(%%) is the slope and (% style="color:blue" %)**b**(%%) is the y-intercept of the line.
102 +
103 +[[image:image-20240902161857-5.png||height="559" width="1103"]]
104 +
105 +* Calibration is complete. The user can customize the chart by editing the title and adding the axis title.
106 +
107 +[[image:image-20240902163527-7.png||height="349" width="656"]]
108 +
109 +
110 +=== Step 2: Calculate the line equation and R-squared statistic ===
111 +
112 +* Write the Slope formula in the formula bar according to the original x and y values statistics table.
113 +
114 +[[image:image-20240902164104-8.png||height="503" width="492"]]
115 +
116 +
117 +* Write Intercept formula in the formula bar according to the original x and y value statistics table.
118 +
119 +[[image:image-20240902164156-9.png||height="525" width="488"]]
120 +
121 +
122 +* Write Correl's squared formula in the formula bar based on the original x and y statistics table.
123 +
124 +The CORREL function returns "R", so we have to square it to compute "R squared".
125 +
126 +[[image:image-20240902164503-11.png||height="523" width="743"]]
127 +
128 +
129 +* These values match those shown in the chart.
130 +
131 +[[image:image-20240902164910-12.png||height="420" width="897"]]
132 +
133 +
134 +=== Step 3: Set up formulas to quickly calculate X and Y values. ===
135 +
136 +
137 +
138 +
139 +
140 +
141 +
142 +
74 74  = 3. Case examples =
75 75  
76 76  
... ... @@ -97,5 +97,4 @@
97 97  1. k(Slope) is very important, We can measure more points to calculate the most accuracy k.
98 98  1. Make sure the mapping is linear, and choose two calibrate points as "far" as possible.
99 99  
100 -
101 101  
image-20240902161711-4.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +66.9 KB
Content
image-20240902161857-5.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +48.1 KB
Content
image-20240902163114-6.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +59.7 KB
Content
image-20240902163527-7.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +25.8 KB
Content
image-20240902164104-8.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +27.2 KB
Content
image-20240902164156-9.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +24.4 KB
Content
image-20240902164500-10.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +34.4 KB
Content
image-20240902164503-11.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +34.4 KB
Content
image-20240902164910-12.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +33.2 KB
Content