Difference between RMSE of Curve fitter and calculated RMSE in MS Excel

Dear colleagues,

I have used Curve fitter app for regression. The app calculates RMSE which is different than calculated by myself in MS Excel.

My calculation in MS Excel for RMSE is about 8 and I use the formula on the right.

I have tried to upload *.sfit file, but it is forbidden. SO I'm uploading only the excel file.

[Mike Croucher]

When I looked at your Excel file, the RMSE appears to be 10.85173 rather than 8. Can you confirm please?

[Torsten]

Cell I74 says 8,924285.

[Mike Croucher]

Ack! While messing around with Excel I had changed one of the data points. I accidently changed H12 to 213.10. No idea how!

Indeed, when I download the Excel file again, I get 8.924285. Thanks

[Boyan]

Yes, it is not about 8. It is exactly 8,92.

But Matlab calculates 3,4754.

@Mike Croucher have explained below, that this difference is because of the way of calculating RMSE by Matlab (depending further of the way of reggression) and "my" formula for RMSE above.

Answer 1

[Mike Croucher]

First, I wanted to confirm your manual calculation of RMSE using the formula you gave but using MATLAB instead of Excel. Here's my working

X = repmat([5,10,15,20,25,30,40,50,60],[9,1]);
Y = repmat([1;2;3;4;5;10;20;50;100],[1,9]);

Data = [213.10	157.80	127.60	107.10	91.40	74.40	56.70	38.10	28.80;
258.70	199.90	164.70	138.90	120.10	103.50	81.70	60.60	51.70;
288.40	225.30	186.20	157.00	136.30	119.30	95.40	73.40	63.90;
310.60	243.60	201.10	169.50	147.40	129.90	104.60	82.30	72.00;
328.60	257.90	212.50	179.00	155.80	137.80	111.60	88.90	78.00;
389.00	302.80	247.20	207.40	180.70	160.60	131.70	109.00	94.90;
456.70	348.90	280.60	234.20	204.10	181.00	149.90	127.80	109.60;
559.00	411.30	323.00	267.50	232.70	204.70	171.30	151.20	126.20;
647.40	459.80	353.80	291.10	252.70	220.40	185.80	167.60	136.90;];

% This is the fit function you defined in Excel
function z = manualFit(x,y)
a = 3630.9;
b = 13.1793;
c = 254.702;
d = -0.6094;

z = a./(x+b) + c.*x.^d.*log(y);
end

% This is the RMSE you got in Excel
yourFit = manualFit(X,Y);
N = numel(Data);
RMSE_Excel = sqrt(sum( (Data-yourFit).^2./N,'all'))

RMSE_Excel = 8.9243

This is the same as your calculation in Excel so we have the same starting point.

Next, I used Curve fitting app to find a fit to your data with 'Robust' turned off.

We can plug those numbers in and see what we get using your formula

% This is the fit function found by Curve Fitting App
function z = curveFitterFit(x,y)
a = 3504.2;
b = 12.97;
c = 244.2884;
d = -0.5909;

z = a./(x+b) + c.*x.^d.*log(y);
end

% This is the RMSE we get for that function using your definition of RMSE
autoFit = curveFitterFit(X,Y);
RMSE_matlab = sqrt(sum( (Data-autoFit).^2./N,'all'))

RMSE_matlab = 8.3406

They are different! This is because MATLAB is using a different definition of RMSE as described in Evaluating Goodness of Fit

Instead of dividing by the number of points N, it divides by the residual degrees of freedom. The residual degrees of freedom is defined as the number of response values n minus the number of fitted coefficients m estimated from the response values. That is,

% This is the RMSE we get for that function using MATLAB's definition of RMSE
autoFit = curveFitterFit(X,Y);
v  = N-4;
RMSE_matlab = sqrt(sum( (Data-autoFit).^2./v,'all'))

RMSE_matlab = 8.5545

This matches the output from the curve fitting app so we are again good and understand what's going on.

Now, it seems that you set the method to 'Robust' and set the Robust method to 'LAR'. When I did that, I got the following results in Curve Fiter

The fitted paramters are different from above as is the RMSE. This is expected because we are now minimising "Least Absolute Residuals" instead of "Least squares". The way to compute RMSE for a LAR fit is different from the formula you posted, just as MATLAB's standard method of computing RMSE is different from your formula as I showed above.

The function computes a robust estimate of the error standard deviation and calls this RMSE. A reference for the robust estimate of the error standard deviation is DuMouchel, W.H., and F.L. O'Brien (1989), "Integrating a robust option into a multiple regression computing environment," Computer Science and Statistics: Proceedings of the 21st Symposium on the Interface, American Statistical Association.

[Boyan]

Thank you Mike,

Very, very complete answer.

Answer 2

[Cris LaPierre]

In Excel you are calculating RMSE using all residuals.

In MATLAB you are calculating RMSE using Least Absolute Residuals. There is no closed-form solution for this.

You can read more here: Introduction to Least-Squares Fitting