MIN function

Category: Statistical functions

What does this function do?

Returns the minimum value in a list of values. Ignores logical values and text.

Syntax:

MIN(number1,number2,...)

• number1 (required): The first of the values or ranges to evaluate.
• number2 (optional): From the second number or range parameters are optional.

Examples:

=MIN(1, 2, 3) = 1
=MIN(A1:A2,B1:B2) = 1, if A1:A2= {1,2} and B1:B2= {2,3}

MAX function

Category: Statistical functions

What does this function do?

Returns the maximum value in a list of values. Ignores logical values and text.

Syntax:

MAX(number1,number2,...)

• number1 (required): The first of the values or ranges to evaluate.
• number2 (optional): From the second number or range parameters are optional.

Examples:

=MAX(1, 2, 3) = 3
=MAX(A1:A2,B1:B2) = 3, if A1:A2= {1,2} and B1:B2= {2,3}

Statistical Functions

The Statistical Functions are used in Excel to perform statistical operations. Take a look at some of the most common functions.

AVERAGE - Returns the average (arithmetic mean) of the arguments, which can be numbers, names, arrays, or references that contain numbers.

COUNT - Count the number of cells in a range that contain numbers.

COUNTA - Count the number of cells in a range that are not empty.

COUNTBLANK - Count the number of cells in a range that are empty.

COUNTIF - Count the cells in a range that meet a specified condition.

COUNTIFS- Count the cells in a range that meet one or more specified conditions.

FREQUENCY - Calculate the frequency of occurrence of a value within a range of values and returns a vertical array of numbers.

LARGE - Returns the largest k-th value of a data set.

MAX - Returns the maximum value in a list of values. Ignores logical values and text.

MEDIAN - Returns the median of a data set.

MIN - Returns the minimum value in a list of values. Ignores logical values and text.

MODE.SNGL - Returns the most frequent or repetitive value of an array or range of data.

RANK.EQ - Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same hierarchy, the top hierarchy of the set of values is returned.

SMALL - Returns the smallest k-th value of a data set.

FREQUENCY function

Category: Statistical functions

What does this function do?

Count the cells in a range that meet one or more specified conditions.

Syntax:

FREQUENCY(data_array, bins_array)

• data_array (required):An array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.

• bins_array (required): An array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

Examples:

=FREQUENCY(A1:A6,B1:B3) = {0, 1, 2, 3}, if A1:A6 = {11; 20; 21; 30; 29; 29} and B1:B3 = {10; 17; 23}. This means that the number of values equal to 10 or less is 0, the number of values in the interval 11 to 17 is 1, the number of values in the interval 18 to 23 is 2 and the number of values greater than 23 are 3.

COUNTIFS function

Category: Statistical functions

What does this function do?

Count the cells in a range that meet one or more specified conditions.

Syntax:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

• criteria_range1 (required): The first range in which to evaluate the associated criteria.

• criteria1 (required): The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

• criteria_range2, criteria2, ...(optional): Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Examples:

=COUNTIFS(A1:A3, “>1″) = 2 if A1:A3={1,2,3}
=COUNTIFS(B1:B5,1,A1:A5,”>1″) = 1 if B1:B5 ={1;0;0;1;1} and A1:A5 ={0;2;3;4;4}

COUNTA function

Category: Statistical functions

What does this function do?

Count the number of cells in a range that are not empty.

Syntax:

COUNTA(value1, [value2], ...)

• value1 (required): The first argument representing the values that you want to count.

• value2 (optional): Additional arguments representing the values that you want to count, up to a maximum of 255 arguments.

Examples:

=COUNTA(A1:A3,B1) = 3 if A1:A3 = {2,””,4} and B1=3

COUNTBLANK function

Category: Statistical functions

What does this function do?

Count the number of cells in a range that are empty.

Syntax:

COUNTBLANK(value1, [value2], ...)

• value1 (required): The first argument representing the values that you want to count.

• value2 (optional): Additional arguments representing the values that you want to count, up to a maximum of 255 arguments.

Examples:

=COUNTBLANK(A1:A3,B1) = 3 if A1:A3 = {2,””,4} and B1=1

COUNT function

Category: Statistical functions

What does this function do?

Count the number of cells in a range that contain numbers.

Syntax:

COUNT(value1, [value2], ...)

• value1 (required): The first item, cell reference, or range within which you want to count numbers.

• value2 (optional): Up to 255 additional items, cell references, or ranges within which you want to count numbers.

Examples:

=COUNT(A1:A3) = 2 if A1:A3 = {2, A, 4}
=COUNT({1,"E",3}) = 2

COUNTIF function

Category: Statistical functions

What does this function do?

Count the cells in a range that meet a specified condition.

Syntax:

COUNTIF(range, criteria)

• range (required): One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

• criteria (required): A number, expression, cell reference, or text string that defines which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

