|
|
|
|
|
|
|
|
|
|
Note:
|
This spreadsheet is deprecated and kept for
archival purpose only. For the most up-to-date
documentation, see:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Function
|
Synopsis
|
Example
|
Result
|
Description
|
|
Test Data
|
|
|
- Commonly Used -
|
|
|
|
|
|
|
|
|
AND
|
=AND(b1, b2, ...)
|
=AND(TRUE,FALSE)
|
FALSE
|
AND implements the logical AND function: the result is TRUE if all of the expressions
evaluate to TRUE, otherwise it returns FALSE.
|
|
Blue
|
1
|
|
IF
|
=IF(condition,if-true,if-false)
|
=IF(FALSE,TRUE,FALSE)
|
FALSE
|
IF evaluates condition. If condition returns a non-zero value the result of the IF
expression is the if-true expression, otherwise IF evaluates to the value of if-false.
|
|
White
|
2
|
|
OR
|
=OR(b1, b2, ...)
|
=OR(3>4,4<3)
|
FALSE
|
OR implements the logical OR function: the result is TRUE if any of the values
evaluated to TRUE.
|
|
Red
|
5
|
|
HLOOKUP
|
=HLOOKUP(value,range,row[,approximate])
|
=HLOOKUP("Blue",G3:H5,2,FALSE)
|
White
|
HLOOKUP function finds the col in range that has a first row cell similar to value.
|
|
|
|
|
LOOKUP
|
=LOOKUP(value,vector1[,vector2])
|
=LOOKUP("White",G3:G5,H3:H5)
|
2
|
LOOKUP function finds the row index of value in vector1 and returns the contents of
vector2 at that row index.
|
|
|
|
|
VLOOKUP
|
=VLOOKUP(value,range,column[,approximate])
|
=VLOOKUP("Red",G3:H5,2,FALSE)
|
#N/A
|
VLOOKUP function finds the row in range that has a first column similar to value.
|
|
|
|
|
COUNTIF
|
=COUNTIF(range,criteria)
|
=COUNTIF(H3:H5,"<4")
|
2
|
COUNTIF function counts the number of cells in the given range that meet the given criteria.
|
|
|
|
|
ROUND
|
=ROUND(number)
|
=ROUND(5.5)
|
6
|
ROUND function rounds a given number.
|
|
|
|
|
SQRT
|
=SQRT(x)
|
=SQRT(2)
|
1.4142135623731
|
SQRT function returns the square root of x.
|
|
|
|
|
SUM
|
=SUM(value1,value2,...)
|
=SUM(H3:H5)
|
8
|
SUM computes the sum of all the values and cells referenced in the argument list.
|
|
|
|
|
SUMIF
|
=SUMIF(range,criteria)
|
=SUMIF(H3:H5,"<4")
|
3
|
SUMIF function sums the values in the given range that meet the given criteria.
|
|
|
|
|
AVERAGE
|
=AVERAGE(value1,value2,...)
|
=AVERAGE(H3:H5)
|
2.66666666666667
|
AVERAGE computes the average of all the values and cells referenced in the argument list.
|
|
|
|
|
MAX
|
=MAX(b1,b2,...)
|
=MAX(H3:H5)
|
5
|
MAX returns the value of the element of the values passed that has the largest value,
with negative numbers considered smaller than positive numbers.
|
|
|
|
|
MEDIAN
|
=MEDIAN(n1,n2,...)
|
=MEDIAN(H3:H5)
|
2
|
MEDIAN returns the median of the given data set.
|
|
|
|
|
MIN
|
=MIN(b1,b2,...)
|
=MIN(H3:H5)
|
1
|
MIN returns the value of the element of the values passed that has the smallest value,
with negative numbers considered smaller than positive numbers.
|
|
|
|
|
STDEV
|
=STDEV(b1,b2,...)
|
=STDEV(A1:A3)
|
2.08166599946613
|
STDEV returns the sample standard deviation of the given sample.
|
|
|
|
|
VAR
|
=VAR(b1,b2,...)
|
=VAR(H3:H5)
|
4.33333333333333
|
VAR calculates sample variance of the given sample. To get the true variance of a
complete population use VARP.
|
|
|
|
|
VARP
|
=VARP(b1,b2,...)
|
=VARP(H3:H5)
|
2.88888888888889
|
VARP calculates the variance of an entire population. VARP is also known as the
N-variance.
|
|
|
|
|
CONCATENATE
|
=CONCATENATE(string1[,string2...])
|
=CONCATENATE("aa","bb")
|
aabb
|
CONCATENATE returns the string obtained by concatenation of the given strings.
|
|
|
|
|
SUBSTITUTE
|
=SUBSTITUTE(text,old,new[,num])
|
=SUBSTITUTE("testing","test","wait")
|
waiting
|
SUBSTITUTE replaces old with new in text. Substitutions are only applied to instance
num of old in text, otherwise every one is changed
|
|
|
|
|
- Information -
|
|
|
|
|
|
|
|
|
ISBLANK
|
|
|
|
|
|
|
|
|
ISEVEN
|
|
|
|
|
|
|
|
|
ISNA
|
|
|
|
|
|
|
|
|
ISNUMBER
|
|
|
|
|
|
|
|
|
ISODD
|
|
|
|
|
|
|
|
|
ISTEXT
|
|
|
|
|
|
|
|
|
N
|
|
|
|
|
|
|
|
|
NA
|
|
|
|
|
|
|
|
|
TYPE
|
|
|
|
|
|
|
|
|
- Bitwise -
|
|
|
|
|
|
|
|
|
BITAND
|
|
|
|
|
|
|
|
|
BITLSHIFT
|
|
|
|
|
|
|
|
|
BITOR
|
|
|
|
|
|
|
|
|
BITRSHIFT
|
|
|
|
|
|
|
|
|
BITXOR
|
|
|
|
|
|
|
|
|
- Logic -
|
|
|
|
|
|
|
|
|
AND
|
=AND(b1, b2, ...)
|
=AND(TRUE,FALSE)
|
FALSE
|
AND implements the logical AND function: the result is TRUE if all of the expressions
evaluate to TRUE, otherwise it returns FALSE.
|
|
|
|
|
FALSE
|
|
|
|
|
|
|
|
|
IF
|
=IF(condition,if-true,if-false)
|
=IF(FALSE,TRUE,FALSE)
|
FALSE
|
IF evaluates condition. If condition returns a non-zero value the result of the IF
expression is the if-true expression, otherwise IF evaluates to the value of if-false.
|
|
|
|
|
NOT
|
|
|
|
|
|
|
|
|
OR
|
=OR(b1, b2, ...)
|
=OR(3>4,4<3)
|
FALSE
|
OR implements the logical OR function: the result is TRUE if any of the values evaluated
to TRUE.
|
|
|
|
|
TRUE
|
|
|
|
|
|
|
|
|
XOR
|
|
|
|
|
|
|
|
|
- Lookup -
|
|
|
|
|
|
|
|
|
ADDRESS
|
|
|
|
|
|
|
|
|
CHOOSE
|
|
|
|
|
|
|
|
|
COLUMN
|
|
|
|
|
|
|
|
|
COLUMNNUMBER
|
|
|
|
|
|
|
|
|
COLUMNS
|
|
|
|
|
|
|
|
|
HLOOKUP
|
=HLOOKUP(value,range,row[,approximate])
|
=HLOOKUP("Blue",G3:H5,2,FALSE)
|
White
|
HLOOKUP function finds the col in range that has a first row cell similar to value.
|
|
|
|
|
INDEX
|
|
|
|
|
|
|
|
|
INDIRECT
|
|
|
|
|
|
|
|
|
LOOKUP
|
=LOOKUP(value,vector1[,vector2])
|
=LOOKUP("White",G3:G5,H3:H5)
|
2
|
LOOKUP function finds the row index of value in vector1 and returns the contents of
vector2 at that row index.
|
|
|
|
|
MATCH
|
|
|
|
|
|
|
|
|
OFFSET
|
|
|
|
|
|
|
|
|
ROW
|
|
|
|
|
|
|
|
|
ROWS
|
|
|
|
|
|
|
|
|
VLOOKUP
|
=VLOOKUP(value,range,column[,approximate])
|
=VLOOKUP("Red",G3:H5,2,FALSE)
|
#N/A
|
VLOOKUP function finds the row in range that has a first column similar to value.
|
|
|
|
|
- Mathematics -
|
|
|
|
|
|
|
|
|
ABS
|
|
|
|
|
|
|
|
|
ACOS
|
|
|
|
|
|
|
|
|
ASIN
|
|
|
|
|
|
|
|
|
ATAN
|
|
|
|
|
|
|
|
|
CEIL
|
|
|
|
|
|
|
|
|
CEILING
|
|
|
|
|
|
|
|
|
COMBIN
|
|
|
|
|
|
|
|
|
COS
|
|
|
|
|
|
|
|
|
COUNTIF
|
=COUNTIF(range,criteria)
|
=COUNTIF(H3:H5,"<4")
|
2
|
COUNTIF function counts the number of cells in the given range that meet the given criteria.
|
|
|
|
|
DEGREES
|
|
|
|
|
|
|
|
|
EVEN
|
|
|
|
|
|
|
|
|
EXP
|
|
|
|
|
|
|
|
|
EXPM1
|
|
|
|
|
|
|
|
|
FACTX
|
|
|
|
|
|
|
|
|
FACTDOUBLE
|
|
|
|
|
|
|
|
|
FIB
|
|
|
|
|
|
|
|
|
FLOOR
|
|
|
|
|
|
|
|
|
G_PRODUCT
|
|
|
|
|
|
|
|
|
GCD
|
|
|
|
|
|
|
|
|
HYPOT
|
|
|
|
|
|
|
|
|
INT
|
|
|
|
|
|
|
|
|
LCM
|
|
|
|
|
|
|
|
|
LN
|
|
|
|
|
|
|
|
|
LN1P
|
|
|
|
|
|
|
|
|
LOG
|
|
|
|
|
|
|
|
|
LOG10
|
|
|
|
|
|
|
|
|
LOG2
|
|
|
|
|
|
|
|
|
MOD
|
|
|
|
|
|
|
|
|
MROUND
|
|
|
|
|
|
|
|
|
ODD
|
|
|
|
|
|
|
|
|
PI
|
|
|
|
|
|
|
|
|
POWER
|
|
|
|
|
|
|
|
|
PRODUCT
|
|
|
|
|
|
|
|
|
QUOTIENT
|
|
|
|
|
|
|
|
|
RADIANS
|
|
|
|
|
|
|
|
|
ROUND
|
=ROUND(number)
|
=ROUND(5.5)
|
6
|
ROUND function rounds a given number.
|
|
|
|
|
ROUNDDOWN
|
|
|
|
|
|
|
|
|
ROUNDUP
|
|
|
|
|
|
|
|
|
SIGN
|
|
|
|
|
|
|
|
|
SIN
|
|
|
|
|
|
|
|
|
SQRT
|
=SQRT(x)
|
=SQRT(2)
|
1.4142135623731
|
SQRT function returns the square root of x.
|
|
|
|
|
SQRTPI
|
|
|
|
|
|
|
|
|
SUM
|
=SUM(value1,value2,...)
|
=SUM(H3:H5)
|
8
|
SUM computes the sum of all the values and cells referenced in the argument list.
|
|
|
|
|
SUMA
|
|
|
|
|
|
|
|
|
SUMIF
|
=SUMIF(range,criteria)
|
=SUMIF(H3:H5,"<4")
|
3
|
SUMIF function sums the values in the given range that meet the given criteria.
|
|
|
|
|
SUMSQ
|
|
|
|
|
|
|
|
|
TAN
|
|
|
|
|
|
|
|
|
TRUNC
|
|
|
|
|
|
|
|
|
- Statistics -
|
|
|
|
|
|
|
|
|
AVEDEV
|
|
|
|
|
|
|
|
|
AVERAGE
|
=AVERAGE(value1,value2,...)
|
=AVERAGE(H3:H5)
|
2.66666666666667
|
AVERAGE computes the average of all the values and cells referenced in the argument list.
|
|
|
|
|
AVERAGEA
|
|
|
|
|
|
|
|
|
COUNT
|
|
|
|
|
|
|
|
|
COUNTA
|
|
|
|
|
|
|
|
|
DEVSQ
|
|
|
|
|
|
|
|
|
GEOMEAN
|
|
|
|
|
|
|
|
|
HARMEAN
|
|
|
|
|
|
|
|
|
LARGE
|
|
|
|
|
|
|
|
|
MAX
|
=MAX(b1,b2,...)
|
=MAX(H3:H5)
|
5
|
MAX returns the value of the element of the values passed that has the largest value,
with negative numbers considered smaller than positive numbers.
|
|
|
|
|
MAXA
|
|
|
|
|
|
|
|
|
MEDIAN
|
=MEDIAN(n1,n2,...)
|
=MEDIAN(H3:H5)
|
2
|
MEDIAN returns the median of the given data set.
|
|
|
|
|
MIN
|
=MIN(b1,b2,...)
|
=MIN(H3:H5)
|
1
|
MIN returns the value of the element of the values passed that has the smallest value,
with negative numbers considered smaller than positive numbers.
|
|
|
|
|
MINA
|
|
|
|
|
|
|
|
|
MODE
|
|
|
|
|
|
|
|
|
PERMUT
|
|
|
|
|
|
|
|
|
RANK
|
|
|
|
|
|
|
|
|
SMALL
|
|
|
|
|
|
|
|
|
STANDARDIZE
|
|
|
|
|
|
|
|
|
STDEV
|
=STDEV(b1,b2,...)
|
=STDEV(A1:A3)
|
2.08166599946613
|
STDEV returns the sample standard deviation of the given sample.
|
|
|
|
|
STDEVA
|
|
|
|
|
|
|
|
|
STDEVP
|
|
|
|
|
|
|
|
|
STDEPA
|
|
|
|
|
|
|
|
|
SUBTOTAL
|
|
|
|
|
|
|
|
|
VAR
|
=VAR(b1,b2,...)
|
=VAR(H3:H5)
|
4.33333333333333
|
VAR calculates sample variance of the given sample. To get the true variance of a
complete population use VARP.
|
|
|
|
|
VARA
|
|
|
|
|
|
|
|
|
VARP
|
=VARP(b1,b2,...)
|
=VARP(H3:H5)
|
2.88888888888889
|
VARP calculates the variance of an entire population. VARP is also known as the
N-variance.
|
|
|
|
|
VARPA
|
|
|
|
|
|
|
|
|
- String -
|
|
|
|
|
|
|
|
|
ASC
|
|
|
|
|
|
|
|
|
CHAR
|
|
|
|
|
|
|
|
|
CLEAN
|
|
|
|
|
|
|
|
|
CODE
|
|
|
|
|
|
|
|
|
CONCATENATE
|
=CONCATENATE(string1[,string2...])
|
=CONCATENATE("aa","bb")
|
aabb
|
CONCATENATE returns the string obtained by concatenation of the given strings.
|
|
|
|
|
EXACT
|
|
|
|
|
|
|
|
|
FIND
|
|
|
|
|
|
|
|
|
LEFT
|
|
|
|
|
|
|
|
|
LEN
|
|
|
|
|
|
|
|
|
LENB
|
|
|
|
|
|
|
|
|
LOWER
|
|
|
|
|
|
|
|
|
MID
|
|
|
|
|
|
|
|
|
PROPER
|
|
|
|
|
|
|
|
|
REPLACE
|
|
|
|
|
|
|
|
|
REPT
|
|
|
|
|
|
|
|
|
RIGHT
|
|
|
|
|
|
|
|
|
SEARCH
|
|
|
|
|
|
|
|
|
SUBSTITUTE
|
=SUBSTITUTE(text,old,new[,num])
|
=SUBSTITUTE("testing","test","wait")
|
waiting
|
SUBSTITUTE replaces old with new in text. Substitutions are only applied to instance
num of old in text, otherwise every one is changed
|
|
|
|
|
T
|
|
|
|
|
|
|
|
|
TRIM
|
|
|
|
|
|
|
|
|
UNICHAR
|
|
|
|
|
|
|
|
|
UNICODE
|
|
|
|
|
|
|
|
|
UPPER
|
|
|
|
|
|
|
|
|
VALUE
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note:
|
This spreadsheet is deprecated and kept for
archival purpose only. For the most up-to-date
documentation, see:
|
|
|
|
|
|
|