CATEGORY
FUNCTION MEANING STRUCTURE NOTES HOW TO USE "" WHAT IF EMPTY CELLS OR
CELLS WITHOUT NUMBERS?
Date and time
DATE To take three separate values and
combine them to form a date DATE(year,month ,day)
don't use it with the IF-functions (use
"03/02/2020" instead); you can also put
negative or out-of-range numbers -
Empty cells are considered (they return
0), cells without numbers give an error
Date and time
DATEDIF To calculate the number of days,
months, or years between two dates DATEDIF(start_date,end_date,unit)
The unit can be "Y", "M", "D" (for the
difference in days it's better to use DAYS);
it can't give you negative numbers Put number or the date into ""
Only the start date can be empty (it
returns 0), cells without numbers give
an error
Date and time DAY To have the day of a date DAY(serial_number) Put number or the date into ""
Empty cells are considered (they return
0), cells without numbers give an error
Date and time
DAYS To have the number of days between
two dates DAYS(end_date, start_date)
It calculates only the difference in days,
but it doesn't consider hours, minutes and
seconds (for this just subtract the two
dates) Put number or the date into ""
Empty cells are considered (they return
0), cells without numbers give an error
Date and time HOUR To have the hour of a time value HOUR(serial_number) Put number or the date into ""
Empty cells are considered (they return
0), cells without numbers give an error
Date and time MINUTE To have the minute of a time value MINUTE(serial_number) Put number or the date into ""
Empty cells are considered (they return
0), cells without numbers give an error
Date and time MONTH To have the month of a date MONTH(serial_number) Put number or the date into ""
Empty cells are considered (they return
0), cells without numbers give an error
Date and time
NOW
To have the current date and time NOW() --
Date and time SECOND To have the second of a time value SECOND(serial_number) Put number or the date into ""
Empty cells are considered (they return
0), cells without numbers give an error
Date and time
TODAY
To have the current date TODAY() - -
Date and time WEEKDAY
To have the a day of the week of a
date WEEKDAY(serial_number,[return_type])
It starts from Sunday which is 1; you can
put out-of-range numbers Put number or the date into ""
Empty cells are considered (they return
0), cells without numbers give an error
Date and time YEAR To have the year of a date YEAR(serial_number) Put number or the date into ""
Empty cells are considered (they return
0), cells without numbers give an error
Financial
FV
To have the future value of an
investment based on periodic,
constant payments and constant
interest rate
FV(rate,nper,pmt,[pv],[type]) If you have the yearly values but the
amount you invest (pmt) is monthly:
rate/12 and nper*12 -
Empty cells are considered (they return
0), cells without numbers give an error
Financial
PMT
To calculates the payment for a loan
based on constant interest rate and
constant payments
PMT(rate, nper, pv, [fv], [type])
If you have the yearly values and you
want to know the montly installment:
rate/12 and nper*13 -
Empty cells are considered (they return
0), cells without numbers give an error
Logical AND
When many conditions have to be true
at the same time
AND(logical1, [logical2], ...) Use "" only for text -
Logical IF To have one value if a condition is true
and another value if it's false
IF(logical_test, value_if_true,
[value_if_false])
Use "" only for text -
Logical
IFERROR
To have one value if there's an error IFERROR(value, value_if_error) Use "" only for text -
Logical OR
To determine if any conditions in a test
are true
OR(logical1, [logical2], ...) Use "" only for text -
Lookup and
reference
MATCH To look up values in a reference or
array
MATCH(lookup_value, lookup_array,
[match_type])
It gives you a
number
that can substitute
the col_index_num in the VLOOKUP
function Use "" only for text -
Lookup and
reference
VLOOKUP
To look in the
first
column of an array
and moves across the row to return
the value of a cell
VLOOKUP (lookup_value, table_array,
col_index_num, [range_lookup])
if we chose TRUE the values of the first
column of the table must be in ascending
order Use "" only for text -
Math ABS To find the absolute value of a number ABS(number) -
Empty cells are considered (they return
0), cells without numbers give an error
Math SUM To sum values SUM(number1,[number2],...) -
Empty cells and cells without numbers
are not considered
Math SUMIF To sum the values in cells that meet a
criterion
SUMIF(range, criteria, [sum_range])
Sum_range
does not
have to be the same
size and shape as range
Use "" with text,
equations/inequalities and dates
Empty cells and cells without numbers
are not considered
Math SUMIFS To sum the values in cells that meet
multiple criteria (max 127 criteria)
SUMIFS(sum_range, criteria_range1,
criteria1, [criteria_range2, criteria2], ...)
Sum_range
must
be the same size and
shape as range
Use "" with text,
equations/inequalities and dates
Empty cells and cells without numbers
are not considered
Math SUMPRODUCT To have the sum of the products of
corresponding array components
SUMPRODUCT(array1, [array2], [array3], ...)
The array arguments must have the same
dimensions -
Empty cells and cells without numbers
are not considered
Statistical AVERAGE To find the average of a numbers AVERAGE(number1, [number2], ...) -
Empty cells and cells without numbers
are not considered
Statistical AVERAGEIF To find the average of numbers that
meet a certain criterion
AVERAGEIF(range, criteria, [average_range])
Average_range
does not
have to be the
same size and shape as range
Use "" with text,
equations/inequalities and dates
Empty cells and cells without numbers
are not considered
Statistical
AVERAGEIFS To find the average of numbers that
meet multiple criteria
AVERAGEIFS(average_range,
criteria_range1, criteria1, [criteria_range2,
criteria2], ...)
Average_range must be the same size and
shape as criteria_range
Use "" with text,
equations/inequalities and dates
Empty cells and cells without numbers
are not considered
Statistical
COUNT To count the number of cells that
contain numbers (every format) COUNT(value1, [value2], ...)
Arguments that are numbers, dates, or a
text representation of numbers (for
example, a number enclosed in quotation
marks, such as "1") are counted - -
Statistical
COUNTA To count the number of non-empty
cells COUNTA(value1, [value2], ...)
The COUNTA function counts cells
containing any type of information
(including error values and empty text
("")) - -
Statistical
COUNTBLANK To count the number of empty cells in
a range COUNTBLANK(range)
If you see that the cell is empty, it will be
counted (doesn't matter if it actually
contains a formula) - -
Statistical COUNTIF To count the number of cells that
meet a criterion
COUNTIF(range, criteria)
Use "" with text,
equations/inequalities and dates -
Statistical COUNTIFS To count the number of cells that
meet multiple criteria
COUNTIFS(criteria_range1, criteria1,
[criteria_range2, criteria2]…)
Each range
must
have the same size and
shape as the criteria_range1
Use "" with text,
equations/inequalities and dates -
Statistical MAX To find the maximum value MAX(number1, [number2], ...) -
Empty cells and cells without numbers
are not considered
Statistical MIN To find the minimum value MIN(number1, [num ber2], ...) -
Empty cells and cells without numbers
are not considered
Statistical
RANK.EQ To have the rank of a number in a list
of numbers RANK.EQ(number,ref,[order])
number is just one cell, ref is a range of
cells; if two numbers have the same rank
the following rank is skipped -
Empty cells and cells without numbers
are not considered
Text
CONCATENATE
To connect two or more text strings CONCATENATE(text1, [text2], ...) You can also use & to concatenate Use "" only for text Emp ty cells return an empty cell
Text
LEFT
To have the first character or
characters in a text string, based on
the number of characters you specify LEFT(text, [num_chars])
If num_chars is omitted, it is assumed to
be 1 Use "" only for text Empty cells return an empty cell
Text LEN
To have the
number
of characters in a
text string LEN(text) Spaces count as characters Use "" only for text Empty cells return 0
Text
LOWER
To convert text to lowercase LOWER(text) Use "" only for text Empty cells return an empty cell
Text
MID
To have a specific number of
characters from a text string, starting
at the position you specify, based on
the number of characters you specify MID(text, start_num, num_chars) Use "" only for text Empty cells return an empty cell
Text
PROPER
To capitalize the first letter of each
word and convert all other letters to
lowercase letter PROPER(text) Use "" only for text Empty cells return an empty cell
Text
RIGHT
To have the last character or
characters in a text string, based on
the number of characters you specify
RIGHT(text,[num_chars]) If num_chars is omitted, it is assumed to
be 1 Use "" only for text Empty cells return an empty cell
Text
SEARCH
To find one text within a second text,
and return the number of the starting
position of the first text from the first
character of the second text SEARCH(find_text,within_text,[start_num ])
If the start_num argument is omitted, it is
assumed to be 1 Use "" only for text -
Text
TRIM
Remove useless spaces
TRIM(text)
Use "" only for text Empty cells return an empty cell
Text
UPPER
To convert text to uppercase UPPER(text) Use "" only for text Empty cells return an empty cell
