GoF

Several goodness-of-fit (GoF) model indexes for Excel

Author: Christopher Teh Boon Sung, Uni. Putra Malaysia

Contact: [email protected]; www.christopherteh.com

Initial Release: June 6, 2019

Updated: Oct. 25, 2021

MIT -licensed:

• Free to use, copy, share, and modify
• Give credit to the developer somewhere in your software code or documentation

List of GoF indexes (and the names of their functions in brackets):

1. Mean Absolute Error (fit_mae)
2. Normalized Mean Absolute Error (fit_nmae)
3. Mean Bias Error (P-O) (fit_mbe)
4. Mean Absolute Percentage Error (MAPE) (fit_mape)
5. Mean Bias Percentage Error (MBPE) (fit_mbpe)
6. Normalized Mean Bias Error (P-O) (fit_nmbe)
7. Median Absolute Percentage Error (MAPE) (fit_mdape)
8. Root Mean Square Error (fit_rmse)
9. Original Index of Agreement (fit_d)
10. New (Refined) Index of Agreement (fit_dr)
11. RMSE to Standard Deviation Ratio (fit_rsr)
12. Nash-Sutcliffe Efficiency (fit_nse)
13. Normalized mean square error (fit_nmse)
14. Fractional bias (fit_fb)
15. Coefficient of Efficiency (fit_coe)
16. Revised Mielke Index (fit_mielke)
17. Persistence Index (fit_pi)
18. Akaike Information Criterion (AIC) (fit_aic)
19. Bayesian Information Criterion (BIC) (fit_bic)
20. Theil's U2 Coefficient of Inequality (UII) (fit_theilu2)
21. Kling-Gupta Efficiency (KGE) (fit_kge)

Note:

• All indexes will ignore cells that are blank (empty), hidden, or contain #N/A error
• Missing values in cells should be left blank or use the function NA() to indicate an error value in that cell

Installation:

1. Open the Visual Basic Editor in Excel (via the Developer tab)
2. Insert this file (Gof.bas) as one of the modules in your workbook (see: https://youtu.be/ett0WiTfQuI).

Usage:

• All GoF functions start with fit_<<name>> where <<name>> is the abbreviated name of the GoF index. For instance, the mean bias error (MBE) index function is fit_mbe, and the normalized mean absolute error (NMAE) function is fit_nmae. See the GoF module for the other functions.
• To use the MBE function, type in =fit_mbe(A1:A10, B1:B10), where A1:A10 is the range of cells containing the observed (measured) values and B1::B10 the estimated (predicted) values. Other GoF functions are used in the same way, except for AIC and BIC functions.
• To use the AIC function, type in =fit_aic(A1:A10, B1:B10, 3, True) where A1:A10 and B1:B10 contain the observed and estimated values, respectively; the third argument (value 3) is the number of model parameters plus one (e.g., simple linear regression equation y = mx + c has 3 model parameters: m, c, and plus one); and the last parameter is True (by default) for second-order AIC. Set to False for first order AIC (use for large samples).
• The BIC function is used in the same way as the AIC function, except the BIC function is fit_bic and it has no fourth parameter, e.g., =fit_bic(A1:A10, B1:B10, 3).