Release 5.2

I am pleased to announce Release 5.2 of the Real Statistics Resource Pack. The new release is now available for free download at Download Resource Pack for Excel 2007, 2010, 2013 and 2016 (Windows version) environments.

The example files Examples Workbook Part 2 and Time Series Examples been updated for compatibility with Release 5.2.

The Real Statistics website will be updated over the course of the next few days to reflect the new capabilities in Release 5.2.

The following is a summary of the new features in Release 5.2.

ARIMA Enhancements

A new ARIMA_Coeff array function has been added which calculates the coefficients of an Arima(p, q, d) model, along with the standard errors of these coefficients and confidence intervals.

In addition, the ARIMA_Stats array function has been added which calculates various statistics (LL, SSE, MSE, AIC, etc.) for an Arima model.

Please join me in thanking Miloš Cipovic, who did a beautiful job of programming the algorithms for these new functions using the Levenberg-Marquardt method.

Minor changes

Used F_DIST_RT function in the Repeated Measures ANOVA data analysis tool to increase accuracy.

Corrected some tooltips on some dialog boxes

Added Nonparametric and TimeSeries function categories to make it easier to get information about more Real Statistics functions via the Insert Function fx capability.

The COCHRAN and QTEST formulas have been enhanced. A third argument has been added which allows you to specify that a continuity correction will be used in the case where there are only two variables (this is also the default), i.e. the case which is equal to McNemar’s test.

Posted in Announcement, New Release | Comments Off on Release 5.2

More about Release 5.1

In the description of the new Release 5.1 features, I forgot to mention something which a number of people have asked for, namely

Follow-up Analyses after Three Factor ANOVA

A new ANOVA Follow-up data analysis tool has been added. This tool allows the user to perform Contrast and Tukey HSD (or Tukey-Kramer) analyses after Three Factor ANOVA.

This tool can be used with both balanced and unbalanced models and can actually be employed after any type of ANOVA (not just Three Factor ANOVA) provided you have the appropriate descriptive data and the values of MSE and dfE created by any omnibus ANOVA data analysis tool.

Quick Update

Release 5.1 is now available for users of Excel 2007, 2010, 2013 and 2016 (Windows). All the examples files have been updated and good progress has been made updating the website for compatibility with Rel 5.1. In particular, the website now includes webpages on the new ANOVA Follow-up data analysis tool as well as 2^k Factorial Design and Correspondence Analysis.

I had originally planned to improve the ARIMA support in Rel 5.1, but was unable to get it tested in time. This feature will be included in the next release.

Posted in Announcement, New Release | Comments Off on More about Release 5.1

Real Statistics Release 5.1

I am proud to announce Release 5.1 of the Real Statistics Resource Pack, which is loaded with a lot of new features. The new release is now available for free download at Download Resource Pack for Excel 2013 and 2016 (Windows version) environments. The resource pack for Excel 2007 and 2010 environments will be available within the next 24 hours.

The example files Examples Workbook Part 1A, Examples Workbook Part 1B, Examples Workbook Part 2 and Multivariate Examples been updated for compatibility with Release 5.1.

The Real Statistics website will be updated over the course of the next several days to reflect the new capabilities in Release 5.1.

Also thanks to all of you who have given donations to help sustain the Real Statistics project. This is most appreciated as are the countless number of people who have identified errors and who have made suggestions to improve the software and website.

The following is a summary of the new features in Release 5.1.

Poisson Regression

A new Poisson Regression data analysis tool has been added that performs regression where the dependent variable contains count data.

Supporting this new tool are the following new functions: PoissonCoeff to calculate the regression coefficients and standard errors, PoissonCov to output the coefficient covariance matrix, and PoissonPred, PoissonPredC and PoissonPredCC to make predictions based on a Poisson regression model.

Correspondence Analysis

A new Correspondence Analysis multivariate data analysis tool has been added. Correspondence analysis plays a role similar to factor analysis or principal component analysis for categorical data expressed as a contingency table. The new tool will carry out the analysis and produce correspondence analysis plots.

Supporting this new tool are the following new functions: CARowFactors and CAColFactors, which return factor vectors (for the original data as well as for supplementary profiles) and CAEigen, which returns the eigenvalues for the correspondence analysis.

2^k Factorial Design

A new 2^k Factorial Design data analysis tool has been added to support ANOVA consisting of any number of factors, each of which has two levels.

Supporting this new tool are the following new functions: Design2k and ExpandDesign2k, which automatically create the coding for such designs, Effect2k, which calculates the effect sizes for 2^k factorial designs, and SS2k, which calculates the SS (sum of squares) values for these designs.

