Skip to Content
Last modified by Mengting Qiu on 2024/09/02 17:23
From version 7.1
edited by Mengting Qiu
on 2024/09/02 14:14
Change comment: There is no comment for this version
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
... ... @@ -15,6 +15,10 @@
15 15  
16 16  In some case, the measurement and real value are in different range, but they are both linear, we have to calculate the real value with the measurement we can use a simple Linear Calibration.
17 17  
18 +
19 +== 2.1 Solve the linear relationship manually ==
20 +
21 +
18 18  (% style="color:blue" %)**Example:**(%%) we have a water level probe, the measurement range is 0 ~~ 10 meters, and the output is 4~~20mA, this means the when the water level is 0 meter, the output is 4mA, when the water level is 10 meters, the output is 20mA.
19 19  
20 20  We can make a coordinate axis as below:
... ... @@ -45,17 +45,102 @@
45 45  When x=8 mA, y=2.5 meters
46 46  
47 47  
48 -A more general formular:
52 +**A more general formular:**
49 49  
50 -(% style="color:#4f81bd" %)**Y=(y2-y1)/(x2-x1)* (x-x1) + y1**
54 +(% style="color:blue" %)**Y=(y2-y1)/(x2-x1)* (x-x1) + y1**
51 51  
52 52  
53 53  Calibration Curve Schematic：
54 54  
55 -[[image:image-20240902114541-1.png||height="479" width="851"]]
59 +[[image:image-20240902114541-1.png||height="336" width="596"]]
56 56  
57 57  
62 +== 2.2 Performing linear calibration curves in Excel ==
58 58  
64 +
65 +In addition, we can also perform calibration curves in Excel and directly obtain linear equations by statistics of X and Y values.
66 +
67 +Citing the same example above, (% style="color:#4f81bd" %)**X**(%%) and (% style="color:#4f81bd" %)**Y**(%%).
68 +
69 +
70 +=== Step 1: Create chart ===
71 +
72 +
73 +A simple spreadsheet with two columns: X values and Y values.
74 +
75 +[[image:image-20240902160516-1.png||height="404" width="561"]]
76 +
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.
79 +
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 +
59 59  = 3. Case examples =
60 60  
61 61  
... ... @@ -79,9 +79,12 @@
79 79  
80 80  (% style="color:red" %)**Notice for Linear Calibrate:**
81 81  
82 -1. k(Slope) is very important, We can measure more points to calculate the most accuracy k.
83 -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. **
84 84  
173 +(% style="color:red" %)**2. Make sure the mapping is linear, and choose two calibrate points as "far" as possible.**
85 85  
86 86  
176 +
177 +
178 +
87 87  
image-20240902160516-1.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +31.2 KB
Content
image-20240902160755-2.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +29.0 KB
Content
image-20240902161202-3.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.ting
Size
... ... @@ -1,0 +1,1 @@
1 +74.0 KB
Content
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