$\begingroup$

It looks to me like it might potentially make sense to account for the way the error changes with the second (calibrated) val as well as with speed. If you're only interested in the relationship with speed, it looks like linear regression would be a good first suggestion.

First, let's look at your data; this is every variable against every other variable, usually called either a scatterplot matrix or a pairs plot:

Features in the pairs plot:

The plot of err vs spd , bottom left, outlined in reddish brown. This is the same plot you show and indicates a clear downward trend. This is what prompts me to suggest linear regression of some kind as a 'first attempt'.

Consider the plot of val1 against val2 ( val2 is the calibrated-instrument measurement, the one you want your field measured-val to be close to), the plot near the middle, outlined in red. It has a nice straight-looking relationship, as you'd hope. There's a 'flat' part where a whole bunch of different calibrated values near 32 were recorded as '32'; this potentially suggests some kind of issue with the device (though it could also be mere chance combined with the rounding that's there).

Consider the err vs val2 , the plot in blue. Values to the left of $\text{val2}\leq 32$ (roughly) look very noisy there and then there's less noise but a distinct upward trend after that. You can see a similar effect in the plot of err vs val1 (the purple-outlined plot in the last row; this is potentially useful, because val2 isn't available normally).

Edit: there's also a suggestion of nonlinearity in the relationship of the two 'val' variables with spd (unsurprisingly).

---

Linear regression (first model):

This fits a straight line. You could just fit a straight line of err against spd , but this misses a bunch of potential value in the information you have in the other variables.

The usual form of linear regression is via least squares, and this is probably easiest to do, but given the possibility of outliers you mention, you may want to consider a more robust alternative.

Here's a linear regression done in R. Note that my err variable isn't multiplied by 100, so if you did it with your numbers, your coefficient would be 100 times as big:

mdl1 <- lm(err~spd,calib) summary(mdl1) Call: lm(formula = err ~ spd, data = calib) Residuals: Min 1Q Median 3Q Max -0.12761 -0.02540 0.01683 0.04742 0.09650 Coefficients: Estimate Std. Error t value Pr(>|t|) (Intercept) 0.3242267 0.0865953 3.744 0.001485 ** spd -0.0039833 0.0009619 -4.141 0.000613 *** --- Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1 Residual standard error: 0.06867 on 18 degrees of freedom Multiple R-squared: 0.4879, Adjusted R-squared: 0.4594 F-statistic: 17.15 on 1 and 18 DF, p-value: 0.0006134

The residual standard error, 0.0687 suggests that the typical error (let alone the maximum error!), even after adjustment is still a good deal larger than you'd like.

Looking at the diagnostics, the model doesn't look too bad. There are low outliers, and the variation looks bigger near the middle, with a decrease at larger fitted values.

---

The relationship between val2 and val1

Before we plow on with that, though, we should investigate the relationship between the field measured val and the calibrated one. The pairs plot above doesn't make it obvious, but I could discern a slight bend... and given the pattern in the err vs val2 plot, I decided to take a closer look:

Clearly there's a 'bend' here! You shouldn't ignore that; it's a source of a fair amount of your error.

It's clearer still if you look at the raw error:

Edit: This effect remains if you remove the clump of values at 32 --

The problem is, of course that in the field you don't know val2 and you need val1 to estimate the 'gold standard' value -- yet val1 is the 'noisy' measurement. This is sometimes called an 'inverse regression' problem.

It's probably worth your time to google on this; or rather on one of these:

inverse regression -sliced --- (because this avoids some not-so-relevant hits)

inverse regression calibration

Your problem is a calibration problem (you're trying to figure out how to estimate val2 , the 'accurate' measure, from val1 , the noisy one), but you have the advantage of a second predictor, which seems to add useful information to the raw calibration curve.

---

A second, simple regression model

With the decrease in variability against fitted in the first model, and the fact that the actual problem is to try to 'back out' val2 from this, consider modelling the actual error rather than the percentage error.

Further, while this model will ignore that 'bend' in the data, it will consider val2 in the model for val1 . Well, we will fit two models in order to see if there's value in extending it:

summary(m1) Call: lm(formula = I(val1 - val2) ~ spd, data = calib) Residuals: Min 1Q Median 3Q Max -4.8978 -1.3288 -0.3747 1.5850 4.8034 Coefficients: Estimate Std. Error t value Pr(>|t|) (Intercept) 13.35791 3.22609 4.141 0.000614 *** spd -0.16092 0.03583 -4.491 0.000283 *** --- Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1 Residual standard error: 2.558 on 18 degrees of freedom Multiple R-squared: 0.5284, Adjusted R-squared: 0.5022 F-statistic: 20.17 on 1 and 18 DF, p-value: 0.0002827

We can't really compare this model with the previous one, it's not on the same scale (however, we again see that spd is needed to 'adjust'). Now lets add val2:

summary(m2) Call: lm(formula = I(val1 - val2) ~ spd + val2, data = calib) Residuals: Min 1Q Median 3Q Max -4.4331 -0.8054 0.0845 1.0342 3.4050 Coefficients: Estimate Std. Error t value Pr(>|t|) (Intercept) 10.55003 2.77761 3.798 0.00144 ** spd -0.17419 0.02954 -5.897 1.76e-05 *** val2 0.09427 0.02976 3.168 0.00562 ** --- Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1 Residual standard error: 2.088 on 17 degrees of freedom Multiple R-squared: 0.7034, Adjusted R-squared: 0.6686 F-statistic: 20.16 on 2 and 17 DF, p-value: 3.258e-05

We see that the largest and smallest residuals are smaller and that spd has a very similar effect to before. The residual standard error is about 20% smaller and the val2 effect is significant. That is, if we want to try to 'guess' val2 from spd and the noisy val1 , we should not ignore the linear relationship between val1 and val2 . The diagnostics (not shown) are better, though the presence of low outliers does suggest that a more robust method might get a little closer for the bulk of the data.

If you do this with the relative error instead, you don't see as much value in adding val2, but I'd still include it (and the model diagnostics aren't nearly as nice, because the size of the error really isn't constant on the percentage scale; it's much nearer to constant on the scale of actual error).

It doesn't get you down to 6% error, but that's mostly because the percentage errors for the low val s are always going to be large on the percentage scale, even though they're nice looked at as raw errors), but if you want to lower it further, you'll need more data in order to estimate a calibration curve (and your job will become more complex) - there's a suggestion of curvature with both val2 and spd; I'd probably look at natural spline regression if you went that direction.

Using Excel:

The linear regressions can be done in Excel, but some of the diagnostics won't be available, and if come to trying to calibrate a curve, it becomes very unwieldy. I think it's worth putting the data into R and learning the tools.

Some issues with sample splitting:

Take a look at some of the discussion here. In the links there, it's discussing the idea of splitting samples into either three or two groups instead of fitting a line, where the actual relationship is smooth (doesn't seem to have 'jumps'). If some of the terms aren't clear in the links, please ask.

Justification for low/high or tertiary splits in ANOVA

(However, if your example data is typical, you may actually have jumps. In that case, you might want to consider models that pick up such jumps without requiring you to identify them by hand - and you would want to account for the potential overfitting as well)