Built-in Excel Functions

In this section we provide tables of built-in worksheet functions that are particularly useful for statistical analysis.

Basic mathematical functions

Mathematical worksheet functions ExcelFigure 1 – Table of basic mathematical functions

Some properties of EXP and LN (see also Exponentials and Logs):

LN(EXP(x)) =x                                     EXP(LN(x)) = x
LN(1) = 0                                               EXP(0) = 1
LN(x*y) = LN(x) + LN(y)                   EXP(x+y) = EXP(x) * EXP(y)
LN(x/y) = LN(x) – LN(y)                   EXP(x-y) = EXP(x) / EXP(y)
LN(x^y) = y * LN(x)                            EXP(x) ^ y = EXP(x*y)

e = EXP(1) =  = 2.718282…

Combinatorial functions

Combinatorial worksheet functions ExcelFigure 2 – Table of combinatorial functions

Rounding functions

Rounding worksheet functions ExcelFigure 3 – Table of rounding functions

Integer functions

Integer worksheet functions ExcelFigure 4 – Table of integer-valued functions

Note that INT(x) = TRUNC(x, 0) when x ≥ 0 and INT(x) = TRUNC(x, 0) – 1 when x < 0.

Random number functions

Random number functions ExcelFigure 5 – Table of random number functions 

The random numbers generated change every time the cell containing the function is regenerated. To generate a random number value that does not change, enter RAND(), or an expression containing RAND(), and then press the function key F9 followed by Enter.

Sum and related functions

For the following examples, let R1 = {4, 6, 7, -3}, R2 = {6, 8, -5, 7}, R3 = {5, “”, “A”, 6, -1}

Sum functions ExcelFigure 6 – Table of summing and related functions

You can also make a count of the unique elements in a data range in Excel. The following array formula counts the number of unique values in range R1 (see below for information about COUNTIF).

      =SUM(1/COUNTIF(R1,R1))

The only problem is that an error results if R1 contains an empty cell. To correct this, you can use the following array formula:

      =SUM((R1<>””)/COUNTIF(R1,R1&””))

If you replace SUM by SUMPRODUCT then the result is an ordinary formula, and not an array formula, and so you only need to press Enter not Ctrl-Shft-Enter.

To count the number of unique numeric cells in range R1 you can use the following array formula:

      =SUM(IF(FREQUENCY(R1,R1)>0,1))

Once again if you replace SUM by SUMPRODUCT the result is an ordinary formula. In fact when SUMPRODUCT has only one argument then it is equivalent to SUM even when used in array formulas. The reasons that these formulas give the desired results is a bit complicated and depends on how Excel treats array formulas, and so it is not necessary for our purposes to go into this further.

The Real Statistics Resource Pack provides the supplemental function COUNTU(R1) which counts the number of unique numeric cells in range R1 and COUNTAU(R1) which counts the number of unique cells in range R1 (see Supplemental Excel Functions).

Figure 7 contains some examples of how these formulas are used.

Count unique formulas Excel

Figure 7 – Count of the number of unique elements in a range

Conditional functions

IF: The most important conditional function is IF, which takes the form

IF(logical-test, value-if-true, value-if–false)

Thus if the logical test evaluates to a TRUE value then the value of the cell containing the IF function is the value of the 2nd argument, while if the logical test evaluates to FALSE then the value of the cell containing the IF function is the value of the 3rd argument.

Here the logical test can be built of from the logical operators and comparison operators. For example

