Loading…
Loading…
Every function used in the course, with its syntax and the lessons where you practise it.
SUM(number1, [number2], …)
Adds all the numbers in a range.
SUMIF(range, criteria, [sum_range])
Adds the cells that meet one condition.
SUMIFS(sum_range, criteria_range1, criteria1, …)
Adds the cells that meet several conditions.
SUMPRODUCT(array1, [array2], …)
Multiplies matching items and returns the sum of those products.
ROUND(number, num_digits)
Rounds a number to a given number of digits.
ROUNDDOWN(number, num_digits)
Rounds a number down, toward zero.
FLOOR(number, significance)
Rounds down to the nearest multiple of significance.
CEILING(number, significance)
Rounds up to the nearest multiple of significance.
ABS(number)
Returns the absolute value of a number.
SUBTOTAL(function_num, ref1, …)
Sum, count, average… that skips filtered-out rows (109 = SUM).
COUNT(value1, [value2], …)
Counts cells that contain numbers.
COUNTA(value1, [value2], …)
Counts cells that are not empty.
COUNTBLANK(range)
Counts empty cells in a range.
COUNTIF(range, criteria)
Counts cells that meet one condition.
COUNTIFS(criteria_range1, criteria1, …)
Counts cells that meet several conditions.
AVERAGEIF(range, criteria, [average_range])
Averages cells that meet a condition.
MIN(number1, [number2], …)
Returns the smallest value.
MAX(number1, [number2], …)
Returns the largest value.
LARGE(array, k)
Returns the k-th largest value.
SMALL(array, k)
Returns the k-th smallest value.
AVERAGEIFS(average_range, criteria_range1, criteria1, …)
Averages cells that meet several conditions.
MAXIFS(max_range, criteria_range1, criteria1, …)
Largest value among cells that meet conditions.
MINIFS(min_range, criteria_range1, criteria1, …)
Smallest value among cells that meet conditions.
RANK.EQ(number, ref, [order])
Position of a number in a list (0 = largest first).
IF(logical_test, value_if_true, [value_if_false])
Returns one value if a test is TRUE and another if FALSE.
IFS(test1, value1, [test2, value2], …)
Checks conditions in order and returns the first TRUE match.
IFERROR(value, value_if_error)
Returns a fallback when a formula errors.
SWITCH(expression, value1, result1, …, [default])
Matches an expression against a list of values.
AND(logical1, [logical2], …)
TRUE if all arguments are TRUE.
OR(logical1, [logical2], …)
TRUE if any argument is TRUE.
XOR(logical1, [logical2], …)
TRUE if an odd number of arguments are TRUE.
NOT(logical)
Reverses TRUE and FALSE.
ISERROR(value)
TRUE if the value is any error.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Looks down the first column of a table and returns a value from another column.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Looks across the first row of a table and returns a value from another row.
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Finds a value in one range and returns the matching item from another.
MATCH(lookup_value, lookup_array, [match_type])
Returns the position of a value in a range.
INDEX(array, row_num, [column_num])
Returns the value at a given row and column of a range.
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Position of a value in a list; exact match by default.
LEN(text)
Returns the number of characters.
LEFT(text, [num_chars])
Returns characters from the start of the text.
RIGHT(text, [num_chars])
Returns characters from the end of the text.
MID(text, start_num, num_chars)
Returns characters from the middle of the text.
FIND(find_text, within_text, [start_num])
Case-sensitive position of text within text.
SEARCH(find_text, within_text, [start_num])
Case-insensitive position of text; supports wildcards.
CONCATENATE(text1, [text2], …)
Joins text items (legacy).
TEXTJOIN(delimiter, ignore_empty, text1, …)
Joins text with a delimiter.
SUBSTITUTE(text, old_text, new_text, [instance_num])
Replaces existing text with new text.
TEXTBEFORE(text, delimiter, [instance_num])
Returns text before a delimiter.
TEXTAFTER(text, delimiter, [instance_num])
Returns text after a delimiter.
DATE(year, month, day)
Builds a date from year, month and day.
DATEDIF(start_date, end_date, unit)
Difference between two dates in "Y", "M" or "D".
DAYS(end_date, start_date)
Number of days between two dates.
WORKDAY(start_date, days, [holidays])
Date a number of working days away.
YEAR(serial_number)
Returns the year of a date.
MONTH(serial_number)
Returns the month of a date.
DAY(serial_number)
Returns the day of a date.
WEEKDAY(serial_number, [return_type])
Day of the week as a number (2 = Monday is 1).
NETWORKDAYS(start_date, end_date, [holidays])
Working days between two dates.
FILTER(array, include, [if_empty])
Returns rows that meet a condition (spills).
SORT(array, [sort_index], [sort_order])
Sorts a range (spills).
SEQUENCE(rows, [columns], [start], [step])
Generates a list of numbers (spills).
LET(name1, value1, …, calculation)
Names intermediate results inside one formula.
LAMBDA(parameter1, …, calculation)
Builds a reusable custom function; give it a name in Define Name.
PMT(rate, nper, pv, [fv], [type])
Payment for a loan with constant payments and rate.
NPV(rate, value1, [value2], …)
Net present value of future cash flows.
IRR(values, [guess])
Internal rate of return for cash flows.
FV(rate, nper, pmt, [pv], [type])
Future value of regular payments.
PV(rate, nper, pmt, [fv], [type])
Present value of regular payments.
NPER(rate, pmt, pv, [fv], [type])
Number of payment periods.
RATE(nper, pmt, pv, [fv], [type], [guess])
Interest rate per period.