Tukey HSD and Tukey-Kramer Tests

The existing Tukey HSD and Tukey-Kramer options to the ANOVA: Single Factor data analysis have been revised. Instead of having to manually perform separate comparison tests, all possible pairwise comparisons are performed automatically. This approach will be be adopted for other ANOVA follow up tests in future releases.

One Factor ANOVA data analysis tool

The layout of the ANOVA: Single Factor dialog box has been revised to make the various options clearer and consistent with other data analysis dialog boxes. In addition, the Dunnett-KW test option (a Kruskal-Wallis follow-up test) has been renamed the Steel test. A new Kruskal-Wallis follow-up test has also been added called the Schaich-Hamerle test.

New functions for t, F and chi-square distributions

Excel’s T.DIST, F.DIST and CHISQ.DIST functions (as well as the related functions and their Excel 2007 equivalents) round down the degrees of freedom to the next lower integer. This can be a problem in some situations, and so we previously introduced the F_DIST and CHISQ_DIST functions which work exactly like F.DIST and CHISQ.DIST except that they don’t round off non-integer degrees of freedom, thereby improving the accuracy of some calculations.

We have now added the following functions which provide similar advantages: T_DIST_RT, T_DIST_2T, T_INV, T_INV_2T, F_DIST_RT, F_INV, F_INV_RT, CHISQ_DIST_RT, CHISQ_INV and CHISQ_INV_RT. In addition, we have enhanced the existing T_DIST function so that it too doesn’t round off the degrees of freedom.

Two sample correlation tests with dependent samples

The Real Statistics already provides the Correl2Test function to test whether two sample pairs drawn independently have significantly different correlations. We now add similar support in the case where the two sample pairs are not independent. In particular, we support two such cases.

In the first case, the two sample pairs have one variable in common. The new array functions Correl2OverlapTTest, Corr2OverlapTTest, Correl2OverlapTest and Corr2OverlapTest support this case, using two different approaches.

In the second case, there is no variable in common. This case might be employed when one pair represents one moment in time and the second pair represents the same subjects at another moment in time. The new array functions Correl2NonOverlapTest and Correl2NonOverlapTest support this case.

Accuracy Improvements

As mentioned above, Excel’s T.DIST, F.DIST and CHISQ.DIST functions (as well as the related functions and their Excel 2007 equivalents) round down the degrees of freedom to next lower integer. This is not a problem for most tests, but can give inaccurate results for some tests, and is especially a problem when the degrees of freedom is less than one.

In order to address this issue, we have replaced T.DIST.2T, F.DIST.RT, CHISQ.DIST.RT, etc. by their Real Statistics equivalents, T_DIST_2T, F_DIST_RT, CHISQ_DIST_RT, etc. for a number of Real Statistic tests (e.g. two sample t test with unequal variance, Hotelling’s T-square test with unequal variance and the Wilk’s version of MANOVA).

If we have not done this for some other test, please send me a comment so that we can correct this in a future release.

Fisher Exact Test

By default, there are limits to the size of the contingency tables supported by the FISHERTEST and FISHER_TEST functions. These limits were set since these functions can take a very long time to run with larger tables and so you may inadvertently block Excel. The limits for these functions have now been revised as follows.

Contingency tables with degrees of freedom less than 9 are supported; tables with 9 or higher degrees of freedom are currently not supported. For each supported table, there is a limit to the total cell count, i.e. the sum of all values in the table, as follows.

  • 2 × 2 – no limit, 2 × 3 – 2,000, 2 × 4 – 1,250,  2 × 5 – 360
  • 2 × 6 – 175,  2 × 7 – 110, 2 × 8 – 75,  2 × 9 – 40
  • 3 × 3 – 320,  3 × 4 – 95, 3 × 5 – 30

If you want to exceed these limits, you can add a third argument to the FISHERTEST function which describes how much you want to increase the limit. E.g. if you want to use the Fisher exact test for a 3 × 3 contingency table in range A1:C3 the sum of whose cells is 350, then you can use the array formula =FISHERTEST(A1:C3,,1.1). The 1.1 specifies that you have increased the limit for a 3 × 3 contingency table from 320 to 320 × 1.1 = 352. Since 350 < 352, the function will run, although it will take longer.

Enhancement for other resource intensive functions

In addition to the Fisher exact test functions listed above, the following functions are resource intensive and are limited in terms of the size of the samples supported.

  • A default limit of n1 + n2 = 28 (sum of the two sample sizes) has been set for MANN_EXACT, Perm2Dist and Perm2Inv, MannDist and MannInv
  • A default limit of sample size n = 25 has been set for SRANK_EXACT, SRANKPair_EXACT, PermDist and PermInv 

