I am pleased to announce Release 2.11 of the Real Statistics Resource Pack. The new release is available for immediate free download (Download Resource Pack) with Excel 2010 and 2013. The versions for older Windows releases of Excel and the Mac version of Excel will be available shortly.
The Real Statistics Examples Worksheet has been updated with some new examples. You can also download this file for free (Download Examples). The website is in the process of being updated for compatibility with the new release.
This release provides the following new functionality:
Data Analysis Tools: The following changes have been made to the data analysis tools in the resource pack:
Input Range Fill button: When using data ranges with many rows, it can be annoying to keep highlighting the entire range. You can now highlight just the first row of the desired data range and then click on the Fill button right next to the Input Range field. The range will then be modified to include all the rows down to the last row above a row with only empty cells.
Output Range New button: When you click on the New button right next to the Output Range field the output is written to a new worksheet (this is done by resetting the output range field to empty).
Tooltips: Tooltips have been added to explain the elements (buttons, text fields, etc.) in the dialog boxes generated when using the data analysis tools in the Real Statistics Resource Pack. Placing the mouse pointer over one of these elements provides information about how to use that element.
Data analysis selection: on the initial dialog box prompting you to select one of the data analysis tools, you can now select a data analysis tool by double clicking (without using the OK button).
Studentized q distribution: The following functions have been added to the resource pack:
QDIST(q, k, df) = studentized q cumulative distribution value for q with k independent variables and df degrees of freedom
QINV(p, k, df) = inverse of the studentized q distribution, i.e. the critical value for the studentized q range.
Partial correlation: The following functions have been added to the resource pack where R1 is an n × k matrix representing samples of size n for the random variables x1,…xk:
PCORREL(R1, i, j) = the partial correlation coefficient of xi with xj based on the data in R1.
PCORR(R1) is an array function which outputs the partial correlation matrix for the data in R1
Reformatting functions: The following array functions have been modified, adding an optional third argument s.
DELROWBLANK(R1, b, s) – fills the highlighted range with the data in range R1 omitting any row which has one or more empty cells
DELROWNonNum(R1, b, s) – fills the highlighted range with the data in range R1 omitting any row which has one or more non-numeric cells
The string s is used as filler in case the output range has more rows than needed. This argument is optional and defaults to the error value #N/A.
if b is True then the first row of R1 (presumably containing column headings) is always copied (even if it contains an empty or non-numeric cell); this argument is optional and defaults to b = False.
In addition, a new function, CountFullRows(R1, b), has been added where b = True (default) or False and
CountFullRows(R1, True) = the number of rows in range R1 which don’t have any empty cells
CountFullRows(R1, False) = the number of rows in range R1 which don’t have any non-numeric cells
Reformatting data analysis tool: The following changes have been made to this tool
- The Remove rows with empty cells and Remove rows with non-numeric cells options have been removed (see below for a new data analysis tool containing these options)
- If you leave the Filler field empty then the default #N/A is used as filler, but if you insert 0 in the field then an empty cell is used as filler
Reformatting Rows data analysis tool: A new data analysis tool has been added which enables you to choose to perform one the following row by row operations:
- Sort a range by rows (using a selected column as the sort code) or
- Remove any rows from a range which contains empty cells or
- Remove any rows from a range which contains non-numeric cells.
These provide easier-to-use versions of the existing QSORTRows, DelRowBlank and DelRowNonNum array functions.
Frequency data analysis tool: In the previous release the maximum bin value used in constructing a frequency table was automatically set to the largest value in the input data set. You can now manually set the Maximum Bin Value to a value greater or equal to the largest data value. This will often make the frequency table and histograms easier to read. If Maximum Bin Value is blank then the default maximum bin value is used as before.
Table Lookup Functions: For the statistical table lookup functions TauCRIT(n, α, t), RhoCRIT(n, α, t), SRankCRIT(n, α, t), KCRIT(n, α, t), WCRIT(n1, n2, α, t), MCRIT(n1, n2, α, t) and QCRIT(k, df, α, t), the arguments α and t are optional with α defaulting to .05 and t (tails) defaulting to 2.
The value of the table lookup function SWCoeff(50,25,False) has now been corrected to give the value -.0035 (instead of -.035).