Calculation

• AVERAGE – returns the average
• CEILING – rounds a number up
• CONCAT – concatenates a list or range of text strings
• COUNT – counts the number of cells
• COUNTA – counts the number of cells (excl. empty)
• COUNTBLANK – counts the number of empty cells
• FLOOR – rounds a number down
• MAX – returns the largest value
• MIN – returns the smallest number
• POWER – returns the result of a number raised to a power
• SUBTOTAL – returns a subtotal in a list or database
• SUM – adds all the numbers

Text

• REPLACE – replaces part of a text
• SUBSTITUTE – replaces existing text with new text
• TRIM – removes all spaces from text (excl. single spaces between words)
• LEFT – returns the specified number of characters from the start of a text string
• RIGHT – returns the specified number of characters from the end of a text string
• MID – returns the characters from the middle of a text string, given a starting position and length
• LEN – returns the number of characters in a text string
• UPPER – converts a text string to all uppercase letters
• LOWER – converts all letters in a text string to lowercase
• PROPER – converts a text string to proper case

Date and Time

• TODAY – returns the current date formatted as a date
• DAY – returns the day of the month
• NOW – returns the current date and time formatted as a date and time
• SECOND – returns the second
• DATEDIF – calculates the date difference (start date,end date,”d”)

Lookup

• VLOOKUP – looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.
• HLOOKUP – looks for a value in the top row of a table and returns the value in the same column from a row you specify

Condition

• IF – checks whether a condition is met, and returns one value if TRUE, and another value if FALSE