In the same manner as described above for FISHERTEST, you can add an argument (i.e. the final argument) to any of the above functions to explicitly change these limits.

Bug Fixes

  • Fixed bug in the GG_Epsilon function which caused this function and the HF_Epsilon function to produce an error value
  • Fixed bug in F_DIST(x, df1, df2, cum) when cum = FALSE
  • Fixed the formatting for the Mixed Repeated Measures data analysis tool when the Standard formatting and Regression options were chosen. When more than a few independent variables were used, the analysis portion of the output tried to overwrite the descriptive statistics portion of the output. This has now been fixed.
  • Moved the heading of the output from the Three Factor ANOVA data analysis tool one cell to the right
Posted in Announcement, New Release | Comments Off on Real Statistics Release 5.1

Release 5.0 for Mac

New Mac Release

Good news for Mac users. The latest release of the Real Statistics Resource Pack (Release 5.0) is now available for use with Excel 2016 for the Mac. You can now download this release at Real Statistics Resource Pack for Mac.

There is also a new release of the Real Statistics Resource Pack that is compatible with Excel 2011 (Mac), but this has not been tested. You can download this version from Real Statistics Resource Pack for Mac as well. I would appreciate your informing me whether this release works on your Mac computer.

I have put in considerable energy and investment to create this new release and test it on a Mac computer. Any donation from you would be appreciated to help offset my costs.

User Interface

The user interface for the Mac version of the Real Statistics data analysis tools is identical to the Windows version with one important difference. When inserting a range into an input field, in Windows you can simply highlight the range of cells and the field will automatically contain the address of the highlighted range (see Figure 1).

Insert range Windows

Figure 1 – Inserting a Range in Windows

In the Mac version of Real Statistics to accomplish the same thing, you need to click on the + button, as shown in Figure 2.

Insert range on Mac

Figure 2 – Inserting a Range on the Mac (step 1)

This will display the dialog box shown on the right side of Figure 3. You can now highlight the desired data range (M3:M7 in this example) and when you press the OK button, the Input Range field will automatically be filled with the appropriate cell range address.

Insert range dialog Mac

Figure 3 – Inserting a Range in the Mac (step 2)

The situation is the same for the Output Range, except that now you should only highlight one cell.

Posted in Announcement, New Release | Comments Off on Release 5.0 for Mac

Release 5.0

I am pleased to announce Release 5.0 of the Real Statistics Resource Pack. The new release is now available for free download at Download Resource Pack for Excel 2007, 2010, 2013 and 2016 (Windows version) environments.

I am still working on Release 5.0 for the Mac, and I expect this to be available in June.

The Examples Workbook Part 1 has now been split into two files: Examples Workbook Part 1A and Examples Workbook Part 1B. These files as well as Examples Workbook Part 2 have been updated for compatibility with Release 5.0. The reliability examples, except for the ICC examples, can now be found in Workbook Part 1B and not Workbook Part 2.

The Real Statistics website will be updated over the course of the next several days to reflect the new capabilities in Release 5.0.

My apologies to all of you who have been waiting for the Real Statistic book. The revised timeframe for Real Statistics using Excel – Fundamentals is now September 2017.

Also thanks to all of you who have given donations to help sustain the Real Statistics project. This is most appreciated as are the countless number of people who have identified errors and who have made suggestions to improve the software and website.

The following is a summary of the new features in Release 5.0.

Krippendorff’s Alpha

Support for Krippendorff’s Alpha, another approach to inter-rater reliability, has been added. This approach has the advantage that it supports categorical, ordinal, interval and ratio type data and also handles missing data.

New functions have been added (KALPHA, KTRANS, KRIP_SES, KRIP_SER, KRIP) to support Krippendorff’s Alpha as well as a new data analysis tool.

Gwet’s AC2

Support for Gwet’s AC2 has also been added.  Gwet’s AC2 is yet another approach to inter-rater reliability which is similar to Krippendorff’s Alpha

New functions have been added (GWET_AC2, GWET_SES, GWET_SER, GTRANS, GWET) to support Gwet’s AC2, as well as a new data analysis tool.

Reliability data analysis tools

The Reliability data analysis tool has been replaced by the following three data analysis tools:

  • Internal Consistency Reliability: Cronbach’s Alpha and Split Half / Guttman’s
  • Interrater Reliability: Cohen’s Kappa, Weighted Kappa, Kendall’s W, Bland-Altman, Intraclass Correlation, Krippendorff’s Alpha and Gwet’s AC2
  • Item Analysis: Discrimination Index, Difficulty Index, Point Biserial Correlation

