PDA

View Full Version : [Excel] - My Excel Template for Robust Statistics

gianmarco
09-10-2009, 05:03 AM
Hi All,
I was playing with the possibility to implement some statistical analyses in Excel with no macros, only formulas.
The project grew up, and the current version of my template has now the features listed at the bottom of this post.
If anyone is interested in it, iinformation are available at the following page of my personal web-page (at the bottom of the page):

http://xoomer.virgilio.it/gianmarco.alberti/index_file/Page395.htm

Regards,
GmA

Features:

‐Number of Samples handled (entered by user): 2

‐Operations on entered samples:
Automatically provided: sorted sample, Trimmed sample, Winsorized sample, random sample.

‐Descriptive statistics:
Number of observations, min., max., sum, range, arithmetic mean, variance, standard deviation, mean absolute
deviation, standard error, x% confidence interval for population mean, x% lower confidence limit, x% upper
confidence limit, 1 quartile, median, 3 quartile, midspread, median absolute deviation, x% confidence interval for
population median, x% lower confidence limit, x% upper confidence limit, Tukey's trimean, skewness, kurtosis.

‐Outliers detection:
Mean, Median, Inter Quartile Range methods.

‐Hypothesis Testing:
t‐Test for independent samples: t‐Test (for the original samples), “Robust” t‐Test (for Trimmed samples, using
trimmed estimators) (+ Bullet Graphs comparing 80, 95 and 99% Confidence Interval for population Mean).
Welch’t (for both original and trimmed samples).
F‐Test for difference in variance (for both original and Trimmed samples).
Midspread comparison: for both original and Trimmed samples.
Mann‐Whitney test (+ Bullet Graphs comparing 80, 95 and 99% Confidence Interval for population Median).
Kolmogorov‐Smirnov test (for both original and Trimmed samples) (+ plot of cumulative distribution of the samples
being compared).

‐Correlation:
Pearson’s r, with confidence interval for population’s r (for both original and Trimmed samples).
Spearman’s r (for both original and Trimmed samples).
Scattergrams of Sample 1 vs Sample 2, Trimmed Sample 1 vs Trimmed Sample 2 (+ regression equation).

‐Graphics:
Box‐plots for original, trimmed, winsorized and random samples.
Box‐plots for original and trimmed samples with indication of median, mean, 1 quartile, 3 quartile, smallest&largest
non‐outlier observations.
Histograms of frequency distribution for original, trimmed, winsorized and random samples.
Histograms of frequency and cumulative distribution for original, trimmed, winsorized and random samples.
Histograms for Confidence Range of population mean for original, trimmed, winsorized and random samples.
Back‐to‐back frequency distribution histograms for original, Trimmed and random samples.
Bullet graphs for comparing 80%, 95% and 99% Confidence Range for Population Mean (for original and trimmed
samples).
Bullet graphs for comparing 80%, 95% and 99% Confidence Range for Population Median (for original and trimmed
samples).
Plot of cumulative distribution of the samples being compared.
Scattergrams of correlation between Samples.

gianmarco
05-17-2010, 10:43 AM
Hi All.

For those who are interested in my Template, and for those who already downloaded it, I wish to inform that I have fixed an error in the calculation of Confidence Interval for the Median. Previous calculations, and consequently also the bullet-graphs for the median, were wrong.

Hoping that the Template will be of some interest,

Best Regards
Gianmarco

techamitdv
05-23-2010, 08:28 AM

techamitdv
05-23-2010, 08:35 AM
sorry it was my Internet. It is working great and I just need to find the way how to use it.

Great efforts.

:wave:

gianmarco
05-23-2010, 10:35 AM
Hi!

I hope it will be of some use.

The .rar file contains the user Guide written by me (sorry for any slip present in it).

Please note that the file can be slow on opening: it is normal, and opening time depends on computer's resource. It is due to the huge quantity of formulas embedded in the spreadsheet.

Further improvements are planned in the future (at least, Q-Q plot and normality test).

Best Regards,
Gm

gianmarco
11-21-2010, 03:10 AM
Hi :wave:

A new version of the Template is available (v. 3.5). In this new version I fixed an error in the calculation of the correlation between trimmed samples :p.

A scholar pointed me out that the correlation coefficient between trimmed samples was always very high because the samples were always put in descending order, so loosing their original paired matching.

After some speculation :confused:, and after reading literature (R.R. Wilcox, "Fundamentals of Modern Statistical Methods", 2001, pp. 189-190), I decided not to calculate correlation between trimmed samples, but between winsorized samples (for the reasons explained in that reference).
Besides, I relied on that reference for the calculation of the significance of the correlation coefficients in the particular case of the use of winsorized samples.
Needless to say, the winsorized samples (used for correlation) have now the observations handled in the proper order (matched pairs).