Difference between RMSE of Curve fitter and calculated RMSE in MS Excel
64 ビュー (過去 30 日間)
古いコメントを表示
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.
4 件のコメント
Mike Croucher
2025 年 1 月 14 日 12:53
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
採用された回答
Mike Croucher
2025 年 1 月 14 日 0:24
編集済み: Mike Croucher
2025 年 1 月 14 日 13:26
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'))
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'))
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'))
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.
その他の回答 (1 件)
Cris LaPierre
2025 年 1 月 13 日 22:16
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
0 件のコメント
参考
カテゴリ
Help Center および File Exchange で Get Started with Curve Fitting Toolbox についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!