Math & Stats functions

Reference for all 40 Math & Stats functions in Kazi Sheets, with syntax and examples.

Add, count, average, round and summarise numbers. Kazi Sheets has 40 Math & Stats functions. Type = in a cell, start typing a name, and Kazi suggests it.

#ABS

Returns the absolute value of a number.

Syntax

ABS(number)

Example

=ABS(-45000)

#AVERAGE

Calculates the arithmetic mean of values in a range.

Syntax

AVERAGE(range)

Example

=AVERAGE(B2:D2)

#CEILING

Rounds a number up to the nearest multiple of significance.

Syntax

CEILING(number, [significance])

Example

=CEILING(2.5, 1)

#CORREL

Returns the Pearson correlation coefficient between two data sets.

Syntax

CORREL(array1, array2)

Example

=CORREL(B2:B10, C2:C10)

#COUNT

Counts how many cells in a range contain numbers.

Syntax

COUNT(range)

Example

=COUNT(B2:E4)

#COUNTA

Counts how many cells in a range are not empty.

Syntax

COUNTA(range)

Example

=COUNTA(A2:A10)

#COUNTBLANK

Counts empty cells in a specified range of cells.

Syntax

COUNTBLANK(range)

Example

=COUNTBLANK(B2:D10)

#DIFF

Subtracts subsequent numbers or range values from the first number.

Syntax

DIFF(num1, num2) or DIFF(range)

Example

=DIFF(B4, B3)

#DIVIDE

Divides one number by another.

Syntax

DIVIDE(numerator, denominator)

Example

=DIVIDE(B2, C2)

#EXP

Returns e raised to the power of a given number.

Syntax

EXP(number)

Example

=EXP(1)

#FLOOR

Rounds a number down to the nearest multiple of significance.

Syntax

FLOOR(number, [significance])

Example

=FLOOR(2.5, 1)

#LARGE

Returns the k-th largest value in a dataset.

Syntax

LARGE(range, k)

Example

=LARGE(B2:B20, 2)

#LN

Returns the natural logarithm of a number.

Syntax

LN(number)

Example

=LN(2.71828)

#LOG

Returns the logarithm of a number to the specified base.

Syntax

LOG(number, [base])

Example

=LOG(100, 10)

#MAX

Returns the largest number in a range.

Syntax

MAX(range)

Example

=MAX(B2:D2)

#MEDIAN

Returns the middle value in a numeric dataset.

Syntax

MEDIAN(range)

Example

=MEDIAN(B2:E2)

#MIN

Returns the smallest number in a range.

Syntax

MIN(range)

Example

=MIN(B2:D2)

#MOD

Returns the remainder after a number is divided by a divisor.

Syntax

MOD(number, divisor)

Example

=MOD(10, 3)

#MODE

Returns the most frequently occurring value in a range.

Syntax

MODE(range)

Example

=MODE(B2:B10)

#MROUND

Rounds a number to the nearest specified multiple.

Syntax

MROUND(number, multiple)

Example

=MROUND(10, 3)

#PERCENTILE

Returns the k-th percentile of values in a range (0 to 1).

Syntax

PERCENTILE(range, k)

Example

=PERCENTILE(B2:B50, 0.9)

#PI

Returns the mathematical constant pi (3.14159...).

Syntax

PI()

Example

=PI()

#POWER

Returns the result of a number raised to a power.

Syntax

POWER(base, exponent)

Example

=POWER(5, 2)

#PRODUCT

Multiplies all numbers given as arguments.

Syntax

PRODUCT(range1, [range2])

Example

=PRODUCT(B2:B5)

#QUARTILE

Returns the quartile of a data set (0=min, 1=25%, 2=median, 3=75%, 4=max).

Syntax

QUARTILE(range, quart)

Example

=QUARTILE(B2:B50, 3)

#QUOTIENT

Returns the integer portion of a division.

Syntax

QUOTIENT(numerator, denominator)

Example

=QUOTIENT(10, 3)

#RAND

Returns an evenly distributed random real number between 0 and 1.

Syntax

RAND()

Example

=RAND()

#RANDBETWEEN

Returns a random integer number between the numbers you specify.

Syntax

RANDBETWEEN(bottom, top)

Example

=RANDBETWEEN(1, 100)

#RANK

Returns the rank of a number in a list of numbers.

Syntax

RANK(number, ref, [order])

Example

=RANK(B2, B2:B20)

#ROUND

Rounds a number to a specified number of decimal places.

Syntax

ROUND(value, [decimals])

Example

=ROUND(AVERAGE(B2:D2), 1)

#ROUNDDOWN

Rounds a number down, toward zero.

Syntax

ROUNDDOWN(value, [decimals])

Example

=ROUNDDOWN(3.1415, 2)

#ROUNDUP

Rounds a number up, away from zero.

Syntax

ROUNDUP(value, [decimals])

Example

=ROUNDUP(3.1415, 2)

#SIGN

Returns 1 for positive, -1 for negative, and 0 for zero.

Syntax

SIGN(number)

Example

=SIGN(B2)

#SMALL

Returns the k-th smallest value in a dataset.

Syntax

SMALL(range, k)

Example

=SMALL(B2:B20, 1)

#SQRT

Returns the positive square root of a number.

Syntax

SQRT(number)

Example

=SQRT(144)

#STDEV

Calculates sample standard deviation of a dataset.

Syntax

STDEV(range)

Example

=STDEV(B2:D2)

#SUBTRACT

Subtracts all subsequent numbers from the first number.

Syntax

SUBTRACT(num1, num2, ...)

Example

=SUBTRACT(100, 30, 20)

#SUM

Adds all numeric values in a range of cells.

Syntax

SUM(range1, [range2])

Example

=SUM(B2:D2)

#TRUNC

Truncates a number to a specified number of fractional digits.

Syntax

TRUNC(number, [decimals])

Example

=TRUNC(8.9)

#VAR

Estimates variance based on a sample.

Syntax

VAR(range)

Example

=VAR(B2:D2)