Real Statistic Text Functions | Real Statistics Using Excel

In Excel 2016, Microsoft introduced two new text functions: CONCAT and TEXTJOIN. Users of versions of Excel prior to Excel 2016 can use the Real Statistics TEXT_JOIN function which provides functionality equivalent to TEXTJOIN.

You can obtain the functionality in CONCAT(txt) via TEXT_JOIN(“”, FALSE, txt) or TEXT_JOIN(“”, TRUE, txt).

In addition, Real Statistics provides a few other text string functions as described below.

Worksheet Functions

Real Statistics Functions: The Real Statistics Resource Pack contains the following functions.

TEXT_JOIN(delim, ignore_empty, txt): returns a text string that concatenates all the text elements in txt inserting the delimiter delim between each of the text elements

TEXT_REVERSE(s): returns a text string with the characters in text string s in reverse order

CharCount(s, c): returns the number of times character c appears in text string s

DUPECHAR(c, n): returns a text string consisting of the character c repeated n times

txt is a list of cell ranges and/or text strings (separated by commas). If ignore_empty = TRUE then any empty cells in txt are ignored. The delimiter delim can be any text string.

Examples

Examples: Figure 1 shows how to use these functions. Note that the formula in cell D11 is equivalent to the Excel 2016 formula =CONCAT(A3,A10:B10).

Figure 1 – Text string examples

Score worksheet functions

Real Statistics Functions: The Real Statistics Resource Pack contains the following letter grade functions.

GRADEVAL(grade, pm): returns a numeric value between 0 and 4.5 where grade A corresponds to 4, B to 3, C to 2, D to 1, and F to 0. pm (plus or minus) takes a value between 0 and .5, where a plus symbol adds the pm value and a minus symbol subtracts the pm value (default 1/3).

VALGRADE(grade, pm, skinny): returns a letter grade (A+, A, A-, B+, B, B-, C+, C, C-, D+, D, D-, F) based on the numeric score specified by grade (which usually takes a value between 0 and 4.5). pm (default 1/3) is used to determine how the plus and minus symbols are assigned to the letter grade. If skinny = TRUE (default FALSE) then the A+ and D- grades are not used (replaced by A and D).

Examples: Figure 2 shows some examples of how to use these grade conversion functions when pm = .2.