DATE

Calculates a date specified by year, month, day, and displays it in formatting of the cell.

DATE(Year,Month,Day)

DATEDIF

Calculates the distance between two dates, in the specified unit.

DATEDIF(Date1,Date2,Units)

DAYS

Calculates the difference between two date values.

DAYS(TODAY(), DATE(yyyy,mm,dd))

TODAY

Returns the current date.

TODAY()

WEEKDAY

Computes a number between 1-7 indicating the day of week.

WEEKDAY(Date,Type)

WEEKNUM

Returns a week number that corresponds to the calendar week of year.

WEEKNUM(Date,Type)

AND

Returns TRUE if all arguments are TRUE.

AND(Logicalvalue1,Logicalvalue2 …Logicalvalue30)

FALSE

Returns the logical value FALSE.

FALSE()

IF

IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result.
For example, to “pass” scores above 10:
=IF(C1>10, “Pass”, “Fail”).
More than one condition can be tested by nesting IF functions.

IF(Test,Then value,Otherwisevalue)

IFNA

Returns the value if the cell does not contain the #N/A (value not available) error value, or the alternative value if it does.

IFNA(Value,Alternate_value)

IFERROR

Returns the value if the cell does not contain an error value, or the alternative value if it does.

IFERROR(Value,Alternate_value)

NOT

Complements (inverts) a logical value.

NOT(Logicalvalue)

SWITCH

Evaluates a list of arguments, consisting of an expression followed by a value.

SWITCH(Expression1,Value1[, Expression2,Value2[…, Expression_n,Value_n]])

OR

Returns TRUE if at least one argument is TRUE.

OR(Logicalvalue1,Logicalvalue2 …Logicalvalue30)

TRUE

The logical value is set to TRUE.

TRUE()

XOR

Returns true if an odd number of arguments evaluates to TRUE.

XOR(Logicalvalue1,Logicalvalue2 …Logicalvalue30)

CHOOSE

Uses an index to return a value from a list of up to 30 values.

CHOOSE(Index,Value1,…,Value30)

COLUMN

Returns the column number of a given reference or a formula reference if no argument is given.

COLUMNS([Reference])

COLUMNS

Returns the number of columns in the given reference.

COLUMNS(Array)

FORMULATEXT

Returns a formula in a given cell as a string.

FORMULATEXT(Reference)

HLOOKUP

Searches down horizontally with reference to adjacent cells.

HLOOKUP(Search_Criterion,Array, Index,Sort_Order)

INDEX

Returns a value from a range of cells by row and column number.

INDEX(Range,Value)

MATCH

Returns the relative position of an element in an array that matches a specified value.

MATCH(Searchcriterion, Lookuparray,Type)

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)

ROW

Returns the row number of a given reference or formula reference if argument not given.

ROW([Reference])

ROWS

Returns the number of rows in the given reference.

ROWS(Array)

VLOOKUP

Searches vertically with reference to adjacent cells to the right.

VLOOKUP(Search_Criterion,Array,Index,Sort_Order)

ABS

Returns the absolute value of a number.

ABS(Number)

CEILING

Rounds up a number to the nearest multiple of significance.

CEILING(Number,Significance)

COUNTUNIQUE

Counts the number of unique values in a list of specified values and ranges.

COUNTUNIQUE(Value1,[Value2, …])

DECIMAL

Converts a text with characters from a number system to a positive integer in the specified base radix.

DECIMAL(“Text”,Radix)

EVEN

Rounds a positive number up to the nearest even integer and rounds a negative number down to the nearest even integer.

EVEN(Number)

FLOOR

Rounds a number down to the nearest multiple of significance.

FLOOR(Number,Significance)

GCD

Calculates the greatest common divisor of numbers.

GCD(Number1,Number2,…)

INT

Rounds a number down to the nearest integer.

INT(Number)

MOD

Returns the remainder when one integer is divided by another.

MOD(Dividend,Divisor)

PI

Returns 3.14159265358979, the value of the mathematical constant PI with 14 decimal places.

PI()

POWER

Returns a number that has been increased by another number.

POWER(Base,Exponent)

PRODUCT

Returns the product of numbers.

PRODUCT(Number1,Number2, …,Number30)

QUOTIENT

Returns the integer part of a division.

QUOTIENT(Dividend,Divisor)

RAND

Returns a random number between 0 and 1.

RAND()

RANDBETWEEN

Returns a random integer between two numbers.

RAND(Lowerbound,Upperbound)

ROMAN

Converts a number to Roman form.

ROMAN(Number,[Mode])

ROUND

Rounds a number to a certain number of decimal places.

ROUND(Number,Count)

ROUNDDOWN