=IF(A5 > G6, …)
=IF(NOT(A7 = B8 + 7) AND (SUM(E2:E7) >= 0, …)

In addition some special logical function such as ISERROR, ISBLANK, ISODD, ISEVEN and ISNUMBER are available. E.g. IF(ISEVEN(A5), …) returns the value TRUE if cell A5 contains an even number and returns the value FALSE if cell A5 is not even.

IF statements can also be nested. E.g. the formula

IF(A3>8,”GOOD”,IF(A3<0,”NEG”,”BAD”))

returns GOOD if the value in cell A3 is larger than 8, it returns NEG if this value is negative and it returns BAD if A3 has a value between 0 and 8 inclusive.

IFS: As described above, you can nest IF statements. The problem with this is that the nesting (and especially the placement of parentheses) can become complicated. Excel 2016 introduces a new IFS function which simplifies such formulas, and takes the form

IFS(logical-test1, value-if-true1, logical-test2, value-if-true2, …)

Thus IF(A3>8,”GOOD”,IF(A3<0,”NEG”,”BAD”)) can be expressed as

=IFS(A3>8,”GOOD”,A3<0,”NEG”,TRUE,”BAD”)

Note that this IFS formula contains three logical tests, the third of which is TRUE.

SWITCH: Excel 2016 also provides a new conditional function similar to IFS, which takes one of the following forms:

SWITCH(expression, value1, result1, value2, result2, …)

SWITCH(expression, value1, result1, value2, result2, …, result-default)

These are equivalent to

IFS(expression=value1, result1, expression=value2, result2, …, TRUE, #N/A!)

IFS(expression=value1, result1, expression=value2, result2, …, TRUE, result-default)

SUMIF and COUNTIF functions: The first of these functions takes the form SUMIF(R1, criteria, R2) where R2 is the array of potential values to be summed and R1 is an array of the same shape and size containing values to be matched against the criteria. For each value in R1 that meets the criteria, the corresponding value in R2 is used in the sum. Where the values in R1 are numeric, criteria can take the form of a constant such as 34 or B5, or a logical expression (in quotes) of form “>34”, “<>34”, “<=34”or “>”&B5.

Note that you need to use the ampersand (i.e. the concatenation operator) when creating logical expressions that refer to a cell’s value. Thus the “<=”&B5 criteria is met for all cells in R1 which are less than or equal to the value in cell B5.

SUMIF COUNTIF Excel

Figure 8 – Examples of COUNTIF and SUMIF functions

COUNTIF takes the syntax COUNTIF(R1, criteria). The value of this function is the number of elements in the array R1 that meets the criteria. While for SUMIF, R1 and R2 are usually one-dimensional arrays (i.e. row or column vectors), for COUNTIF, R1 can be any m ×  n array.

Where the values in array R1 are alphanumeric, the criteria takes forms such as “Male” or B5. Wildcards can also be used; e.g. “*ite” selects all values in R1 which end in the letters “ite”. Selection is case-insensitive, i.e. “Male” is equivalent to “MALE” or “mALe”. You can also use criteria such as “>=”&”M” to select all values in R1 which start with the letter M or higher.

A few other examples:

COUNTIF examplesFigure 9 – Additional examples of COUNTIF

SUMIFS and COUNTIFS: These functions are similar to the above functions. The difference is that multiple criteria can be applied. The syntax of these functions is as follows where all the arrays have the same shape and size:

SUMIFS(R, R1, criteria1, R2, criteria2,…)
COUNTIFS(R1, criteria1, R2, criteria2,…)

In the above, two criteria are indicated, but many more can be used. The value of SUMIFS is the sum of all the elements in array R that meet all the criteria. An element in R meets all the criteria provided the corresponding element in R1 meets criteria1, and the corresponding element in R2 meets criteria2, etc.

The value of COUNTIFS is a count of the number of elements that meet all the criteria.

Note that SUMIF(R1, criteria, R2) is equivalent to SUMIFS(R2, R1, criteria).

We can also define AVERAGEIF and AVERAGEIFS using the same syntax as for SUMIF and SUMIFS. These functions calculate the average values of the elements meeting the criteria.

Excel 2016 also provides the MINIFS and MAXIFS using the same syntax as for SUMIFS. These functions calculate the minimum and maximum values of the elements meeting the listed criteria.

Table lookup functions

To illustrate the table lookup functions we use the following data in the examples.

Data table lookupFigure 10 – Data range for examples

Table lookup functions ExcelFigure 11 – Table of lookup functions

If R is a column vector (i.e. an r × 1 array), then INDEX(R, r) can be used in place of INDEX(R, r, 1).

Note that the first argument of the OFFSET function must be a reference to a cell; it cannot be a constant. Thus =OFFSET(4,2,3) would yield an error.

For our purposes, we only use MATCH where the second parameter is a row or column vector. We don’t consider R taking a form such as G4:J7, only forms such as G4:J4 (row vector) or G4:G8 (column vector).

If R is a row vector then MATCH(x, R, 0) returns the column number of the first incidence of x in the R. If x occurs multiple times then the column number of the first match is returned. If there is no match then #N/A is returned. You can test for such an error using ISERROR(MATCH(x, R, 0)). Similarly, if R is a column vector, then MATCH(x, R, 0) returns the row number of the first incidence of x in the R.

Note that when MATCH is looking for a match the formats are also matched. Thus MATCH(6,A1:A5,0) = 4 for the example data above, while MATCH(“6”,A1:A5,0) = #N/A, indicating that no match is found. Text is not case-sensitive. Thus “Z” and “z” match, as well as “cat” and “CaT”. Thus MATCH(A4:E4,”z”,0) = 2 and not 5.

Wildcards are also used with text: ? stands for any single character while * stands for any sequence of characters. Thus, MATCH(“?”,C1:C4,0) = 1 and not 3 since “?” is recognized as a wildcard and not the question mark character.

Finally in all the above examples, we set the third argument of MATCH to zero. Actually this parameter can be set to -1, 0 or 1. If omitted the default value is 1.

MATCH function ExcelFigure 12 – Third argument of the MATCH function

Matrix functions

Excel supports the following matrix functions. These functions are covered in more detail in Matrices and Iterative Procedures.

Matrix functions ExcelFigure 13 – Table of matrix functions

Excel 2013 also provides the array function MUNIT(n) which returns the n × n identity matrix.

6 Responses to Built-in Excel Functions

  1. Jody says:

    Hi
    Can you have a function inside of a formula? If so are there any examples?

    • Charles says:

      Hi Jody,
      Yes. There are many examples on the website. E.g. =AVERAGE(ABS(A1:A40)), the mean of the absolute values of the data elements in range A1:A40. Or =SUM(A1:A4)+1, one more than the sum of the elements in the range A1:A4.
      Charles

  2. Raza says:

    Can someone tell me how to get the probability that three quotes are within x number of each other?

    Thank you

    • Charles says:

      Raza,
      You need to supply additional information. E.g. is there a lower and upper limit for how big a quote can be? Do the quotes follow some distribution?
      Charles

  3. dockhem says:

    i have a problem in excel.my excel workbook has a sheet (1),which have number in j5. in k5 there long if formula , which calculate data from j5 , =IF(J5<1,"0",IF(J5<11,"2",IF(J5<21,"4",IF(J5<31,"6",IF(J5<41,"8",IF(J5<51,"10",IF(J5<61,"12",IF(J5<71,"14",IF(J5<81,"16",IF(J5<91,"18",IF(J5<101,"20",IF(J5<111,"22",IF(J5<121,"24",IF(J5<131,"26",IF(J5<141,"28",IF(J5<151,"30",IF(J5<161,"32",IF(J5<171,"34",IF(J5<181,"36",IF(J5<191,"38",IF(J5<201,"40",IF(J5<211,"42",IF(J5<221,"44",IF(J5<231,"46",IF(J5<241,"48",IF(J5<251,"50",IF(J5<261,"52",IF(J5<271,"54",IF(J5<281,"56",IF(J5<291,"58",IF(J5<301,"60",IF(J5<311,"62",IF(J5<321,"64",IF(J5<331,"66",IF(J5<341,"68",IF(J5<351,"70",IF(J5<361,"72",IF(J5<371,"74",IF(J5<381,"76",IF(J5<391,"78",IF(J5<401,"80",IF(J5<411,"82",IF(J5<421,"84",IF(J5<431,"86",IF(J5<441,"88",IF(J5<451,"90",IF(J5<461,"92",IF(J5<471,"94",IF(J5<481,"96",IF(J5<491,"98",IF(J5<501,"100",IF(J5<511,"102",IF(J5<521,"104",IF(J5<531,"106",IF(J5<541,"108",IF(J5<551,"110",IF(J5<561,"112",IF(J5<571,"114",IF(J5<581,"116",IF(J5<591,"118",IF(J5<601,"120",IF(J5<611,"122",IF(J5<621,"124",IF(J5<631,"126",IF(J5<641,"128","T"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). in another sheet named April in n5 cell i have =IF('(1)'!$A$5:$A$20905=APRIL!$A5,(SUMIF('(1)'!$I$5:$I$20905,APRIL!$M$1,'(1)'!$K$5:$K$20905)),0) formula. this always returns 0, whether k5 has anything. can you help me ?

    • Charles says:

      Dockhem,
      I’m afraid that I can’t help you since it would take me too long to decipher your formula.
      Charles

Leave a Reply

Your email address will not be published. Required fields are marked *