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