Distribution Fitting Capabilities

The goal of these new capabilities is to determine how to fit various distributions to sample data. In particular, new functions have been added to estimate the parameters of these distributions using the method of moments (WEIBULL_FITM, GAMMA_FITM, BETA_FITM, UNIFORM_FITM), maximum likelihood (WEIBULL_FIT, GAMMA_FIT, BETA_FIT, UNIFORM_FIT) and regression (WEIBULL_FITR).

Anderson-Darling Test

The Anderson-Darling Test is a way of determining whether a specified distribution is a fit for a given sample. This test is now provided for the following distributions: normal, exponential, Weibull, gamma and generic (i.e. any distribution with no unknown parameters).

New functions have been added (ANDERSON, ADTEST, ADCRIT, ADPROB) to support the Anderson-Darling Test, as well as a new data analysis tool.

Chi-square Goodness of Fit Test

New distribution-specific capabilities have been added to complement the existing FIT_TEST function. The following distributions are initially supported: normal, exponential, Weibull, gamma, beta and uniform. The new GOFTESTExact function can be used when the distribution parameters are known and the new GOFTEST function can be used when the distribution parameters are not known. In addition these tests can be performed via a new data analysis tool.

Non-parametric data analysis tools

The Non-parametric data analysis tool has been split into the following two data analysis tools:

  • Non-parametric Tests: Friedman’s Test, Runs Tests, Cochran’s Q Test, Moods’ Test
  • Goodness of Fit Tests: Two Sample Kolmogorov-Smirnov Test, One Sample Anderson-Darling Test, Chi-square Goodness of Fit Test

Changes to the User Interface

Upon pressing Ctrl-m (or an equivalent) you have access to the various data analysis tools via the original interface or the newer MultiPage interface. A new Corr tab has been added to the MultiPage interface that provides access to the following data analysis tools: Correlation Tests, Polychoric Correlation as well as the three reliability data analysis tools described above.

A new Reliability option has been added to the original interface, which gives access to the three reliability data analysis tools described above. Also a Goodness of Fit option has been added.

Improved Box Plots

The existing Box Plot and Box Plot with Outliers data analysis capabilities have been revised to better handle negative data elements. In such cases, you should refer to the labels for the y axis shown on the right side of the chart. Big thanks to Bob who explained how to make this improvement!

In addition, the Box Plot now shows the mean for each group (via an × on the chart)

Statistical Tables

A Two Sample Kolmogorov-Smirnov table of critical values has been added as well as One Sample Anderson-Darling tables of critical values.

The One Sample Kolmogorov-Smirnov table of critical values has also been revised. This also improves the accuracy of the KSCRIT and KSPROB functions. Errors in the KSCRIT, KSPROB and KINV functions have also been fixed.

Two Sample Kolmogorov-Smirnov Test

A new KS2CRIT function has been added which automatically performs the lookup of values in the Two Sample Kolmogorov-Smirnov table of critical values. In addition, the new KS2PROB function estimates the p-value for the Two Sample KS test based on interpolation between values in the Two Sample Kolmogorov-Smirnov table of critical values.

Polygamma Function

The POLYGAMMA worksheet function has been added to calculate the digamma and trigamma functions.

Bug Fixes

  • Fixed the LCRIT and LPROB functions for n > 50
  • Fixed the LogitSelect function, which did not work properly
  • Fixed the Three Factor ANOVA using Regression (totals were not calculated)
  • Fixed the VAR_POWER function (roles of the two parameters were reversed)
  • Fixed Chi-square Independence Test data analysis tool when the standard format was used without headings
Posted in Announcement, New Release | Comments Off on Release 5.0

Release 4.14

I am pleased to announce Release 4.14 of the Real Statistics Resource Pack. The new release is now available for free download at Download Resource Pack for Excel 2007, 2010, 2013 and 2016 (Windows version) environments.

Note that now there are three versions of the software: one for Excel 2013/2016, another for Excel 2010 and a third for Excel 2007.

A new version for the Mac will be available within the next few weeks.

The Examples Workbook Parts 1 and 2 and the Multivariate Examples files have been updated for compatibility with the new release.

The Real Statistics website will be updated over the course of the next few days to reflect the new capabilities in Release 4.14.

Discriminant Analysis

A new Discriminant Analysis data analysis tool has been added to the multivariate analysis part of the Real Statistics Resource Pack. The tool will perform both linear discriminant analysis (LDA) and quadratic discriminant analysis (QDA).

