AN EXCEL MACRO FOR TRANSFORMED AND WEIGHTED AVERAGING

An Excel macro is presented for averaging spreadsheet data. The macro has several special features: (1) The data are weighted by the inverse variance of each datum to decrease the contribution-of noisy outliers. (2) There is a provision for a power or a log transform of the data before averaging. The rationale for transforming the data before averaging is discussed. (3) The output includes the average value, its standard error, and the reduced chi-square that measures the goodness of fit. (4) The standard error is corrected by a heterogeneity factor based on the reduced chi-square. The averaging of data is rarely done properly, and the intent of this article is to clarify the issues and provide a tool that allows researchers to improve their averaging techniques.