Although all the statistical analyses described in this website can be done with standard Excel capabilities, it is often easier to use the supplemental functions and data analysis tools provided in the Real Statistics Resource Pack.
The functions provided in the Real Statistics Resource Pack are summarized in Real Statistics Functions. Here we briefly review the available supplemental data analysis tools.
Accessing Real Statistics Data Analysis Tools
You can access the Real Statistics data analysis tools by pressing Ctrl-m or via the Add-Ins ribbon (as described in Accessing Real Statistics Tools). One of two dialog boxes will appear which lists all the available supplemental data analysis tools (see Figure 1 and 2).
Figure 1 – Original Real Statistics data analysis tools main menu
Figure 2 – Real Statistics multipage data analysis tools main menu
You next choose one of the data analysis tools from this list. A dialog box will now appear which is similar to that presented in Figure 2 of Excel’s Data Analysis Tools, as described in detail in Using Real Statistics Data Analysis Tools.
The following are the currently supported Real Statistics data analysis tools.
List of Real Statistics Data Analysis Tools
Descriptive Statistics and Normality, includes:
- Descriptive Statistics
- Box Plot
- Box Plot with Outliers
- QQ Plot
- Shapiro Wilks
- Outliers and Missing Data
- Grubbs’ Test
Frequency Table – creates a frequency table from data
- Create a frequency table and histogram
- Create a frequency table and histogram based on bins
- Create raw data from frequency table
- Descriptive statistics for frequency table
Diversity Indices
Chi-square Test for Independence – tests m × n contingency tables for independence
T Tests and Non-parametric equivalents, includes:
- T Test: One Sample
- T Test: Two Independent Samples
- T Test: Two Paired Samples
- Mann-Whitney Test for Independent Samples
- Wilcoxon Signed-Rank Test for Paired Samples
- Wilcoxon Signed-Rank Test for One Sample
Analysis of Variance, see the following for details
- Single Factor Anova
- Two Factor Anova
- Three Factor Anova
- Nested Anova
- One Repeated Factor Anova
- Mixed Repeated Factor Anova
- Randomized Complete Block Design
- Split-plot Design
- Latin squares Design
- MANOVA
- ANCOVA
- Two Factor Anova Follow-up
Single Factor Anova, supports Excel and standard formats and includes:
- Anova: Single Factor
- Kruskal-Wallis non-parametric test
- Brown-Forsythe F* test
- Welch’s test
- Levene’s Test for homogeneity of variances
- Anova follow-up tests
- Kruskal-Wallis follow-up tests
- Random Factor
Two Factor Anova, supports Excel and standard formats and includes:
- Anova model, used for balanced models
- Regression model, used for unbalanced models
- Descriptive statistics, including interaction tables
- Two Random Factors
- Two Mixed Factors
- Reformat Data (convert data into one-way Anova format)
- Scheirer-Ray-Hare Test
- Gage R&R
Three Factor Anova, supports standard formats by row and by column and includes:
- Anova model, used for balanced models
- Regression model, used for unbalanced models
- Descriptive statistics
- Regression without replication
Nested Anova
One Factor Repeated Measures Anova, includes
- Anova: Repeated Measures (one within subjects factor)
- Contrasts
Two Mixed Repeated Measures Anova, includes
- Anova: Repeated Measures (one within subjects and one between subjects factor)
Randomized Complete Block Design, supports Excel and standard formats and includes:
Split-plot Design, supports Excel and standard formats and includes:
- Anova: Split-plot Design (RCBD whole plots version)
- Anova: Split-plot Design (CRD whole plots version)
- Contrasts for Split-plot Design
- Tukey’s HSD for Split-plot Design
Latin Squares Design, supports Excel and standard formats and includes:
Analysis of Covariance (Ancova)
Single Factor MANOVA
- Single Factor MANOVA, includes:
- Significance Testing – significant test for MANOVA omnibus test
- Sum of Squares and Cross Product (SSCP) Matrices – displays T, H and E matrices
- Group and Total Means – displays group and total mean vectors
- Group Covariance Matrices – displays group covariance matrices
- Multivariate Outliers – test for outliers using Mahalanobis distance
- Box’s Test – test for homogeneity of covariance matrices
- Multiple ANOVA – follow up MANOVA with ANOVA on all dependent variables
- Contrasts – follow up MANOVA with multivariate and univariate contrasts
- Repeated Measures – using MANOVA for two factor repeated measures analyses
Two Factor Anova Follow-up
- Simple Effect (rows, columns)
- Contrasts (rows, columns, interaction)
- Tukey HSD (rows, columns, interaction)
Correlation (one-sample), includes
Polychoric Correlation
- Polychoric Correlation (including tetrachoric correlation)
Regression, includes
- Linear Regression – multiple linear regression, see below for details
- Weighted Linear Regression – esp. useful to address heteroscedasticity
- Exponential Regression – nonlinear exponential regression
- Logistic Regression – binary logistic regression, see below for details
- Multinomial Logistic Regression, see below for details
Linear Regression, includes
- Regression Analysis – performs multiple linear regression analysis
- Regression without a Constant – assumes the intercept is zero
- Robust Standard Errors – select none or type HC0 through HC3
- Residuals and Cook’s D – creates a table which includes Cook’s Distance and DFFITS, for use in identifying outliers and influencers in regression
- Durbin-Watson Test – checks for autocorrelation
- Stepwise Regression
Other types of Regression
- Exponential Regression – nonlinear version
- Polynomial Regression
- Least Absolute Deviation (LAD) Regression
- Deming Regression
Logistic and Probit Regression – performs binary logistic and probit regression
- Logistic Regression using Solver
- Logistic Regression using Newton’s Method
- Classification Table
- ROC Curve
- Probit Regression
Multinomial Logistic Regression
- Multinomial Logistic Regression (for raw and summary data)
Time Series Analysis, see the following for details
- Basic Forecasting Methods
- ARMA Modeling and Forecasting
- ARMA Options
- ARIMA Modeling and Forecasting
- Testing
Basic Time Series Forecasting
- Basic Time Series Forecasting, includes
- Simple Moving Average
- Weighted Moving Average
- Simple Exponential Smoothing
- Holt’s Linear Trend
- Holt-Winters
ARIMA Models and Forecasting
Time Series Testing
- Time Series Testing Tool, includes
- ACF and ACVF
- PACF
- Bartlett’s, Box-Pierce, Ljung-Box tests
- ADF test
- Augmented ADF test
Survival Analysis, includes
Reliability, includes
- Internal Consistency Reliability, see below
- Interrater Reliability, see below
- Item analysis (item difficulty, item discrimination and point-serial correlation)
Internal Consistency Reliability
Interrater Reliability
- Cohen’s kappa
- Cohen’s weighted kappa
- Fleiss’s kappa
- Intraclass correlation
- Kendall’s W (with or without correction for ties)
- Krippendorff’s Alpha
- Gwet’s AC2
- Bland-Altman
Non-parametric Tests
- Non-parametric Tests, includes
- Friedman Test
- Cochran’s Q Test (raw data and summary data options)
- One Sample Runs Test
- Two Sample Runs Test
- Moods’ Median Test
- Mann-Whitney Test
- Wilcoxon Signed-Ranks Test
- Kruskal-Wallis Test
Goodness of Fit Testing
- Goodness of Fit Analysis, includes
- Two Sample Kolmogorov-Smirnov Test (raw data and summary options)
- Anderson-Darling Test
- Chi-square Goodness of Fit Test
Resampling
- Bootstrapping (p-values, histograms and confidence intervals) for one sample, two sample, paired sample, multiple group (ANOVA) and correlation coefficient tests
- Randomization (p-values and histograms) for two sample, paired sample, multiple group (ANOVA) and correlation coefficient tests
Step Chart
ROC Curve and Classification Table
Solve Set of Linear Equations, includes
- Solve Set of Linear Equations – outputs the solution only
- Perform Gaussian Elimination – shows result of Gaussian elimination
Extract Columns from Data Range – allows you to create a new data range by selecting certain columns from an existing data range
Reformatting a Data Range
- Reformat Data Range, includes:
- Reshape – reproduce input data in a range of a different shape
- Reverse – reverse the order of the input data range
- Sort – reproduce the data in ascending sorted order
- Sort without duplicates – reproduce the data in sorted order removing duplicates
- Shuffle – randomly permute the data (selection without replacement)
- Randomize – randomly select data from data range (with replacement)
- Remove blank cells – remove all empty cells
- Remove non-numeric cells – remove all cells with non-numeric data
- Remove error cells – replace any error cells by empty cells
- Copy exact formulas – copy a range to a new location without changing the cell addresses
Reformatting a Data Range by Rows
- Sort by rows – sort rows based on the value of the cell in a selected column
- Remove rows with empty cell(s) – remove any row which has an empty cell
- Remove rows with non-num cell(s) – remove any row with a non-numeric cell
Matrix Operations
- Matrix Operations, includes:
- Transpose – transposes a matrix
- Correlation Matrix – creates the correlation matrix for the specified data range
- Covariance Matrix – creates the population or sample covariance matrix for the specified data range
- Inverse – inverts a square matrix
- Diagonal – creates a diagonal matrix with specified diagonal
- Eigenvalues and Eigenvectors – finds the eigenvalues and eigenvectors of a symmetric square matrix
- Eigenpairs (non-sym) – finds the real eigenvalues and eigenvectors of any square matrix, including non-symmetric matrices
- QR Factorization – finds orthogonal matrix Q and upper triangular R where input matrix = QR
- Schur Factorization – finds orthogonal matrix Q and upper triangular T such that input matrix = QTQT.
- SVD Factorization – finds the Singular Value Decomposition (SVD) consisting of diagonal matrix D and orthogonal matrices U and V where input matrix = UDVT.
- Spectral Factorization – finds the Spectral Decomposition of a symmetric input matrix which is equal to QDQT where D is a diagonal matrix and Q is orthogonal
- Identity Matrix – creates an identity matrix of the specified size
- Empty Box – creates an empty matrix of the specified size and shape
Multiple Imputation (MI)
- Frequency and patterns of missing data
- Simple imputation
- FCS imputation
- Combining multiple imputations
- Multiple regression using MI
Full Information Maximum Likelihood (FIML)
- Pairwise frequency of missing data
- FIML using Solver
- Multiple regression using FIML
- Data analysis tool
Multivariate Analyses, see the following for details:
- Hotelling’s T-square Test
- MANOVA (see above listing)
- Factor Analysis
- Cluster Analysis
- Discriminant Analysis
Hotelling’s T-square Test
- Example of data analysis tool
- One sample
- Two paired samples
- Two independent samples with equal covariance matrices
- Two independent samples with unequal covariance matrices
- Repeated Measures – using one sample T-square test for one factor repeated measures analyses
Factor Analysis – includes correlation matrix, eigenvalues/vectors, factor matrix, Varimax rotation, factor scores
Cluster Analysis
- Cluster Analysis – k-means++ test
- Jenks Natural Breaks
Discriminant Analysis
Statistical Power and Sample Size Requirements
This data analysis tool supports the following tests:
- One-sample normal test
- Two-sample normal test
- One-sample and paired-sample t test
- Two-sample t test
- One-sample binomial test
- One-sample correlation test
- One-sample variance test
- Two-sample variance test
- Chi-square test (goodness of fit and independence tests)
- One-way ANOVA
- Multiple regression
- Cronbach’s alpha test
- Intraclass correlation ICC(1,1)
You can click on any of the data analysis tools listed above (or the listed options within these tools) to get additional information about that tool (or option).
Thank you very much for making this suite available to us. Appreciate your efforts (and the “educational” spirit) in crafting a value added option to a popular workhorse for the masses.
Respected sir,
I want find the “relationship between emotional intelligence and math abilities of secondary school students.”
how can i compare emotional intelligence componets &with what type of math abilities get little confusion. piz help in this regards.
Mulla,
You haven’t provided enough information for me to respond in any detail. You could start by exploring the correlation coefficient. Perhaps a t test would be useful.
Charles
I have downloaded the resource pack for Mac many thanks
The control M produces the real stats menu but it does no contain a survival analysis option.. I am trying to perform cox proportional hazards model – why can’t i access survival analysis from menu??
Nisha,
Sorry, but the Mac version of the software doesn’t yet support these capabilities. Currently, they are only supported in the Windows version.
Charles
Dear Charles,
Thanks for an amazing tool. Any idea when you might release a Mac version with survival analysis?
Akash
Akash,
I have the release ready, but I don’t have a Mac to test it on. As soon as I can get a Mac to test on, I will issue the new release.
Charles
That is great news Charles. Here’s hoping you can get to a Mac soon!
Charles I have a Mac by the way. Anyway I can help out remotely with testing your new release?
Akash,
Thanks. I appreciate that. If I can find a Mac to create the release on, then I would be happy to send you the release software to test.
Charles
Thanks for a great tool. I am a NOVICE stat guy just trying to crunch some numbers for fantasy football. The Jenks Natural Breaks algorithm is great for determining the cutoff value for tiers, or what I think you call classes. However, it relies on me telling it how many classes (k) that I want. Is there a way to determine the optimal k? Is it always the case that the larger k is, the better the fit would be? So I guess I would be looking for the lowest k that does not significantly improve with a larger k. If that makes sense, how could I statistically calculate that. I am looking at less than 100 samples, so the number of permutations will not be impossible. Thanks again.
Mike,
Yes, the larger the number of classes the better the fit would be. In fact, the best fit would occur when each data element is in its own class, but this defeats the purpose of the whole exercise.
I don’t know of a statistical test which determines whether a higher number of classes no longer yields a significant difference. With a small sample, you can experiment until you see that a higher number of classes doesn’t seem to matter much. Usually after about 6 classes, the Jenks Natural Breaks algorithm slows down considerably (assuming that you want to test all possible partitions) and so this may be the determining factor in how many classes to consider.
Charles
Dear Professor,
First of all thanks for this great add-in.
But, who write a Tesis, they have to put/write (at least in APA) the p-value.
But, in some tables, special Contrast, Tukey HSD (I use .. )etc, you only put the “sig” and “no” or “yes”.
It is not possible to put the real p-value in next version?
Thank you, very, very much!
José
Jose,
That is a reasonable request. I am in the process of testing the next release and I will try to add this information. In the meantime, for Tukey HSD, you can use the QDIST function to get the p-value.
Charles
Hi Charles, thanks for your Excel add-in.
This is very beneficial for me as I am not fortunate enough to have Minitab or SPSS in my official system.
I have installed Real Statistics as per instruction given on site. but when I am trying to use a function by pressing Ctrl +m and selecting the function and data an error comes “Compile error in hidden module: frmMatrix ”
This is happening with each and every function.
please help.
windows 7 ultimate, 32- bit OS, =VER() turns out to 4.5 EXCEL 2007
I am sorry, but I don’t know what is causing this problem. I am using Real Statistics with Excel 2007 without any problems, but other Excel 2007 users have run into problems similar to yours. I suggest that you look at the comments on the following webpage to see what others have suggested.
http://www.real-statistics.com/free-download/real-statistics-resource-pack/real-statistics-resource-pack-excel-2007/
In particular, the following suggestion may be useful
“Copy the downloaded file to /Microsoft Office/Office12/Library/Analysis , then restart excel. Then install in normal way.”
Charles
Merci beaucoup
Came across this hidden gem . I’m a data scientist and mostly play around with R. But this is helpful for my colleagues who are not comfortable with R and not fortunate enough to have Minitab or SPSS in their machine. At least its a stop-gap arrangement where we could run statistical tests locally before communicating findings.
Thanks
Hi,
I am seeing in other posts there is a cluster analysis included but when I download this version I do not see it? Do I need an upgrade?
Nicole
Nicole,
You should find it under the Multivariate menu choice. If you don’t see it there then you will need to upgrade.
Charles
Hi Charles, thanks for your Excel add-in.
The option of logistic regression does not appear in the dialog box .
I have the 2013 Excel.
Thank you again.
Choose the Regression option. On the next dialog box that appears, choose the Logistic Regression option.
Charles
I downloaded your add-in and I playing with it now. I have a question, not directly related to the add-in – Excel contains the following functions SUMSQ, SUMX2MY2, SUMX2PY2 and SUMXMY2, what practical applications do these functions have – where would on use them?
Thanks,
Shane
Shane,
SUMSQ is used very often in statistics: to compute variance, Durbin-Watson, regression, length of vector, etc.
SUMXMY2 is used for calculating Durbin-Watson and in K-means cluster analysis
Since SUMX2MY2(R1, R2) = SUMSQ(R1) – SUMSQ(R2) and SUMX2PY2(R1, R2) = SUMSQ(R1) + SUMSQ(R2), they seem much less useful to me.
Charles
Hi Charles. Thanks for the availability of this excel add-in. I have attempted to run the SlopeTest function (Excel 2013) and the output is only on cell, the Std Err value. There are no other cells reported. Any suggestions? Thanks
Lee,
The SlopeTest function is an array function; i.e. the output requires more than one cell. You need to follow the instructions on the webpage Array Functions and Formulas.
Charles.
Thanks for the quick reply Charles. An oversight on my part. Everything is now working perfectly. Sorry for the inconvenience.
Charles, the toolbar opens, but whe I try to run any function it returns the same error as if I access trough the supplement.
Charles, I get the software version: 3.8.1 Excel 2007. When I try to access the realstat tool bar it returns a system error &H80004005 (-21476467229). Non specified error and then a compilation error in the hidden module frmlnput.
In fact, I can use the functions directly (and in fact I do use them this way), but turns the process unproductive.
Andre
Andre,
What happens if you press Crtl-m instead of accessing the realstats toolbar?
Charles
Andre,
This seems to be a known bug in Excel. Most people are using the Excel 2007 version of the Real Statistics software without any problems. A few people have reported some problems getting access to the software, but as far as I am aware this the first time this bug has been reported.
If you google “Excel error message &H80004005 (-21476467229)” you will see information about the bug. The webpage http://peltiertech.com/unspecified-painfully-frustrating-error/ seems to summarize the situation well.
Since the bug is only indirectly related to the Real Statistics software, if at all, I don’t know what to suggest. Please let know whether you were using a previous release of the software, in which I can send a copy of that version to you.
Charles
Hi, I’m using a brazilian portughese version of excell and installing the realstat2007 version. But when I try to access the resource pack it returns a non specified error and indicates that it may probably be due to a problem running the hidden module. Do you have any clue on how to solve this? Thanks a lot.
Andre
Andre,
I don’t know why you are getting this error message. What do you get when you enter the formula =VER()? In the error message does it state which hidden module is causing the problem?
Charles
I followed your directions and got to the dialogue box in fig. 2 but each time I send a command it shows a notice; “Compile error in hidden module: Analysis”. How can I solve this problem Sir. Dear Charles, VER() gives 3.6.2. My excel is 2007.
Dear Charles, VER() gives 3.1.2. I have installed the 2010 version on another pc, windows server 2008 r2, and it seem to work. Thank you.
Thank You for your great job. I get “Compile error in hidden module: Analysis” while using the “ctrl-m” part of the module. My excel is 2007, SO is Windows XP.
Marco
Marco,
What do you see when you enter the formula =VER() in any cell?
Charles
I followed your directions and got to the dialogue box in fig. 2 but each time I send a command it shows a notice; “Compile error in hidden module: Analysis”. How can I solve this problem Sir.
Charles
Which version of Excel are you using? Which version of the Real Statistics Resource Pack are you using? (you can find this out by enterring the formula =VER() in any cell).
Charles
It is a very useful website. How do I calculate the CORNALPHA using the Resource Pack? I do not think there is an option if and when I press Ctrl+m
Regards
Usman,
Just enter the function CRONALPHA in any cell in the worksheet, just like any other worksheet function (AVERAGE, MAX, STDEV, etc.). You can also access the Cronbach alpha test by pressing Ctrl-m and choosing the Descriptive Statistics option.
Charles
First, I would like to thank you for this project. It is truly wonderful that someone would offer functionality that appears to be this good for free.
I say, “appears to be,” because I have yet to actually be able to run the software. I am on a Mac and continue to encounter the error message of “data library not found” (or something to that effect) and the prompting for a password which was spoken about by Donald McDonald in his post elsewhere on your web site. I wish I had read the post that your add-in was not yet ready for Mac before I willy-nilly proceeded with the install, but, alas, such was not to be the case.
I tried to delete the add-in through the Visual Basic editor, but it prompts me for a password. So, for now, I will just live with the occasional annoyance of my spreadsheets thinking I am trying to invoke your analysis macro when I’m really not.
In any event, I would again like to thank you for your work on this. I found my way to your web site because I am a long-time user of Microsoft’s Analysis Toolpak at work and fully expected it to be present when I purchased Microsoft Office’s new “annual family license” ($100 per year for five machines) for my small army of Macs-users at home. Regrettably, as you’re probably aware, Microsoft has pulled the Analysis Toolpak from the Mac version (as far as I know, the Windows annual license was unaffected) and is referring those who are affected by this to something called StatsPlus LE.
StatsPlus LE is ok, but does not have the integration that the Analysis Toolpak (or your add-in) possesses: when you run the add-in, it opens a new workbook with the results and leaves you wondering how to link back to your original data so you can make changes to the data and re-run the analysis without going through the whole routine again. To get full integration of StatsPlus LE that the Analysis Toolpak provides (as well as some of the more useful statistical routines), you are required to buy a $200 license.
One more time, thank you for your work on this. I will certainly be looking forward to the Mac version.
Hi Curt,
Unfortunately the Real Statistics Resource Pack currently only works in the Microsoft environment. It is not yet available for the Mac. The main problem I have is that I don’t have a Mac myself on which to create the Mac version. I am still looking for someone to give me access to a Mac so that I create and test a Mac version.
Charles
Update: The software now is available for use on the Mac
I have downloaded the latest version of the program and when I go to bring it into Excel as an add-in I am prompted for a RealStats password. Please advise.
Jaclyn
Jaclyn,
I am not sure what is causing this problem in your case, but the most common cause is trying to open the file containing the software pack. In this case you will indeed be requested to provide a password, but you don’t need to open the file to use the software. It is important that you tell Excel that the software should be considered to be an “add-in”. This is done as described on the website, namely:
1. Move the Resource Pack file to where you want it located on your computer (see our recommendation on the website). Caution: once you install the resource pack at a particular location it will be more difficult to move it later.
2. Select Office Button > Excel Options > Add-Ins in Excel 2007 or File > Help|Options > Add-Ins in Excel 2010/2013, and click on the Go button at the bottom of the window.
3. Check the Realstats option on the dialog box that appears and click the OK button.
4. If this option doesn’t appear, click on Browse to find and choose the realstats.xlam file. Then complete step 2 as described above.
Once you complete these steps you should be able to use the software without providing a password.
If this doesn’t work, please let me know and I will try to help you.
Charles