Classification tables and predictions for training and non-training data can be made.

Polychoric Correlation

A new Polychoric Correlation data analysis tool has been added which calculates the polychoric correlation for two discrete, finite, ordinal variables (using Solver). Data for such variables can be organized into a two dimensional contingency table (as for the chi-square test of independence). When the two variables are dichotomous, the polychoric correlation is called a tetrachoric correlation.

In addition, the TCORREL function has been added which estimates the tetrachoric correlation coefficient, along with a p-value and confidence interval

Percentile and Quartile Functions

New QUARTILE_EXC(R1, k) and PERCENTILE_EXC(R1, p) functions have been added which have the same functionality as QUARTILE.EXC and PERCENTILE.EXC. This is useful for Excel 2007 users since this version of Excel doesn’t support the .EXC functions. These functions do differ from their Excel counterparts only in the extreme cases: where the Excel function return #NUM!, the Real Statistics functions return MIN(R1) and MAX(R1).

In addition, these functions take a final argument which provides other options for defining percentile and quartile based on Hyndman-Fan methods 4, 5, 6, 7, 8 and 9 (note that Excel’s .EXC approach is method 6 and .INC is method 7). QUARTILE_EXC(R1, k, m) and PERCENTILE_EXC(R1, p, m) default to method 6 when m is omitted.

Fisher test effect size

The FISHER_TEST function has been added, which not only reports the p-value for the Fisher exact test (as is done by FISHERTEST), but also estimates equivalent phi and Cramer V effect sizes.

Bug Fixes

  • Fixes bug in Cluster Analysis data analysis tool when writing to another webpage
  • Fixes bug in SVD functions
Posted in Announcement, New Release | 2 Comments

Update for Mac Users

Good news for Mac users.

I know that many Mac users have been eager to get the latest Real Statistics features for Excel on their Mac. Until now, I have been unable to do this since I don’t own a Mac and have been unable to borrow one for long enough to create and test a new version for the Mac.

I have now decided that the only solution is to buy a Mac. Until about 20 years ago I only used a Mac, but in the past 20 years I have been using Windows. After the new Mac arrives and I get acclimated to it, I will create the new Real Statistics release for Mac.

In order to offset the costs of the Mac, I ask any of you Mac users to make a small donation to offset my costs. This is completely optional, but will be appreciated. You can make the donation by clicking here on the word Donation.

Thanks for your continued support.

Charles

Posted in Announcement | Comments Off on Update for Mac Users

Release 4.13

I am pleased to announce Release 4.13 of the Real Statistics Resource Pack. The new release is now available for free download at Download Resource Pack for Excel 2007, 2010, 2013 and 2016 (Windows version) environments. Also the Examples Workbook Parts 1 and 2 and the Multivariate Examples files have been updated for compatibility with the new release.

The Real Statistics website will be updated over the course of the next few days to reflect the new capabilities in Release 4.13.

Probit Logistic Regression support

The logistic regression data analysis tool has been expanded to add an option for probit regression. This data analysis tool is now called Logistic and Probit Regression data analysis tool.

In addition, the following probit regression functions have been added, which operate like their logistic regression counterparts:

ProbitCoeff, ProbitCoeff2, ProbitCoeffs, ProbitRSquare, ProbitTest, ProbitPred, ProbitPredC

Mann-Whitney and Signed-Ranks Confidence Intervals

The following two new functions are supported which output a confidence interval for the Hodges-Lehmann median based on the Mann-Whitney and Signed-ranks tests:

MANN_CONF(R1, R2, lab, ttype, alpha): returns a column array with the following values based on the Mann-Whitney test conducted on the samples in ranges R1 and R2: lower and upper limits of the 1-alpha confidence interval using U-crit (defined by ttype), followed by the median and the lower and upper limits of the 1-alpha confidence interval based on U-crit + 1.

SRANK_CONF(R1, R2, lab, ttype, alpha): returns a column array with the following values based on the Paired Wilcoxon Signed-Ranks test conducted on the samples in ranges R1 and R2: lower and upper limits of the 1-alpha confidence interval using T-crit (defined by ttype), followed by the median and the lower and upper limits of the 1-alpha confidence interval based on T-crit + 1.

If lab = True, then an extra column containing labels is returned (default is False). alpha is the alpha value and defaults to .05.

If ttype = 0 (default), the the normal approximation is used. If ttype = 1 then a table lookup is used (via MCRIT and MPROB, or SRankCRIT and SRankPROB) with harmonic interpolation. If ttype = 2 then a table lookup is used with linear interpolation, and if ttype = 3 then an exact test is used (via MANNINV and MANNDIST, or PERMINV and PERMDIST).

