Numeric functions
This cheat sheet provides a quick reference guide for various mathematical functions commonly used in data analysis and programming. Each function is accompanied by its syntax, a sample usage, and a brief description.
ABS
The ABS function returns the distance of the number from zero on the number line, ensuring that the result is non-negative.
Syntax
ABS(number)
Sample
ABS(10.35) => 10.35
ABS(-15) => 15
ADD
The ADD function computes the total of multiple numbers provided as arguments.
Syntax
ADD(number1, [number2, ...])
Sample
ADD(5, 7) => 12
ADD(-10, 15, 20) => 25
AVG
The AVG function calculates the mean of a set of numerical values.
Syntax
AVG(number1, [number2, ...])
Sample
AVG(10, 20, 30) => 20
AVG(-5, 5) => 0
CEILING
The CEILING function rounds a number up to the nearest integer greater than or equal to the input.
Syntax
CEILING(number)
Sample
CEILING(8.75) => 9
CEILING(-15.25) => -15
COUNT
The COUNT function calculates the number of numeric arguments provided.
Syntax
COUNT(number1, [number2, ...])
Sample
COUNT(1, 2, "abc", 3) => 3
COUNT(-5, 0, "$abc", 5) => 3
COUNTA
The COUNTA function counts the number of non-empty arguments provided.
Syntax
COUNTA(value1, [value2, ...])
Sample
COUNTA(1, "", "text") => 2
COUNTA("one", "two", "three") => 3
COUNTALL
The COUNTALL function calculates the total number of arguments, both numeric and non-numeric.
Syntax
COUNTALL(value1, [value2, ...])
Sample
COUNTALL(1, "", "text") => 3
COUNTALL("one", "two", "three") => 3
EVEN
The EVEN function rounds positive values up to the nearest even number and negative values down to the nearest even number.
Syntax
EVEN(number)
Sample
EVEN(7) => 8
EVEN(-5) => -6
EXP
The EXP function returns 'e' raised to the power of a given number.
Syntax
EXP(number)
Sample
EXP(2) => 7.38905609893065
EXP(-1) => 0.36787944117144233
FLOOR
The FLOOR function rounds a number down to the nearest integer.
Syntax
FLOOR(number)
Sample
FLOOR(8.75) => 8
FLOOR(-15.25) => -16
INT
The INT function truncates the decimal part, returning the integer portion of a number.
Syntax
INT(number)
Sample
INT(8.75) => 8
INT(-15.25) => -15
LOG
The LOG function computes the logarithm of a number to a specified base. (default = e).
Syntax
LOG([base], number)
Sample
LOG(10, 100) => 2
LOG(2, 8) => 3
MAX
The MAX function identifies the highest value from a set of numbers.
Syntax
MAX(number1, [number2, ...])
Sample
MAX(5, 10, 3) => 10
MAX(-10, -5, -20) => -5
MIN
The MIN function identifies the lowest value from a set of numbers.
Syntax
MIN(number1, [number2, ...])
Sample
MIN(5, 10, 3) => 3
MIN(-10, -5, -20) => -20
MOD
The MOD function calculates the remainder when dividing (integer division) one number by another.
Syntax
MOD(number1, number2)
Sample
MOD(10, 3) => 1
MOD(-15, 4) => -3
ODD
The ODD function rounds positive values up to the nearest odd number and negative values down to the nearest odd number.
Syntax
ODD(number)
Sample
ODD(6) => 7
ODD(-5.5) => -7
POWER
The POWER function raises a given base to a specified exponent.
Syntax
POWER(base, exponent)
Sample
POWER(2, 3) => 8
POWER(10, -2) => 0.01
ROUND
The ROUND function is used to round a number to a specified number of decimal places (precision). Default value for precision is 0.
Syntax
ROUND(number, [precision])
Sample
ROUND(8.765, 2) => 8.77
ROUND(-15.123, 1) => -15.1
ROUNDDOWN
The ROUNDDOWN function rounds a number down to a specified number of decimal places (precision). Default value for precision is 0.
Syntax
ROUNDDOWN(number, [precision])
Sample
ROUNDDOWN(8.765, 2) => 8.76
ROUNDDOWN(-15.123, 1) => -15.2
ROUNDUP
The ROUNDUP function rounds a number up to a specified number of decimal places (precision). Default value for precision is 0.
Syntax
ROUNDUP(number, [precision])
Sample
ROUNDUP(8.765, 2) => 8.77
ROUNDUP(-15.123, 1) => -15.1
SQRT
The SQRT function calculates the square root of a given number.
Syntax
SQRT(number)
Sample
SQRT(25) => 5
SQRT(2) => 1.4142135623730951
VALUE
The VALUE function is used to extract the numeric value from a string (after handling % or - accordingly).
Syntax
VALUE(text)
Sample
VALUE("123$") => 123
VALUE("USD -45.67") => -45.67