+ Reply to Thread
Results 1 to 7 of 7

Thread: [Excel] - My Excel Template for Robust Statistics

  1. #1
    TS Contributor
    Points: 21,403, Level: 92
    Level completed: 6%, Points required for next Level: 947
    Awards:
    Downloads
    gianmarco's Avatar
    Location
    Italy
    Posts
    1,027
    Thanks
    155
    Thanked 203 Times in 149 Posts

    [Excel] - My Excel Template for Robust Statistics




    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....le/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.

  2. #2
    TS Contributor
    Points: 21,403, Level: 92
    Level completed: 6%, Points required for next Level: 947
    Awards:
    Downloads
    gianmarco's Avatar
    Location
    Italy
    Posts
    1,027
    Thanks
    155
    Thanked 203 Times in 149 Posts

    error fixed

    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

  3. #3
    Points: 1,648, Level: 23
    Level completed: 48%, Points required for next Level: 52

    Location
    Sydney
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The download is corrupted. I'd tried through winrar. Could you please upload zip?

  4. #4
    Points: 1,648, Level: 23
    Level completed: 48%, Points required for next Level: 52

    Location
    Sydney
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sorry it was my Internet. It is working great and I just need to find the way how to use it.

    Great efforts.


  5. #5
    TS Contributor
    Points: 21,403, Level: 92
    Level completed: 6%, Points required for next Level: 947
    Awards:
    Downloads
    gianmarco's Avatar
    Location
    Italy
    Posts
    1,027
    Thanks
    155
    Thanked 203 Times in 149 Posts
    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

  6. #6
    TS Contributor
    Points: 21,403, Level: 92
    Level completed: 6%, Points required for next Level: 947
    Awards:
    Downloads
    gianmarco's Avatar
    Location
    Italy
    Posts
    1,027
    Thanks
    155
    Thanked 203 Times in 149 Posts

    My Excel Template for Robust Statistics-update

    Hi

    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 .

    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 , 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).



    Thanks for your attention.

    Best regards,
    Gm

  7. #7
    TS Contributor
    Points: 21,403, Level: 92
    Level completed: 6%, Points required for next Level: 947
    Awards:
    Downloads
    gianmarco's Avatar
    Location
    Italy
    Posts
    1,027
    Thanks
    155
    Thanked 203 Times in 149 Posts

    Re: My Excel Template for Robust Statistics-update


    Hi!!
    As I wrote about my Template for chi-square test, users can leave a message in the Guestbook of my personal Website.

    Regards
    Gm

+ Reply to Thread

           




Similar Threads

  1. [Excel] - Template for Chi-Square / Fisher test
    By gianmarco in forum Other Software
    Replies: 3
    Last Post: 12-19-2011, 07:03 AM
  2. Replies: 2
    Last Post: 10-28-2010, 04:14 AM
  3. Replies: 2
    Last Post: 08-12-2008, 11:46 AM
  4. Excel or Mathcad template
    By Karen Platt in forum Statistics
    Replies: 0
    Last Post: 05-13-2008, 02:51 PM
  5. Excel TTEST() vs Excel DataAnalysis\t-test
    By fiducial in forum Statistics
    Replies: 5
    Last Post: 02-06-2008, 12:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts






Advertise on Talk Stats