Rounds a number down, toward zero, to a certain precision.

ROUNDDOWN(Number,Count)

ROUNDUP

Rounds a number up, away from zero, to a certain precision.

ROUNDUP(Number,Count)

SQRT

Returns the positive square root of a number.

SQRT(Number)

SUM

Returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination.

Arguments:
number1 – The first value to sum. number2 – [optional] The second value to sum.
number3 – [optional] The third value to sum.

Example:
=SUM(A1,A2,A3,A4,A5) returns sum of all these cells
=SUM(A1:A5) returns sum of cells from A1 to A5

Quick usage (auto function): https://share.vidyard.com/watch/BM1Mtk84Uj9n9kbPCtcpbY?

SUM(number1,number2,…, number30)

SUMIF

Adds cells specified by given criteria.

SUMIF(Range,Criteria,Sumrange)

SUMIFS

It is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when corresponding cells meet criteria based on dates, numbers, and text.

Arguments:
sum_range – The range to be summed.
range1 – The first range to evaluate.
criteria1 – The criteria to use on range1.
range2 – [optional] The second range to evaluate.
criteria2 – [optional] The criteria to use on range2.

Examples:
=SUMIFS(F5:F15,C5:C15,”red”) // color=”red” =SUMIFS(F5:F15,C5:C15,”red”,D5:D15,”TX”) // color=”red”, state=”TX”

SUMIFS returns a sum of values in column F where the color in column C is “red”.
In the second example, SUMIFS sums values in column F when the color is “red” and the state is Texas (TX)

SUMIFS(sum_Range, Criterion_range1,Criterion1[ ; Criterion_range2,Criterion2 [,…]])

SUMPRODUCT

Multiplies the corresponding elements in the given arrays and returns the sum of these products.

SUMPRODUCT(Array1,Array2,…,Array30)

TRUNC

Truncates a number by removing decimal places.

TRUNC(Number,Count)

AVERAGE

Returns the average of the arguments.
Example:
=AVERAGE(C1:C3) returns the average of cell numbers from C1 to C3;
Quick usage (auto function): https://share.vidyard.com/watch/eZ2DgyRQYxVE7H1LTqPng5?

AVERAGE(Number1,Number2, …Number30)

AVERAGEIF

Returns the arithmetic average of all cells in a range that satisfy a given condition.

AVERAGEIF(Range,Criterion[, Average_Range])

COUNT

Counts how many numbers are in the list of arguments.
Example:
=COUNT(1,2,3) // returns 3
=COUNT(A1:A100) // count numbers in A1:A10

COUNT(Value1,Value2,…,Value30)

COUNTA

Counts (numbers and text) how many values are in the list of arguments.
Example:
=COUNTA(1,2,3) // returns 3 =COUNTA(1,”a”,”b”) // returns 3
=COUNTA(A1:A10) // count non-empty cells in A1:A10

COUNTA(Value1,Value2,… Value30)

COUNTBLANK

Returns the number of empty cells.
Example:
=COUNTBLANK(B5:B15) // returns 3 as there are 3 empty cells between B5 – B15

COUNTBLANK(Range)

COUNTIF

Returns the number of cells that meet with certain criteria within a cell range.

COUNTIF(Range,Criteria)

COUNTIFS

Returns the number of rows or columns that meet the criteria in multiple ranges.

COUNTIFS(Range1,Criterion1[, Range2,Criterion2[,…]])

MAX

Returns the maximum value in a list of arguments.
Example:
=MAX(12,17,25,11,23) // returns 25;
Quick usage (auto function): https://share.vidyard.com/watch/mqYbmRhbw9Mg49CbQSBH7a?

MAX(Number1,Number2,…Number30)

MEDIAN

Returns the median of a set of numbers.

MEDIAN(Number1,Number2, …Number30)

MIN

Returns the minimum value in a list of arguments.
Example:
=MIN(12,17,25,11,23) // returns 11;
Quick usage (auto function): https://share.vidyard.com/watch/fWgrwnC1Wmc1KNC3yz8Fr4?

MIN(Number1,Number2,…Number30)

CHAR

Converts a number into a character according to the current code table.

CHAR(Number)

CLEAN

Returns text that has been “cleaned” of line breaks and other non-printable characters.

The CLEAN function accepts a text string and returns text that has been “cleaned” of line breaks and other non-printable characters.
You can use CLEAN to strip non-printing characters and strip line breaks from text.
Example:
=TRIM(CLEAN(A1)) // clean and remove extra space

CLEAN(“Text”)

CODE

Returns a numeric code for the first character in a text string.
Example:
CODE(“a”) returns the code 97.

CODE(“Text”)

