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 28.1
edited by Mengting Qiu
on 2024/09/02 17:23
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -49,14 +49,14 @@
49 49  When x=8 mA, y=2.5 meters
50 50  
51 51  
52 -A more general formular:
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,87 @@
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 (% style="color:#4f81bd" %)**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 +* Write (% style="color:#4f81bd" %)**Intercept formula**(%%) in the formula bar according to the original x and y value statistics table.
117 +
118 +[[image:image-20240902164156-9.png||height="525" width="488"]]
119 +
120 +* Write (% style="color:#4f81bd" %)**CORREL's squared formula**(%%) in the formula bar based on the original x and y statistics table.
121 +
122 +The CORREL function returns "R", so we have to square it to compute "R squared".
123 +
124 +[[image:image-20240902164503-11.png||height="523" width="743"]]
125 +
126 +* These values match those shown in the chart.
127 +
128 +[[image:image-20240902164910-12.png||height="420" width="897"]]
129 +
130 +
131 +=== Step 3: Set up formulas to quickly calculate X and Y values. ===
132 +
133 +
134 +* The equation of the best fitting line is "Y-value = SLOPE * x-value + INTERCEPT", so the solution of "y-value" is obtained by multiplying X value and SLOPE, and then INTERCEPT is added.
135 +
136 +A simple formula table for calculating Y value automatically with input X value is obtained.
137 +
138 +[[image:image-20240902171627-15.png||height="440" width="754"]]
139 +
140 +
141 +* The X value based on the Y value is solved by subtracting INTERCEPT from the Y value and dividing the result by SLOPE: "x-value = (y-value-intercept)/SLOPE".
142 +
143 +A simple formula table for automatically calculating X value based on the input Y value is obtained.
144 +
145 +[[image:image-20240902171909-16.png||height="604" width="756"]]
146 +
147 +
74 74  = 3. Case examples =
75 75  
76 76  
... ... @@ -94,8 +94,12 @@
94 94  
95 95  (% style="color:red" %)**Notice for Linear Calibrate:**
96 96  
97 -1. k(Slope) is very important, We can measure more points to calculate the most accuracy k.
98 -1. Make sure the mapping is linear, and choose two calibrate points as "far" as possible.
171 +(% style="color:red" %)**1. k(Slope) is very important, We can measure more points to calculate the most accuracy k. **
99 99  
173 +(% style="color:red" %)**2. Make sure the mapping is linear, and choose two calibrate points as "far" as possible.**
100 100  
175 +
176 +
177 +
178 +
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
image-20240902171200-13.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +64.5 KB
Content
image-20240902171417-14.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +36.4 KB
Content
image-20240902171627-15.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +37.9 KB
Content
image-20240902171909-16.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +54.6 KB
Content