There is also a one sample version of the SRANK_CONF function, namely

SRANK_CONF(R1, v, lab, ttype, alpha)

where v is the hypothetical median value.

Multinomial Logistic Regression Accuracy

A new function has been added that calculates the accuracy of a Multinomial Logistic Regression model.

MLogit_Accuracy(R1, r, lab, head, iter): returns a column array with the accuracy of the multinomial logistic regression model defined from the data in R1 for each independent variable and the total accuracy of the model. Thus, if R1 contains k independent variables, then the output is a k+1 × 1 column array (or a k+1 × 2 array if lab = True).

The arguments R1, r, lab, head and iter are as for the MLogitCoeff function.

New Distribution Functions

The following new functions support distributions that are useful in Bayesian statistics.

IGAMMA_DIST(x, alpha, beta, cum): inverse gamma distribution

IGAMMA_INV(p, alpha, beta): inverse of the inverse gamma cdf

ICHISQ_DIST(x, df, cum): inverse chi-square distribution

ICHISQ_INV(p, df): inverse of the inverse chi-square cdf

DIRICHLET_DIST(pvector, avector): Dirichlet distribution at pvector (whose values add up to 1) based on alpha parameters in avector.

DIRICHLET_RAND(avector): an array pvector consisting of random values for the Dirichlet distribution with alpha values in avector

Minor Enhancements

The CountRowsUnique function has been enhanced by adding two new optional arguments. CountRowsUnique(R1, head, ncols): returns a count of the unique rows in R1; if head = True (default False) then first row of R1 (presumably a heading) is not counted; the last ncols columns of R1 are not considered when determining uniqueness (ncols = 0).

The SUBRANGE function has been eliminated since its role is subsumed in the SUBMATRIX function.

The LogitCoeff, LogitCoeff2, LogitPred, LogitRSquare and LogitTest functions now accept non-numeric data. Rows with non-numeric data are ignored. In addition, a new optional guess argument has been added to these functions (as the last argument) which allows the user to specify initial coefficient values (instead of the default of zeros).

A new LinAlg category has been added to Excel’s standard Insert Function facility. This enables Windows users of versions of Excel 2010/2013/2016 to get additional information about the various Real Statistics matrix functions (DIAGONAL, eVECTORS, SCHUR, etc.). In addition, a new StatMath category has been added with some mathematical functions (especially those for complex number and matrix operations).

Various additional functions now accept arrays and not just ranges as their arguments.

Bug Fixes

  • Fixes an error in the Kaplan-Meier confidence intervals
  • Fixes an error in the VAR2_POWER and VAR2_SIZE functions
  • Fixes an overflow error in the eVECTORS function
  • Fixes a bug in the various logistic regression capabilities whereby LN(0) was not trapped and so an error was issued. Also fixes an error in the Multinomial Logistic Regression tool when checking whether the output will overwrite existing cells.
  • Fixes a bug in LogCoeff2 which prevented it from displaying its values.

Posted in Announcement, New Release | 2 Comments

Real Statistics with 64 bit Excel

While most people use the 32 bit version of Excel, there is an important community of users of the 64 bit version of Excel. A number of people have asked the question as to whether the Real Statistics Resource Pack works with the 64 bit version of Excel.

The following is an answer to this question based on the experiences of Paolo Piva, who is using the Real Statistics Resource Pack in Windows 10 (64 bit) using the 64 bit version of Excel 2016.

“I confirm that I use it under Windows 10 Pro 64 bits and that I installed it on Excel 64 bits, as part of Office 365 (which is Excel 2016).”

Paolo noted further that “when I closed Excel and then launched it again, the add-in menu with Real Statistics did not show up in the ribbon… I had therefore to implement the suggestion Unblock the Add-in File as described in: https://www.excelcampus.com/vba/add-in-ribbon-disappears/

Paolo added “just to make sure that things work properly and the Addins menu is displayed Excel must be run as an Administrator.  If it is not run as an Administrator the Addins menu will not be displayed but still the Real Statistics package will work and can be accessed with the usual “Command M”

I assume here that Paolo means the Ctrl-m key sequence.

Posted in Announcement, Hint | Comments Off on Real Statistics with 64 bit Excel

Real Statistics Rel 4.12

I am pleased to announce Release 4.12 of the Real Statistics Resource Pack. The new release is now available for free download (Download Resource Pack) for Excel 2007, 2010, 2013 and 2016 (Windows version) environments. Also the Examples Workbook Parts 1 and 2 and the Multivariate Examples files have been updated for compatibility with the new release.

