Supported Functions
Subset supports the most commonly used formulas that work in other spreadsheet software.
If you have trouble using these formulas on your own, every formula can be found as a subset, which also shows you the various ways you can use the formulas. Clone the formula subset into your workspace and link it to your existing blocks.
For example if you want to save time figuring out the LEFT and FIND formula to extract certain text from a string, you can clone the LEFT, RIGHT, MID Formula to Find Text subset directly into your workspace.
DATE
Calculates a date specified by year, month, day, and displays it in the cell's formatting.
DATE(Year; Month; Day)
Date and time
DATE(2016,3,14)
DATE(B1,C2,D3)
DATEDIF
Calculates distance between two dates, in provided unit parameter.
DATEDIF(Date1; Date2; Units)
Date and time
DATEDIF(B1,C2,"YM")
DATEDIF("3/14/2016","6/15/2025","Y")
DAY
Returns the day of the given date value.
DAY(Number)
Date and time
DAY(A2)
14
DAYS
Calculates the difference between two date values.
DAYS(Date2; Date1)
Date and time
DAYS(B1,C2)
DAYS("6/15/2025","3/14/2016")
DAYS360
Calculates the difference between two date values in days, in 360-day basis.
DAYS360(Date2; Date1[; Format])
Date and time
EDATE
Shifts the given startdate by given number of months.
EDATE(Startdate; Months)
Date and time
EDATE(B1,C2)
EDATE("3/14/2016",6)
EOMONTH
Returns the date of the last day of a month which falls months away from the start date.
EOMONTH(Startdate; Months)
Date and time
EOMONTH(B1,1)
EOMONTH(”3/14/2016”,1)
HOUR
Returns hour component of given time.
HOUR(Time)
Date and time
HOUR(B1)
INTERVAL
Returns interval string from given number of seconds.
INTERVAL(Seconds)
Date and time
INTERVAL(B1)
MINUTE
Returns minute component of given time.
MINUTE(Time)
Date and time
MINUTE(B1)
MONTH
Returns the month for the given date value.
MONTH(Number)
Date and time
MONTH(B1)
MONTH(”3/14/2016”)
NETWORKDAYS
Returns the number of working days between two given dates.
NETWORKDAYS(Date1; Date2; [Holidays])
Date and time
NETWORKDAYS(B1,C2)
NETWORKDAYS("6/15/2025","3/14/2016")
NOW
Returns current date + time.
NOW()
Date and time
NOW()
SECOND
Returns second component of given time.
SECOND(Time)
Date and time
SECOND(B1)
TIME
Calculates time from given hour, minute and second.
TIME(Hour; Minute; Second)
Date and time
TIME(B1,C2,D3)
TIME(10,10,31)
TODAY
Returns current date.
TODAY()
Date and time
TODAY()
WEEKDAY
Computes a number between 1-7 representing the day of week.
WEEKDAY(Date; Type)
Date and time
WEEKDAY(B1)
WEEKDAY(”3/14/2016”)
WEEKNUM
Returns a week number that corresponds to the week of year.
WEEKNUM(Date; Type)
Date and time
WEEKNUM(B1)
WORKDAY
Returns the working day number of days from start day.
WORKDAY(Date, Shift[; Holidays])
Date and time
WORKDAY(B1,1)
44635
YEAR
Returns the year as a number according to the internal calculation rules.
YEAR(Number)
Date and time
YEAR(B1)
YEAR(”3/14/2016”)
YEARFRAC
Computes the difference between two date values, in fraction of years.
YEARFRAC(Date2; Date1[; Format])
Date and time
YEARFRAC(B1,C2)
YEARFRAC("6/15/2025","3/14/2016")
ISBINARY
Returns TRUE if provided value is a valid binary number.
ISBINARY(Value)
Information
ISBINARY(B1)
ISBINARY(1)
ISBLANK
Returns TRUE if the reference to a cell is blank.
ISBLANK(Value)
Information
ISBLANK(B1)
ISERR
Returns TRUE if the value is error value except #N/A!.
ISERR(Value)
Information
ISERR(B1)
ISERROR
Returns TRUE if the value is general error value.
ISERROR(Value)
Information
ISERROR(B1)
ISEVEN
Returns TRUE if the value is an even integer, or FALSE if the value is odd.
ISEVEN(Value)
Information
ISEVEN(B1)
ISEVEN(2)
ISFORMULA
Checks whether referenced cell is a formula.
ISFORMULA(Value)
Information
ISFORMULA(B1)
ISLOGICAL
Tests for a logical value (TRUE or FALSE).
ISLOGICAL(Value)
Information
ISLOGICAL(B1)
ISNA
Returns TRUE if the value is #N/A! error.
ISNA(Value)
Information
ISNA(B1)
ISNONTEXT
Tests if the cell contents are text or numbers, and returns FALSE if the contents are text.
ISNONTEXT(Value)
Information
ISNONTEXT(B1)
ISNUMBER
Returns TRUE if the value refers to a number.
ISNUMBER(Value)
Information
ISNUMBER(B1)
ISODD
Returns TRUE if the value is odd, or FALSE if the number is even.
ISODD(Value)
Information
ISODD(B1)
ISREF
Returns TRUE if provided value is #REF! error.
ISREF(Value)
Information
ISREF(B1)
ISTEXT
Returns TRUE if the cell contents refer to text.
ISTEXT(Value)
Information
ISTEXT(B1)
FV
Returns the future value of an investment.
FV(Rate; Nper; Pmt[; Pv;[ Type]])
Financial
FV(B1,C2,D3)
FV(6%,10,-200,-500,1)
NPV
Returns net present value.
NPV(Rate; Value1; ...; Value30)
Financial
NPV(B1,C2:F2)
NPV(6%,-300,200,200,200)
PMT
Returns the periodic payment for a loan.
PMT(Rate; Nper; Pv[; Fv[; Type]])
Financial
PMT(B1,C2,D3)
PMT(6%,10,5000,500,1)
PV
Returns the present value of an investment.
PV(Rate; Nper; Pmt[; Fv[; Type]])
Financial
PV(B1,C2,D3)
PV(6%,10,-200,-500,1)
RATE
Returns the interest rate per period of an annuity.
RATE(Nper; Pmt; Pv[; Fv[; Type[; guess]]])
Financial
RATE(B1,C2,D3)
PV(10,200,500)
CUMIPMT
Returns the cumulative interest paid on a loan between a start period and an end period
CUMIPMT(Rate; Nper; Pv; Start, End; type)
Financial
AND
Returns TRUE if all arguments are TRUE.
AND(Logicalvalue1; Logicalvalue2 ...Logicalvalue30)
Logical
AND(B1>C2,B1<D3)
AND(B1>5,B1<50)
FALSE
Returns the logical value FALSE.
FALSE()
Logical
FALSE()
IF
Specifies a logical test to be performed.
IF(Test; Then value; Otherwisevalue)
Logical
IF(B1>=C2,D3,E4)
IF(B1>=70,"Pass","Fail")
IFNA
Returns the value if the cell does not contains the #N/A (value not available) error value, or the alternative value if it does.
IFNA(Value; Alternate_value)
Logical
IFNA(B1,C2)
IFNA(B1,”Missing“)
IFERROR
Returns the value if the cell does not contains an error value, or the alternative value if it does.
IFERROR(Value; Alternate_value)
Logical
IFERROR(B1,C2)
IFERROR(B1,”Missing“)
NOT
Complements (inverts) a logical value.
NOT(Logicalvalue)
Logical
NOT(AND(B1>5,B1<50))
SWITCH
Evaluates a list of arguments, consisting of an expression followed by a value.
SWITCH(Expression1, Value1[, Expression2, Value2[..., Expression_n, Value_n]])
Logical
SWITCH(B1,1,"BEAR",2,"BASE",3,"BULL","EMPTY")
OR
Returns TRUE if at least one argument is TRUE.
OR(Logicalvalue1; Logicalvalue2 ...Logicalvalue30)
Logical
OR(B1>C2,B1<D3)
OR(B1>5,B1<50)
TRUE
The logical value is set to TRUE.
TRUE()
Logical
TRUE()
CHOOSE
Uses an index to return a value from a list of up to 30 values.
CHOOSE(Index; Value1; ...; Value30)
Lookup and reference
CHOOSE(B1,C2,D3,E4)
CHOOSE(3,C2,D3,E4)
COLUMN
Returns column number of a given reference or formula reference if argument not provided.
COLUMNS([Reference])
Lookup and reference
COLUMN()
COLUMNS
Returns the number of columns in the given reference.
COLUMNS(Array)
Lookup and reference
COLUMNS(C2:F2)
FORMULATEXT
Returns a formula in a given cell as a string.
FORMULATEXT(Reference)
Lookup and reference
FORMULATEXT(B1)
HLOOKUP
Searches horizontally with reference to adjacent cells to the bottom.
HLOOKUP(Search_Criterion; Array; Index; Sort_Order)
Lookup and reference
HLOOKUP(B1,B5:E9,C2)
HLOOKUP(100,B5:E9,4)
INDEX
Returns the content of a cell, specified by row and column number, or an optional range name.
INDEX(Reference; Row; Column; Range)
Lookup and reference
INDEX(B1:C3,A2,A3)
MATCH
Returns the relative position of an item in an array that matches a specified value.
MATCH(Searchcriterion; Lookuparray; Type)
Lookup and reference
MATCH(B1,C1:C3,1)
OFFSET
Returns the value of a cell offset by a certain number of rows and columns from a given reference point.
OFFSET(Reference; Rows; Columns; Height; Width)
Lookup and reference
OFFSET(B1,C2,D3)
OFFSET(B1,6,2)
ROW
Returns row number of a given reference or formula reference if argument not provided.
ROW([Reference])
Lookup and reference
ROW()
ROWS
Returns the number of rows in the given reference.
ROWS(Array)
Lookup and reference
ROWS(B1:B8)
VLOOKUP
Searches vertically with reference to adjacent cells to the right.
VLOOKUP(Search_Criterion; Array; Index; Sort_Order)
Lookup and reference
VLOOKUP(B1,B5:E9,C2)
VLOOKUP(100,B5:E9,4)
ABS
Returns the absolute value of a number.
ABS(Number)
Math and trigonometry
ABS(B1)
COMBIN
Returns number of combinations (without repetitions).
COMBIN(Number; Number)
Math and trigonometry
COMBIN(B2,C3)
COMBIN(2,6)
COMBINA
Returns number of combinations (with repetitions).
COMBINA(Number; Number)
Math and trigonometry
COMBINA(B2,C3)
COMBINA(2,6)
COUNTUNIQUE
Counts the number of unique values in a list of specified values and ranges.
COUNTUNIQUE(Value1, [Value2, ...])
Math and trigonometry
COUNTUNIQUE(B2,C3)
COUNTUNIQUE(A1:D1)
POWER
Returns a number raised to another number.
POWER(Base; Exponent)
Math and trigonometry
POWER(B2,C3)
POWER(2,6)
PRODUCT
Returns product of numbers.
PRODUCT(Number1; Number2; ...; Number30)
Math and trigonometry
PRODUCT(B2,C3)
PRODUCT(2,6)
RAND
Returns a random number between 0 and 1.
RAND()
Math and trigonometry
RAND()
RANDBETWEEN
Returns a random integer between two numbers.
RAND(Lowerbound; Upperbound)
Math and trigonometry
RANDBETWEEN(B1,C2)
RANDBETWEEN(1,50)
ROUND
Rounds a number to a certain number of decimal places. Count Key: -2 Round to the nearest 100 -1 - Round to the nearest 10 0- Round to nearest whole number 1 Round to nearest 1 Decimal place 2 - Round to nearest 2 Decimal place
ROUND(Number; Count)
Math and trigonometry
ROUND(B1,C2)
ROUNDDOWN
Rounds a number down, toward zero, to a certain precision. Count Key: -2 Round to the nearest 100 -1 - Round to the nearest 10 0- Round to nearest whole number 1 Round to nearest 1 Decimal place 2 - Round to nearest 2 Decimal place
ROUNDDOWN(Number; Count)
Math and trigonometry
ROUNDDOWN(B1,C2)
ROUNDUP
Rounds a number up, away from zero, to a certain precision. Count Key: -2 Round to the nearest 100 -1 - Round to the nearest 10 0- Round to nearest whole number 1 Round to nearest 1 Decimal place 2 - Round to nearest 2 Decimal place
ROUNDUP(Number; Count)
Math and trigonometry
ROUNDUP(B1,C2)
SUM
Adds all the numbers in a range of cells.
SUM(Number1; Number2; ...; Number30)
Math and trigonometry
SUM(C2:F2)
SUMIF
Adds the cells specified by given criteria.
SUMIF(Range; Criteria; Sumrange)
Math and trigonometry
SUMIF(B1:B5,A1)
SUMIF(B1:B5,A1,C1:C5)
SUMIFS
Returns the sum of the values of cells in a range that meets multiple criteria in multiple ranges.
SUMIFS(Sum_Range ; Criterion_range1 ; Criterion1 [ ; Criterion_range2 ; Criterion2 [;...]])
Math and trigonometry
SUMIFS(D1:D5,E1:E5,A1,F1:F5,B1)
SUMPRODUCT
Multiplies corresponding elements in the given arrays, and returns the sum of those products.
SUMPRODUCT(Array1; Array2...Array30)
Math and trigonometry
SUMPRODUCT(C2:F2,C3:F3)
AVERAGE
Returns the average of the arguments.
AVERAGE(Number1; Number2; ...Number30)
Statistical
AVERAGE(C2:F2)
AVERAGEIF
Returns the arithmetic mean of all cells in a range that satisfy a given condition.
AVERAGEIF(Range; Criterion [; Average_Range ])
Statistical
AVERAGEIF(C2:F2,">1")
AVERAGEIF(C2:F2,">"&C1)
CONFIDENCE
Returns upper confidence bound for normal distribution.
CONFIDENCE(Alpha; Stdev; Size)
Statistical
CONFIDENCE(B1,C2,D3)
CONFIDENCE(0.05,0.07,100)
CORREL
Returns the correlation coefficient between two data sets.
CORREL(Data1; Data2)
Statistical
CORREL(C2:F2,C3:F3)
COUNT
Counts how many numbers are in the list of arguments.
COUNT(Value1; Value2; ... Value30)
Statistical
COUNT(C2:F2)
COUNTA
Counts how many values are in the list of arguments.
COUNTA(Value1; Value2; ... Value30)
Statistical
COUNTA(C2:F2)
COUNTBLANK
Returns the number of empty cells.
COUNTBLANK(Range)
Statistical
COUNTBLANK(C2:F2)
COUNTIF
Returns the number of cells that meet with certain criteria within a cell range.
COUNTIF(Range; Criteria)
Statistical
COUNTIF(C2:F2,">1")
COUNTIF(C2:F2,">"&C1)
COUNTIFS
Returns the count of rows or columns that meet criteria in multiple ranges.
COUNTIFS(Range1; Criterion1 [; Range2; Criterion2 [; ...]])
Statistical
COUNTIFS(C1:F1,C2:F2,">1")
COUNTIFS(C1:F1,C2:F2,">"&C1)
LARGE
Returns k-th largest value in a range.
LARGE(Range; K)
Statistical
LARGE(C2:F2,B1)
LARGE(C2:F2,2)
MAX
Returns the maximum value in a list of arguments.
MAX(Number1; Number2; ...Number30)
Statistical
MAX(B1,C2,D3,E4)
MAX(C2:F2)
MEDIAN
Returns the median of a set of numbers.
MEDIAN(Number1; Number2; ...Number30)
Statistical
MEDIAN(B1,C2,D3,E4)
MEDIAN(C2:F2)
MIN
Returns the minimum value in a list of arguments.
MIN(Number1; Number2; ...Number30)
Statistical
MIN(B1,C2,D3,E4)
MIN(C2:F2)
SMALL
Returns k-th smallest value in a range.
SMALL(Range; K)
Statistical
SMALL(C2:F2,B1)
SMALL(C2:F2,2)
STDEV
Returns standard deviation of a sample.
STDEV(Value1; Value2; ... Value30)
Statistical
STDEV(C2:F2)
STDEV(100,700,200,200)
CHAR
Converts a number into a character according to the current code table.
CHAR(Number)
Text
CHAR(B1)
CHAR(97)
CONCATENATE
Combines several text strings into one string.
CONCATENATE("Text1"; ...; "Text30")
Text
CONCATENATE(B1,C2)
CONCATENATE(50,"cent")
EXACT
Returns TRUE if both text strings are exactly the same.
EXACT(Text; Text)
Text
EXACT(B1,C2)
FIND
Returns the location of one text string inside another.
FIND( "Text1"; "Text2"[; Number])
Text
FIND(” “,B1)
FIND(” “,”Hello World”)
LEFT
Extracts a given number of characters from the left side of a text string.
LEFT("Text"; Number)
Text
LEFT(B1,5)
LEFT(”Hello World”,5)
LEN
Returns length of a given text.
LEN("Text")
Text
LEN(B1)
LEN(”Hello World”)
LOWER
Returns text converted to lowercase.
LOWER(Text)
Text
LOWER(B1)
LOWER(”HELLO WORLD”)
MID
Returns substring of a given length starting from Start_position.
MID(Text, Start_position, Length)
Text
MID(B1,C2,D3)
MID(B1,1,1)
PROPER
Capitalizes words given text string.
PROPER("Text")
Text
PROPER(B1)
PROPER("hello world")
REPLACE
Replaces substring of a text of a given length that starts at given position.
REPLACE(Text; Start_position; Length; New_text)
Text
REPLACE(B1,1,5,"Goodbye")
REPLACE("Hello World",1,5,"Goodbye")
REPT
Repeats text a given number of times.
REPT("Text"; Number)
Text
REPT(B1,3)
REPT(”Hello World”,2)
RIGHT
Extracts a given number of characters from the right side of a text string.
RIGHT("Text"; Number)
Text
RIGHT(B1,5)
RIGHT(”Hello World”,5)
SEARCH
Returns the location of one text string inside another. (Allows the use of wildcards.)
SEARCH( "Text1"; "Text2"[; Number])
Text
SEARCH(" ",B1)
SEARCH(" ",”Hello World”)
SUBSTITUTE
Returns string where occurrences of Old_text are replaced by New_text. Replaces only specific occurrence if last parameter is provided.
SUBSTITUTE(Text; Old_text; New_text; [Occurrence])
Text
SUBSTITUTE(A1,"New York","Big Apple")
T
Returns text if given value is text, empty string otherwise.
T(Value)
Text
T(B1)
TEXT
Converts a number into text according to a given format.
TEXT(Number; Format)
Text
TEXT(B1,"$0.00")
TEXT(B1,"0.00")
TRIM
Strips extra spaces from text.
TRIM("Text")
Text
TRIM(B1)
TRIM(" hello world ")
UPPER
Returns text converted to uppercase.
UPPER(Text)
Text
UPPER(B1)
UPPER("hello world")
Last updated