Examples:

=COUNTIF(A1:A3,"> 1") = 2 if A1:A3 = {1,2,3}
=COUNTIF(B1:B3,"count") = 1 if B1:B3 = {"add", "count", "add"}

LARGE function

Category: Statistical functions

 

What does this function do?

Returns the largest k-th value of a data set.

Syntax:

LARGE (array,k)

• Array (required): the array or range of data for which you want to determine the k-th largest value.
• K (required): the position (from the largest) in the array or cell range of data to return.

Examples:

=LARGE({1,2,5,3,6}, 4)= 2

If A1:A5={1,2,5,3,6}

=LARGE({A1:A5, 4)= 2

SMALL function

Category: Statistical functions

 

What does this function do?

Returns the smallest k-th value of a data set.

Syntax:

SMALL (array,k)

• Array (required): the array or range of data for which you want to determine the k-th smallest value.
• K (required): the position (from the smallest) in the array or cell range of data to return.

Examples:

=SMALL({1,2,5,3,6}, 4)= 5

If A1:A5={1,2,5,3,6}

=SMALL({A1:A5, 4)= 5

Mathematical Functions

The Mathematical Functions are used in Excel to perform various arithmetic operations such as addition, subtraction, division and the product of two numbers. They are also used to generate random values, or to round values and to calculate all kinds of subtotals. Here are some of the most commonly used Mathematical Functions:

MOD - Returns the remainder (residue) of the division of two numbers.

MROUND - Rounds a number to the nearest multiple.

RAND- Returns a random number. The number is always greater than (or equal to) zero but less than one.

RANDBETWEEN - Returns a random number between two specified numbers.

ROUND - Rounds a number to the specified number of decimal places.

ROUNDUP - Rounds up a number to the number of decimal places specified.

ROUNDDOWN - Rounds down a number to the number of decimal places specified.

SUBTOTAL - Returns a subtotal in a list or database.

SUM - Returns the sum of a range of cells.

SUMIF - Add the cells specified by a given condition or criteria.

SUMIFS - Add the cells specified by a given set of conditions or criterias.

SUMPRODUCT - Multiplies two or more arrays and returns the sum of the products.

SUBTOTAL function

Category: Mathematical functions

What does this function do?

Returns a subtotal in a list or database.

Syntax:

SUBTOTAL(function_num, ref1, ref2, ...)

• function_num (required): is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.
• ref1 (required): first range or reference for which you want the subtotal.
• ref2 (required): are 2 to 254 ranges or references for which you want the subtotal.

Function_num (includes hidden values)	Function_num (ignores hidden values)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Examples:

If A1:A5 = {1,3,5,7,15}

=SUBTOTAL(1,A1:A5) = Average range A1:A5 = 6.2
=SUBTOTAL(9, A1:A5) = Sum of the range A1:A5 = 31

SUMIFS function

Category: Mathematical functions

What does this function do?

Add the cells specified by a given set of conditions or criterias.

Syntax:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

• sum_range (required): One or more cells to sum, including numbers or names, ranges, or cell references that contain numbers. Blank and text values are ignored.

• criteria_range1 (required): The first range in which to evaluate the associated criteria.

• criteria1 (required): The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32."

• criteria_range2, criteria2, …(optional): Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Examples:

Imagine we have the following range A1: C3

2	5	10
3	3	25
4	2	36

=SUMIFS(A1:A3,B1:B3, ">2") = Sum the values in A1: A3 values provided in B1: B3 that are higher than 2 = 5

=SUMIFS (A1: A3, B1: B3, ">2", C1: C3,25) = Sum the values in A1: A3 values provided in B1: B3 that are higher than the values 2 and C1: C3 that are equal to 25 = 3

SUMIF function

Category: Mathematical functions

What does this function do?

Add the cells specified by a given condition or criteria.

Syntax:

SUMIF(range, criteria, [sum_range])

• range (required): The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

• criteria (required): The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. For example, criteria can be expressed as 32, ">32", B5, 32, "32", "apples", or TODAY().

• sum_range (optional): The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

Examples:

Imagine we have the following range A1: B3

2	5
3	3
4	2

=SUMIF(A1:A3 ">2") = Sum the values in A1:A3 which are greater than 2 = 7
=SUMIF(A1:A3, ">2", B1: B3) = Sum values B1:B3 as long as the values in A1: A3 are greater than 2 = 5

SUM function

Category: Mathematical functions

What does this function do?

Returns the sum of a range of cells.

Syntax:

SUM(number1,[number2],...])

• number1 (required): The first number argument that you want to add.
• number2 (optional): Number arguments 2 to 255 that you want to add.

Examples:

= SUM (2,3,5) = 10

If A1 = 2, A2 = 3, B3 = 5

= SUM (A1,A2,B3) = 10