The Real Statistics website will be updated over the course of the next several days to reflect the new capabilities in Release 4.12.

The new release contains the following new capabilities:

Deming Regression

A new Deming Regression data analysis tool has been added, along with the following functions:

DRegCoeff(R1, R2, λ, lab) = 2 × 2 array consisting of the intercept and slope coefficients and standard errors for Deming regression on the data in R1 and R2 where lambda = λ.

DRegResiduals(R1, R2, λ, lab) = n × 7 array consisting of predicted y, x-hat, y-hat, raw residual, x-residual, y-residual and optimized residual for each pair of data elements in R1 aand R2 based on the Deming regression on the data in R1 and R2 where lambda = λ and n = the number of elements in R1 (or R2).

DRegIdentity(R1, R2, λ, lab) = 2 × 1 array consisting of  and  for Deming regression on the data in R1 and R2 where lambda = λ.

DRegPred(x0, R1, R2, λ, lab) = 4 × 1 array consisting of the predicted value of y corresponding to x0, the standard error for this prediction and the lower and upper ends of a  confidence interval for this prediction based on the Deming regression on the data in R1 and R2 where lambda = λ.

If lab = TRUE (default FALSE), then a column with labels is appended to the output for readability.

If an explicit lambda argument is omitted, then it is calculated from the data in R1 and R2. If an explicit lambda argument is given, then it is assumed that R1 and R2 are column arrays. We also have the following function, which explicitly calculates lambda from the data in R1 and R2:

DRegLambda(R1, R2) = the lambda value calculated from R1 and R2

The standard errors are calculated using jackknifing as described on the website.

Split-Half and Guttman Reliability

A new Split-Half/Guttman data analysis tool has been added which calculates split-half reliability statistics using Spearman-Brown as well as Guttman’s measurement. In addition, the following functions are now supported.

COV_SPLIT(R1,  s) = sample covariance for the data in range R1 based on the split described by string s.

CORR_SPLIT(R1,  s) = correlation for the data in range R1 based on the split described by string s.

GUTTMAN_SPLIT(R1, s) = Guttman’s lambda for the data in range R1 based on the split described by string s.

GUTTMAN(R1) = the Guttman’s reliability measure for the data in range R1, i.e. the maximum Guttman’s lambda based on all possible splits; when the number of splits is too large, a second argument iter can be used to find an approximate maximum Guttman’s lambda based on a randomly generated iter number of splits.

String s consists of 0’s and 1’s where each character in the string corresponds to one column in R1 (thus the length of s must be equal to the number of columns in R1). E.g. the string s = “101010” represents the split half where the odd numbered questions are in one half and the even numbered questions are in the other half.

The following functions relate to the Spearman-Brown correction:

SB_SPLIT(R1, s) = split half coefficient (after Spearman-Brown correction) for data in R1 based on the split described by the string s.

SB_CORRECTION(r, n, m) = Spearman-Brown correction when the split-half correlation based on an m vs. n–m split is r. If n is omitted, then it is assumed that there is a 50-50 split. If n is present but m is omitted, then it is assumed that m = n/2.

Partitioning Functions

To support the Guttman functions, described above, the following functions have been added:

INIT_SPLIT(n, m) = returns a string of length n consisting of m 0’s followed by n–m 1’s. If omitted m defaults to n/2.

NEXT_SPLIT(s) = returns the string representing the next split after the split represented by s.

E.g. use of these functions will generate the following list of strings of length four: 0011, 0101, 0110, 1001, 1010, 1100.

The following new functions have also been added:

INIT_PARTITION(n) = returns a string consisting of n 0’s

NEXT_PARTITION(s) = returns the string representing the next partition after the partition represented by s.

E.g. use of these functions will generate the following list of strings of length three: 000, 001, 010, 011, 100, 101, 110, 111.

Finally, we also have also added the following functions:

RAND_SPLIT(n, m) = returns a random string of length n consisting of m 0’s and n–m 1’s. If omitted m defaults to n/2.

RAND_PARTITION(n) = returns a random string of length n consisting of 0’s and 1’s.

Spearman-Brown Predicted Reliability

The following functions have been added about Spearman-Brown predicted reliability. These functions can also be used with Cronbach’s Alpha.

SB_PRED(m, rho, n) = Spearman-Brown predicted reliability based on m items when Spearman-Brown for n items is rho.

SB_SIZE(rho1, rho, n) = the number of items necessary to bring the Spearman-Brown predicted reliability up (or down) to rho1 from n items with Spearman-Brown of rho.