CONCATENATE

Concatenates (joins) up to 30 values together and returns the result as text.
Pay attention to the space character, comma, etc. must be enclosed in quotes.

Example:

=CONCATENATE(A1, “, “,B1)

CONCATENATE(“Text1”,…, “Text30”)

EXACT

Compares two text strings, considering upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.

Example:
=EXACT(“apple”,”apple”) // returns TRUE =EXACT(“Apple”,”apple”) // returns FALSE

EXACT(“Text_1”,”Text_2”)

FIND

Returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.
[start_num] – optional
Example:

To return the position of the letter “A” in the word “Apple”:
=FIND(“A”,”Apple”) returns 1

=FIND (“find_text”, “within_text”, [start_num])

LEFT

Extracts a given number of characters from the left side of a supplied text string.
Example, LEFT(“apple”,3) returns “app”.

LEFT(“Text”,Number)

LEN

Returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.

Example:
=LEN(“Lorem ipsum dolor”) → 17
=LEN(1000) → 4

LEN(“Text”)
LEN(number)

LOWER

Returns the text converted to lowercase.
=LOWER(“Apple”)

LOWER(“Text”)

MID

Returns a substring of a given length starting from Start_position.

Arguments:

• text – the text to extract from

• start_position – the location of the first character to extract

• length – the number of characters to extract

  Example:
  =MID(“apple”, 2,3) returns ppl

MID(“Text”,Start_position,Length)

PROPER

Capitalizes words in a given text string.

Arguments:

• Text – The text that should be converted to proper case.

• Example:
  =PROPER(“apple”) returns Apple

PROPER(“Text”)

REPLACE

Replaces substring of text of a given length that starts at given position.
Arguments:

• text – The text to replace.

• start_position – The starting location in the text to search.

• length – The number of characters to replace.

• new_text – The text to replace old_text with.
  Example:
  =REPLACE(“XRT-2017”, 5,4, 2018) returns XRT-2018

REPLACE(“Text”,Start_position,Length,New_text) or REPLACE(Number,Start_position,Length,New_text)

REPT

Repeats the text a given number of times.
Example:
=REPT(“Google”, 3) as a result:
GoogleGoogleGoogle

REPT(“Text”,Number)

RIGHT

Extracts a given number of characters from the right side of a text string.
Example:
Google (A4 cell)
=RIGHT(A4, 3)
Result: gle
or just =RIGHT(“Google”, 3)

RIGHT(“Text”,Number)

SEARCH

Returns the position of one text string inside another. (Allows the use of wildcards.)

SEARCH (search_for, text_to_search, [starting_at])

• search_for – The string to look for within text_to_search.

• text_to_search – The text to search for the first occurrence of search_for.

• starting_at – [ OPTIONAL – 1 by default ] – The character within text_to_search at which to start the search.

Example:
=SEARCH(“n”,A2)

=SEARCH(“wood”,”How much wood can a woodchuck chuck”,14)

SEARCH(“Text1”,”Text2”,[ Number])

SPLIT

Divides the provided text using space character as a separator and returns the substring at zero-based position specified by the second argument.

E.g.:

• SPLIT(“Lorem ipsum dolor”, 0) -> “Lorem”

• SPLIT(“Lorem ipsum dolor”, 1) -> “ipsum”

• SPLIT(“Lorem ipsum dolor”, 2) -> “dolor”

• SPLIT(“Lorem ipsum dolor”, 3) -> #VALUE

SPLIT(“Text”, nu,ber_of_position)

SUBSTITUTE

Returns a string in which all occurrences of Old_text are replaced by New_text. Replaces only specific occurrence if the last parameter is provided.
Example: word “tuttle” is in A4 cell:
=SUBSTITUTE(A4, “t”, “b”) As a result we have “bubble”

SUBSTITUTE(Text,Old_text,New_text,[Occurrence])

T

Returns text if the given value is text, otherwise an empty string.
For example, word text is in B1 cell:
=T(B1) returns text

T(<cell>)

TEXT

Converts a number into text according to a specified format.
Note! The format should be inside ““
For example, number 5 (A1 cell) into 5%:
=TEXT(A1,”0%”) or just number =TEXT(5,”0%”)

TEXT(Number,”<format>”)

TRIM

Removes extra spaces from text.
Example:
=TRIM(” Turn off in time. “) // returns “Turn off in time.”

TRIM(“Text”) or TRIM(Number)

UNICHAR

Returns the character created by using the provided code point.

UNICHAR(Number)

UNICODE

Returns the Unicode code point of a first character of a text.

UNICODE(Text)

UPPER

Returns the text converted to uppercase.

UPPER(Text)