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.

Name
Description
Syntax
Type
Example 1
Example 2

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