LAD Regression Enhancements

The following enhancements have been made to the LAD Regression data analysis tool and the functions that support LAD regression:

  • Support for regression without an intercept has been added. Note the revised forms of the following functions where con = TRUE (default) if regression is with an intercept and con = FALSE if no constant regression term is used.

LADRegCoeff(R1, R2, con, iter)

LADRegWeights(R1, R2, con, iter)

  • An option has been added to the LAD Regression data analysis tool to report the standard error for the regression coefficients, as well as confidence intervals, based on bootstrapping. In addition, there is the following new function which outputs the standard errors based on nboot bootstraps:

LADRegCoeffSE(R1, R2, con, iter, nboot)

Note too that a bug has been fixed where the variable names were not shown correctly when the output from the data analysis tool was displayed on a new page.

Total Least Squares Regression

The following functions have been added to calculate the regression coefficients when total least squares is used instead of ordinary least squares.

If there is only one independent variable, the results will be the same as Deming Regression where lambda = 1. With more than one independent variable, the singular value decomposition (SVD) of the matrix containing the data from both the independent and dependent variables is used.

TRegCoeff0(R1, R2, lab) = column array consisting of the intercept and slope coefficients based on total least squares linear regression using the data in R1 and R2, where both R1 (containing x data) and R2 (containing y data) are column arrays. If lab = TRUE (default FALSE) then a column is appended with the labels “intercept” and “slope”.

TRegCoeff(R1, R2, iter) = column array consisting of the regression coefficients based on total least squares linear regression using the data in R1 and R2. iter (default 100) is the number of iteration used in calculating the SVD. iter is ignored if R1 contains only one column.

Three Factor ANOVA without Replication

The Three Factor ANOVA data analysis tool has been enhanced to support three factor ANOVA without replication.

Enhancement to Average Rank Function

The following function has been enhanced to solve problems with the standard Excel RANK.AVG function:

RANK_AVG(x, R1, order, num_digits)

This function is equivalent to the standard Excel function RANK.AVG, except that the values in range R1 are rounded off to num_digits decimal places (default 8) before the ranking is done.

This function addresses the following shortcomings in the RANK.AVG function:

  • RANK.AVG doesn’t handle decimal precision very well. E.g. you can have cases where 12.1 appears in the range A1:A10, yet =RANK.AVG(12.1, A1:A10) returns the error value #N/A.
  • RANK.AVG only accepts an explicit cell range as its second argument. E.g. even though A1:A10-B1:B10 evaluates to a cell range, =RANK.AVG(A1, A1:A10-B1:B10) will produce an error
  • RANK.AVG is not available in versions of Excel prior to Excel 2010.

Singular Value Decomposition Enhancement

The Singular Value Decomposition (SVD) option on the Matrix Operations data analysis tool has been enhanced to support non-square matrices. This is also the case for the following functions: SVD_U, SVD_D and SVD_V.

Donation Webpage

The website and Real Statistics software are free and will remain free of charge.

In order to offset some of my costs (especially for hosting services, software and materials), I am asking for a small donation if you are happy with the services that you have been receiving. This will be appreciated, but is completely optional. To donate, please click on Please Donate.

Bug Fixes

  • A bug in calculating the confidence interval in the Multiple Anova option of the Manova data analysis tool has been fixed.
  • When clicking on the Mann-Whitney, Wilcoxon Signed Ranks or Kruskal-Wallis options of the Non-parametric Tests data analysis tool, the Descriptive Statistics data analysis tool was incorrectly displayed. This has now been corrected.
  • Fixed an error in the display of Cook’s D output from the Linear Regression data analysis tool when the Column headings included in the data is unchecked.
  • Minor changes to some of the dialog boxes have been made, especially to correct some errors in the tool tips.
  • Various broken links on the website have been repaired, which should make the user experience better.

Improved Web Hosting Performance

Last month I contracted to upgrade the web hosting capabilities to improve the availability and response time of the Real Statistics website. After a few weeks of problems, I believe we are now seeing the benefits of this upgrade.

Ongoing Activities

I continue to upgrade the Real Statistics Resource Pack to accomplish the following two objectives. This will take time and will happen over the next several software releases:

  • Add documentation for each Real Statistics function to Excel’s standard Insert Function facility
  • Allow any function arguments that call for a cell range to use the output from another function as long as that output is equivalent to a cell range. E.g. you can also use the formula =GUTTMAN(TRANSPOSE(A1:J5)) or =GUTTMAN(A1:E10-F1:J10).

Charles

Posted in Announcement, New Release | Comments Off on Real Statistics Rel 4.12