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