Worksheet Functions
Real Statistics Functions: The Real Statistics Resource Pack contains the following functions:
FTEXT(R1): returns the formula in cell R1 as text
CharCount(s, c): returns the number of occurrences of character c in string s
VER(): returns the version number of the Real Statistics Resource Pack being used
ExcelVer(): returns the version number of the version of Excel being used
ColLabel(n): returns the label of the nth column
Array2String(R1): returns a string that represents the array R1
FirstNonNum(R1): returns the address (e.g. “B7”) of the first empty or non-numeric cell in range R1 or “None” if R1 does not contain any empty or non-numeric cells
SheetNames(): returns a column array with a list of all the sheet names for the current workbook
INDEX_MAX(R1, excl): returns the row index of the largest element in the column array or range R1
INDEX_MIN(R1, excl): returns the row index of the smallest element in the column array or range R1
Detailed Descriptions
FTEXT
If cell A1 contains the formula =SUM(B1:B6) then =FTEXT(A1) takes the value “=SUM(B1:B6)”. FTEXT can also be used with a cell range, and so =FTEXT(A1:A2) will return a column range with the formulas in cells A1 and A2.
FTEXT is similar to the standard Excel function FORMULATEXT. A key difference is that if cell A1 does not contain a formula, then =FORMULATEXT(A1) will return an error value, while =FTEXT(A1) will return the value in cell A1.
You can also use FTEXT for indirect references. E.g. in the first example above, you can use the formula =FTEXT(INDIRECT(“A1”)) to obtain the formula in cell A1. If cell B1 contains the value “A1”, then the formula in cell A1 can be obtained via the formula =FTEXT(INDIRECT(B1)).
CharCount
If cell A1 contains the text “I welcomed Charlie home”, then the formula =CharCount(A1,”e”) returns the value 4, while =CharCount(A1,”i”) returns the value 1 and =CharCount(A1,” “) returns the value 3.
Version numbers
If you are using Excel 2013 and Release 8.9 of the Real Statistics Resource Pack then VER() = “8.9 Excel 2010/2013/2016/2019/2021/365 Windows” and ExcelVer() = “15.0”.
Excel 2016, 2019, 2021, and 365 all return the Excel version number “16.0”.
ColLabel
It is easy to count rows since they are numbered. Columns are not numbered but instead are labeled by letters (see Excel Spreadsheets). The ColLabel function is useful when you need to count columns. E.g. the 5th column is E, the 50th column is AX and the 15,000th column is VDX. Thus ColLabel(5) = “E”, ColLabel(50) = AX and ColLabel(15000) = “VDX”.
To find the inverse of ColLabel you can use the Excel function COLUMN. E.g. to find out the number of the column AX, enter the formula =COLUMN(AX1). The result will be 50. Note that you shouldn’t put AX in quotes and you need to specify a row number (AX1 not AX).
Array2String
For example, suppose that the first row of range A1:B3 contains the values “A”, “B” and “C” and the second row contains the values 5, 10, 20, then the output from Array2String(A1:B3) is the string “{A,B,C;5,10,20}”.
Commas are used to separate columns and semi-colons are used to separate rows. The output string is enclosed in curly brackets.
FirstNonNum
The FirstNonNum(R1, ntype) function also takes an optional second argument which further specifies the type of error checking to be done. ntype can take any of the values shown in Figure 1 (including any of the acceptable values). ntype = “numeric” is the default.
| ntype | equivalent values | What R1 must contain to return “None” |
| error | any value that isn’t #N/A, #DIV/0!, etc. | |
| blank | empty | any value that isn’t an error value or blank/empty cell |
| numeric | num | numeric values (no error values, blanks or alphabetic characters) |
| non-negative | non-neg, non negative, non neg | non-negative numeric values (0, 3 or 3.7 but not -3 or -3.7) |
| positive | pos | positive numeric values (3 or 3.7 but not 0, -3, -3.7) |
| integer | int | integer values (0, 3 or -3, but not 3.7 or -3.7) |
| count | whole. natural | non-negative integers (0, 1, 2, …) |
| positive integer | pos-int, pos int | positive integers (1, 2, 3, …) |
| RGB | rgb | valid RGB codes; i.e. any integer from 0 to 256^3 – 1 |
| zero-one | dichot, dichotomous | only 0 or 1 |
| zero-to-one | numeric values from 0 to 1 (inclusive) |
Figure 1 – ntype values
INDEX_MAX and INDEX_MIN
Suppose that range A2:A6 contains the values -1, 4, 6, 0, 2. The formula =INDEX_MAX(A2:A6) returns the value 3 since the maximum value 6 occurs in the 3rd position of the range. Similarly, =INDEX_MIN(A2:A6) returns the value 1 since the minimum value -1 occurs in the first position.
These functions take an optional second argument which specifies that a specific element in the array or cell range should be excluded. E.g. the formula =INDEX_MAX(A2:A6, A3) still returns 3 since the 3rd element is still the largest element in A2:A6 excluding cell A3. If, however, you change the value of cell A4 to 1, then =INDEX_MAX(A2:A6, A3) would return the value 5 since the 5th element (with value 2) would now be the largest in A2:A6 excluding A3.
Note too that =INDEX_MAX(A2:A6, 2) is equivalent to =INDEX_MAX(A2:A6, A3) since A3 is the 2nd cell in the range.
The formula =INDEX_MAX(R1) is equivalent to the standard Excel formula =MATCH(MAX(R1),R1,0).
If more than one element in the column array or range R1 has the same maximum value then =INDEX_MAX(R1, excl) returns the relative position of the first such element.
INDEX_MIN works exactly like INDEX_MAX except that it identifies the index of the smallest element in an array or range.
References
Microsoft (2015) FORMULATEXT function
https://support.microsoft.com/en-us/office/formulatext-function-0a786771-54fd-4ae2-96ee-09cda35439c8
Wyatt, A. (2020) Determining your version of Excel
https://excelribbon.tips.net/T008938_Determining_Your_Version_of_Excel.html
Bruin, D. () Position of max value in list
https://exceljet.net/formulas/position-of-max-value-in-list