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

64 ビュー (過去 30 日間)
Boyan
Boyan 2025 年 1 月 13 日 20:29
編集済み: Boyan 2025 年 1 月 18 日 12:27
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
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
Boyan
Boyan 2025 年 1 月 18 日 10:08
編集済み: Boyan 2025 年 1 月 18 日 12:27
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.

サインインしてコメントする。

採用された回答

Mike Croucher
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'))
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.
  1 件のコメント
Boyan
Boyan 2025 年 1 月 18 日 12:22
Thank you Mike,
Very, very complete answer.

サインインしてコメントする。

その他の回答 (1 件)

Cris LaPierre
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.

カテゴリ

Help Center および File ExchangeGet Started with Curve Fitting Toolbox についてさらに検索

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by