Changes for page How to do with Linear Calibration?
Last modified by Mengting Qiu on 2024/09/02 17:23
From version 6.1
edited by Mengting Qiu
on 2024/09/02 13:49
on 2024/09/02 13:49
Change comment:
There is no comment for this version
To version 27.1
edited by Mengting Qiu
on 2024/09/02 17:19
on 2024/09/02 17:19
Change comment:
There is no comment for this version
Summary
-
Page properties (1 modified, 0 added, 0 removed)
-
Attachments (0 modified, 16 added, 0 removed)
- image-20240902160516-1.png
- image-20240902160755-2.png
- image-20240902161202-3.png
- image-20240902161711-4.png
- image-20240902161857-5.png
- image-20240902163114-6.png
- image-20240902163527-7.png
- image-20240902164104-8.png
- image-20240902164156-9.png
- image-20240902164500-10.png
- image-20240902164503-11.png
- image-20240902164910-12.png
- image-20240902171200-13.png
- image-20240902171417-14.png
- image-20240902171627-15.png
- image-20240902171909-16.png
Details
- Page properties
-
- Content
-
... ... @@ -16,74 +16,159 @@ 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 18 19 - **Example:**we have a waterlevelprobe,themeasurementrangeis 0 ~~ 10 meters,andthe outputis 4~~20mA, this meansthe when the water levelis 0 meter, the outputis 4mA, whenthe waterlevelis 10 meters, the output is 20mA.19 +== 2.1 Solve the linear relationship manually == 20 20 21 + 22 +(% 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. 23 + 21 21 We can make a coordinate axis as below: 22 22 23 -1. (% style="color:b lue" %)**Y**(%%) axis is the real value, from 0~~10 meters24 -1. (% style="color:b lue" %)**X**(%%) axis is the probe output , from 4~~20mA26 +1. (% style="color:#4f81bd" %)**Y**(%%) axis is the real value, from 0~~10 meters 27 +1. (% style="color:#4f81bd" %)**X**(%%) axis is the probe output , from 4~~20mA 25 25 26 26 We use two points to make the linear line: Point1(x1,y1) = (4,0), Point2(x2,y2)= (20,10). 27 27 28 -Since the reading is linear, all (% style="color:b lue" %)**probe output**(%%) and (% style="color:blue" %)**real value**(%%) is on this line, so we can calculate the real value by probe output in two steps:31 +Since the reading is linear, all (% style="color:#4f81bd" %)**probe output**(%%) and (% style="color:#4f81bd" %)**real value**(%%) is on this line, so we can calculate the real value by probe output in two steps: 29 29 30 -* **Step1:** Get (% style="color:b lue" %)**k(Slope)**(%%) for the line:33 +* **Step1:** Get (% style="color:#4f81bd" %)**realk(Slope)**(%%) for the line: 31 31 32 32 k=(y2-y1)/(x2-x1) = (10-0)/(20-4) =10/16= 0.625 33 33 34 -* **Step2:** Get (% style="color:b lue" %)**kreal value(y)**(%%):37 +* **Step2:** Get (% style="color:#4f81bd" %)**real value( y )**(%%): 35 35 36 -k=(y-y1)/(x-x1) 39 + k =(y-y1)/(x-x1) 37 37 38 -~-~-> y = k*(x-x1)+y1 = 0.625 * (x-4) + 0. 41 + ~-~-> y = k*(x-x1)+y1 = 0.625 * (x-4) + 0. 39 39 40 - ~= 0.625 * (x-4) 43 + ~= 0.625 * (x-4) 41 41 42 - When x=12mA,y=5meters45 +Thus, we can introduce x-values to the already obtained equations to derive the corresponding y-values: 43 43 44 -When x= 8mA, y=2.5 meters47 +When x=12 mA , y=5 meters 45 45 49 +When x=8 mA, y=2.5 meters 46 46 47 -A more general formular: 48 48 49 - Y=(y2-y1)/(x2-x1)*(x-x1)+y152 +**A more general formular:** 50 50 54 +(% style="color:blue" %)**Y=(y2-y1)/(x2-x1)* (x-x1) + y1** 51 51 52 -[[image:image-20240902114541-1.png||height="492" width="874"]] 53 53 57 +Calibration Curve Schematic: 54 54 59 +[[image:image-20240902114541-1.png||height="336" width="596"]] 55 55 56 -= 3. Case examples = 57 57 62 +== 2.2 Performing linear calibration curves in Excel == 58 58 59 -Some case for example which we can use Linear Calibration: 60 60 65 +In addition, we can also perform calibration curves in Excel and directly obtain linear equations by statistics of X and Y values. 61 61 62 -C ase1: CalibrateMicrowaveRadarReadling.67 +Citing the same example above, (% style="color:#4f81bd" %)**X**(%%) and (% style="color:#4f81bd" %)**Y**(%%). 63 63 64 -The microwave radar reading might effect by the OUM(Object Under Measured), but the reading still linear. In this case, we can measure the closest(x1,y1) and the farthest point(x2,y2). Where the x is reading in platform, y is the real value. And use above method to calibrate. 65 65 70 +=== Step 1: Create chart === 66 66 67 -Case 2: Calibrate the Soil EC base on SE01 soil sensor raw EC reading. 68 68 69 - TheSE01 probe iscalibrated viamineralsoil. The reading for other soil will bedifferentby still in linear, Inthiscase, we can measuretwopoints(x1,y1)and (x2,y2). Where the x isreading of Raw EC, y is the real EC for the soil. Anduseabove method to calibrate73 +A simple spreadsheet with two columns: X values and Y values. 70 70 75 +[[image:image-20240902160516-1.png||height="404" width="561"]] 71 71 72 -Case 3: use water level probe to calibrate for oil. 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 74 - Oil has different density vs water, but we can still use the immersion type water level pressure sensor toget the oil level. In this case, we canmeasuretwo points (x1,y1) and (x2,y2).Where the x is readingof water level, y is theoil level. And use above methodtocalibrate80 +[[image:image-20240902160755-2.png||height="394" width="562"]] 75 75 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. 76 76 77 - Noticefor LinearCalibrate:85 +[[image:image-20240902161202-3.png||height="453" width="824"]] 78 78 79 -1. k(Slope) is very important, We can measure more points to calculate the most accuracy k. 80 -1. Make sure the mapping is linear, and choose two calibrate points as “far” as possible. 87 +* Right-click on one of the blue dots and select the (% style="color:#4f81bd" %)**"Add Trendline" **(%%)option. 81 81 89 +[[image:image-20240902161711-4.png||height="490" width="681"]] 82 82 91 +* A straight line will appear on the chart. 83 83 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"**(%%). 84 84 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. 85 85 97 +Because the ideal data example is used, the R-squared value in this case is 1. 86 86 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. 87 87 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. 88 88 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 + 148 += 3. Case examples = 149 + 150 + 151 +Some case for example which we can use Linear Calibration: 152 + 153 + 154 +(% style="color:blue" %)**Case 1: Calibrate Microwave Radar Readling.** 155 + 156 +The microwave radar reading might effect by the OUM(Object Under Measured), but the reading still linear. In this case, we can measure the closest(x1,y1) and the farthest point(x2,y2). Where the x is reading in platform, y is the real value. And use above method to calibrate. 157 + 158 + 159 +(% style="color:blue" %)**Case 2: Calibrate the Soil EC base on SE01 soil sensor raw EC reading.** 160 + 161 +The SE01 probe is calibrated via mineral soil. The reading for other soil will be different by still in linear, In this case, we can measure two points (x1,y1) and (x2,y2). Where the x is reading of Raw EC, y is the real EC for the soil. And use above method to calibrate 162 + 163 + 164 +(% style="color:blue" %)**Case 3: use water level probe to calibrate for oil.** 165 + 166 +Oil has different density vs water, but we can still use the immersion type water level pressure sensor to get the oil level. In this case, we can measure two points (x1,y1) and (x2,y2). Where the x is reading of water level, y is the oil level. And use above method to calibrate 167 + 168 + 169 +(% style="color:red" %)**Notice for Linear Calibrate:** 170 + 171 +1. k(Slope) is very important, We can measure more points to calculate the most accuracy k. 172 +1. Make sure the mapping is linear, and choose two calibrate points as "far" as possible. 173 + 89 89
- 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