excel functions explained...date & time functions date & time functions date() what it does:...
TRANSCRIPT
EXCEL FUNCTIONS EXPLAINED
TEXT FunctionsTEXT FUNCTIONS
FIND()
What it Does:
Finds a specified text string in another text string and
returns its starting position number
Syntax:
=FIND(find_text, within_text, [start_num])
©trumpexcel.com
Notes:
• If the start number is not specified, then it starts looking from the beginning of the string.• The search is case-sensitive.• FIND function can not handle wildcard characters.
• If you want to use it with wildcard characters, use the SEARCH function• It returns a #VALUE! error if the searched string is not found in the text.
LEFT()
What it Does:
Returns the number of specified characters from the
beginning of the text string
Syntax:
=LEFT(text, [num_chars])
©trumpexcel.com
Notes:
• If the number of characters is not specified, it returns the left most character.• Spaces are also counted as characters.• It also works with numbers, as it treats numbers as text.
LEN()
What it Does:
Returns the total number of characters in a text string
Syntax:
=LEN(text)
©trumpexcel.com
Notes:• Spaces are counted as characters.• It also works with numbers, as it treats numbers as text.
LOWER()
What it Does:
Converts all uppercase letters in a text string to
lowercase
Syntax:
=LOWER(text)
©trumpexcel.com
Notes:• Characters that are not letters remain unchanged.
MID()
What it Does:
Returns part of a text string. The beginning character
and number of characters are specified by the user
Syntax:
=MID(text, start_num, num_chars)
©trumpexcel.com
Notes:• Spaces are counted as characters.• If the start number is greater than the length of text, MID returns "" (empty text).• Returns an error if:
• Start number is less than 1.• Number of characters is a negative number.
PROPER()
What it Does:
Capitalizes the first character of every word (text
string)
Syntax:
=PROPER(text)
©trumpexcel.com
Notes:• Characters that are not letters remain unchanged.• It capitalizes the first letter of any word that follows a non text character
• For example: =Proper(hello,excel) returns Hello,Excel
REPLACE()
What it Does:
Replaces part of a text string, based on the number of
specified characters, with a different text string
Syntax:
=REPLACE(old_text, start_num, num_chars,
new_text)
©trumpexcel.com
Notes:• Start Number and Number of Characters can not be negative.
REPT()
What it Does:
Repeats the specified text a given number of times
Syntax:
=REPT(text, number_times)
©trumpexcel.com
Notes:• If the number of times repeat value is 0 (zero), it returns "" (empty text).• If the number of times repeat value is not an integer, it is truncated (10.8 becomes 10).• The result of the REPT function cannot be longer than 32,767 characters, or REPT returns a #VALUE! Error.
RIGHT()
What it Does:
Returns the number of specified characters from the
end of the text string
Syntax:
=RIGHT(text, [num_chars])
©trumpexcel.com
Notes:• If number of characters is not specified, it returns the right-most character.• Spaces are counted as characters.• It also works with numbers, as it treats numbers as text.
SEARCH()
What it Does:
Locates one text string within a second text string,
and returns its position
Syntax:
=SEARCH(find_text,within_text,[start_num])
©trumpexcel.com
Notes:• SEARCH function is not case sensitive.
• For case sensitive search, use FIND function.• It can handle wildcard characters.• If the searched value is not found, a #VALUE! error is returned.• If the start number is not mentioned, it starts from the beginning.
SUBSTITUTE()
What it Does:
Substitutes existing text with the new specified text in
a text string
Syntax:
=SUBSTITUTE(text, old_text, new_text,
[instance_num])
©trumpexcel.com
Notes:• If you specify an instance number, then only that instance of old text is replaced. Otherwise, every occurrence of
old text in the text string is changed to the new text.• Best to use when there is a need to change some specific text with some other text.
TEXT()
What it Does:
Converts a numeric value to text and display it in a
specified format
Syntax:
=TEXT(value, format_text)
©trumpexcel.com
Notes:• Useful function when there is a need to show numbers in a format, or a need to combine numbers and
text/symbols.• The numbers are converted to text, and hence can not be used in calculations
• If you need to use these numbers in formulas/calculations, use custom number formatting.
UPPER()
What it Does:
Converts text to uppercase
Syntax:
=UPPER(text)
©trumpexcel.com
Notes:• Characters that are not letters remain unchanged.• Text input can be a cell reference (that has the text) or text string itself.
LOGICAL FunctionsLOGICAL
FUNCTIONS
AND()
What it Does:
Returns TRUE if all its arguments are TRUE; returns
FALSE if one or more arguments are FALSE.
Syntax:
=AND(logical1, [logical2], ...)
©trumpexcel.com
Notes:• Most useful in situation when you want to test multiple conditions and want to know if all are TRUE.• The arguments must either evaluate to logical values (TRUE/FALSE), or the arguments must be arrays/references
of logical values.
FALSE()
What it Does:
Returns the logical value FALSE
Syntax:
=FALSE()
©trumpexcel.com
Notes:• If you type FALSE into a cell, it automatically becomes the logical value FALSE
IF()
What it Does:
Evaluates a condition and returns one value if it’s
TRUE and another if it’s FALSE
Syntax:
=IF(logical_test, [value_if_true], [value_if_false])
©trumpexcel.com
Notes:• A maximum of 64 nested IF conditions can be tested in the formula.• If any of the argument is an array, each element of the array is evaluated.
IFERROR()
What it Does:
If a formula evaluates to an error, it returns the
specified value, else it returns the result of the
formula
Syntax:
=IFERROR(value, value_if_error))
©trumpexcel.com
Notes:• IFERROR is the best way to handle errors.• Error evaluated by IFERROR:
• #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!
NOT()
What it Does:
Reverses the value of a logical argument. Changes
TRUE to FALSE, and FALSE to TRUE
Syntax:
=NOT(logical)
©trumpexcel.com
Notes:• NOT is useful in reversing the logical value
• It makes TRUE a FALSE and a FALSE a TRUE
OR()
What it Does:
Returns TRUE if any argument is TRUE; returns FALSE
if all arguments are FALSE.
Syntax:
=OR(logical1, [logical2], ...)
©trumpexcel.com
Notes:• The arguments must either evaluate to logical values (TRUE/FALSE), or the arguments must be arrays/references
of logical values.• Text and empty cells are ignored.
TRUE()
What it Does:
Returns the logical value TRUE
Syntax:
=TRUE()
©trumpexcel.com
Notes:• If you type True into a cell, it automatically becomes the logical value TRUE
DATE & TIME FunctionsDATE & TIME FUNCTIONS
DATE()
What it Does:
Returns the serial number of a particular date, for
which the day, month, and year are specified
Syntax:
=DATE(year, month, day)
©trumpexcel.com
Notes:• The result displayed would depend on the formatting of the cell
• If the cell is formatted as General, the result is displayed as a date (which is also a serial number)• To get the result as a serial number, change the number formatting to Number
• Best used in cases where year, month, day values are available separately
DATEVALUE()
What it Does:
Converts a date that is stored as text to a serial
number that Excel recognizes as a date
Syntax:
=DATEVALUE(date_text)
©trumpexcel.com
Notes:• DATEVALUE function returns a serial number
• For example: =DATEVALUE(“1/1/2014”) returns 41640 (which is the serial number for this date)• If you do not give a day, and only give month and year, it returns the serial number for the first day of that
month• For example: -DATEVALUE(“1/2014”) would return the serial number 41640 (which represents the
first day of the first month of 2014)• If a year is not provided, excel automatically takes the current year (based on computer systems clock
settings)• The serial number returned by the DATEVALUE function can vary depending on the computer's system date
settings
DAY()
What it Does:
Returns day of the specified date (between 1 to 31).
It takes the serial number of a date as input
Syntax:
=DAY(serial_number)
©trumpexcel.com
Notes:• Apart from serial numbers, DAY function would also work with dates entered as
• A result from some other formula• A date stored as text• A date entered as text in DAY formula (In double quotes)
HOUR()
What it Does:
Returns hour of the specified time (between 0 to 23).
Syntax:
=HOUR(serial_number)
©trumpexcel.com
Notes:• The argument can be provided in different formats
• As a serial number: The integer part of the serial number represents the date, while the decimal portion represents time• If you provide 41640.5 as the serial number, it would return 12 (as the decimal portion 0.5
represents 12 hours)• As text: =HOUR("12:00 PM") would return 12
MINUTE()
What it Does:
Returns minute value of the specified time (between 0
to 59)
Syntax:
=MINUTE(serial_number)
©trumpexcel.com
Notes:• The argument can be provided in different formats
• As a serial number: The integer part of the serial number represents the date, while the decimal portion represents time• If you provide 41640.78125 as the serial number, it would return 45 (as the decimal portion 0.78125
represents 18 hours and 45 minutes)• As text: =MINUTE("41640.78125") would return 45
MONTH()
What it Does:
Returns the month of the specified date. It ranges
from 1 (January) to 12 (December)
Syntax:
=MONTH(serial_number)
©trumpexcel.com
Notes:• Apart from serial numbers, MONTH function would also work with dates entered as
• A result from some other formula• A date stored as text• A date entered as text in MONTH formula (In double quotes)
NETWORKDAYS()
What it Does:
Returns the number of whole working days between
two dates. Working days exclude weekends and any
dates identified in holidays
Syntax:
=NETWORKDAYS(start_date, end_date, [holidays])
©trumpexcel.com
Notes:• Could be used to calculate overtime for employees• Saturday and Sunday are considered as weekends, and are not counted
• In case the weekends are days other than Saturday and Sunday, use NETWORKDAYS.INTL formula• Date can be entered as:
• A result from some other formula• A date stored as text• A date entered as text (In double quotes)
NETWORKDAYS.INTL()
What it Does:
Returns the number of whole workdays between two
dates (weekends can be other than Saturday and
Sunday). Working days exclude weekends and any
dates identified in holidays
Syntax:
=NETWORKDAYS.INTL(start_date, end_date,
[weekend], [holidays])
©trumpexcel.com
Notes:• Could be used to calculate overtime for employees• Date can be entered as:
• A result from some other formula• A date stored as text• A date entered as text in MONTH formula (In double quotes)
• Weekend could any two consecutive days, or any single day of the week.
NOW()
What it Does:
Returns the serial number of the current date and
time
Syntax:
=NOW()
©trumpexcel.com
Notes:• It is a volatile formula (use with caution)
SECOND()
What it Does:
Returns the seconds of a time value (between 0 to
59)
Syntax:
=SECOND(serial_number)
©trumpexcel.com
Notes:• The argument can be provided in different formats
• As a serial number: The integer part of the serial number represents the date, while the decimal portion represents time• If you provide 41640.7813657407 as the serial number, it would return 10 (as the decimal portion
0.7813657407 represents 18 hours, 45 minutes, and 10 seconds)• As text: =SECOND("41640.7813657407") would return 10
TODAY()
What it Does:
Returns the serial number of the current date.
Syntax:
=TODAY()
©trumpexcel.com
Notes:• It is a volatile formula (use with caution)
WEEKDAY()
What it Does:
Returns a number based on the day of the week
Syntax:
=WEEKDAY(serial_number,[return_type])
©trumpexcel.com
Notes:• Returns 1 for Sunday and 7 for Saturday
• You can change this by selecting from various options in return_type section• Apart from serial numbers, WEEKDAY function would also work with dates entered as
• A result from some other formula• A date stored as text• A date entered as text in WEEKDAY formula (In double quotes)
WORKDAY()
What it Does:
Returns the date after or before a given number of
working days
Syntax:
=WORKDAY(start_date, days, [holidays])
©trumpexcel.com
Notes:• Saturday and Sunday are considered as weekends, and are not counted
• In case the weekends are days other than Saturday and Sunday, use NETWORKDAYS.INTL formula• Date can be entered as:
• A result from some other formula• A date stored as text• A date entered as text (In double quotes)
WORKDAY.INTL()
What it Does:
Returns the date after or before a given number of
working days
Syntax:
=WORKDAY.INTL(start_date, days, [weekend],
[holidays])
©trumpexcel.com
Notes:• Date can be entered as:
• A result from some other formula• A date stored as text• A date entered as text (In double quotes)
• Weekend could be any two consecutive days, or any single day of the week.
LOOKUP & REFERENCE Functions
LOOKUP & REFERENCE FUNCTIONS
COLUMN()
What it Does:
Returns the column number of the given cell reference
Syntax:
=COLUMN([reference])
©trumpexcel.com
Notes:• If the cell reference is omitted, it returns the column number of the current cell
COLUMNS()
What it Does:
Returns the number of columns in an array or
reference
Syntax:
=COLUMNS(array)
©trumpexcel.com
Notes:• Even if the array contains multiple rows and columns, only the columns are counted• For example:
• COLUMNS(A1:B1) returns 2• COLUMNS(A1:B100) also returns 2
HLOOKUP()
What it Does:
Looks for value in the top row of a table. Column of
the matching cell is used to return a value based on
the row number specified
Syntax:
=HLOOKUP(lookup_value, table_array,
row_index_num, [range_lookup])
©trumpexcel.com
Notes:• The match could be exact (FALSE or 0 in range_lookup) or approximate (TRUE or 1)• In approximate lookup, make sure that the list is sorted in ascending order (left to right), or else the result could
be inaccurate• When range_lookup is TRUE (approximate lookup) and data is sorted in ascending order
• If the HLOOKUP can not find the value, it returns the largest value, which is less than the Lookup_value• It returns a #N/A error if the lookup_value is smaller than the smallest value• If lookup_value is text, wildcard characters can be used (refer to live example below)
INDEX()
What it Does:
Returns the value from a table, based on the specified
row and column number
Syntax:
=INDEX(array, row_num, [column_num])
©trumpexcel.com
Notes:• If the row number or column number is 0, it returns the values of the entire row or column respectively• If Index function is used in front of a cell reference (such as A1:) it returns a cell reference instead of a value (see
live example below)• Most widely used along with the MATCH function• Unlike VLOOKUP, INDEX function can return a value from the left of the lookup value
INDIRECT()
What it Does:
Returns the reference specified by a text string
Syntax:
=INDIRECT(ref_text, [a1])
©trumpexcel.com
Notes:• It is a volatile formula (use with caution)• The Reference Text (ref_text) could be:
• A reference to a cell that in-turn contains a cell reference in A1-style or R1C1-style format• A reference to a cell in double quotes• A named range that returns a reference
ROW()
What it Does:
Returns the row number of the given cell reference
Syntax:
=ROW([reference])
©trumpexcel.com
Notes:• If the cell reference is omitted, it returns the row number of the current cell
MATCH()
What it Does:
Looks for a value/string in a list, and returns its
position
Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
©trumpexcel.com
Notes:• It is a useful function when you need the position of an item in a list• Most widely used in conjunction with INDEX formula• Match type (match_type) specifies how excel matches the item in a list
• When Match Type is 0• This is the exact match criteria. It returns the first exact match position (or a error if there is no match)• Wildcard characters can be used when lookup value is a text string
• When Match Type is 1 (default)• The data must be sorted in the ascending order for this option. It returns the largest value equal to or less than the lookup value
• When Match Type is -1• The data must be sorted in the descending order for this option. It returns the smallest value equal to or greater than the lookup
value
OFFSET()
What it Does:
Returns a reference to a range that is a specified
number of rows and columns from a cell or range of
cells.
Syntax:
=OFFSET(reference, rows, cols, [height], [width])
©trumpexcel.com
Notes:• It is a volatile formula (use with caution)• OFFSET could return a single cell reference or a range of reference• If height or width value is omitted, it is taken as that of the reference
ROWS()
What it Does:
Returns the number of rows in the given reference or
array
Syntax:
=ROWS(array)
©trumpexcel.com
Notes:• Even if the array contains multiple rows and columns, only the rows are counted• For example:
• ROWS(A1:A2) returns 2• ROWS(A1:Z2) also returns 2
VLOOKUP()
What it Does:
Looks for a given value in a vertical list, and once it
has spotted that value, it would use that row and
return the value from the specified column number
Syntax:
=VLOOKUP(lookup_value, table_array,
col_index_num, [range_lookup])
©trumpexcel.com
Notes:• The V in VLOOKUP stands for Vertical• The match could be exact (FALSE or 0 in range_lookup) or approximate (TRUE or 1)• In approximate VLOOKUP, make sure that the list is sorted in ascending order, or else the result could be
inaccurate• When range_lookup is TRUE (approximate lookup; default if omitted) and data is sorted in ascending order
• If the VLOOKUP can not find the value, it returns the largest value, which is less than the Lookup_value• It returns a #N/A error if the lookup_value is smaller than the smallest value• If lookup_value is text, wildcard characters can be used (refer to live example below)
• VLOOKUP can not look for a value on the left of the lookup value
IS FORMULA FAMILYISBLANK(); ISERROR(); ISNA(); ISNUMBER(); ISODD(); ISEVEN(); ISTEXT()
What it Does:
Checks the specified value and returns TRUE or
FALSE depending on the outcome
Syntax:
=ISBLANK(); ISERROR(); ISNA(); ISNUMBER();
ISODD(); ISEVEN(); ISTEXT()
©trumpexcel.com
Notes:• While Excel automatically converts numerical value in double quotes to a number in formulas, this is not
applicable in IS formulas• For example: ISNUMBER(“10”) would return a FALSE, as 10 in double quotes is treated as text
MATH FunctionsMATH FUNCTIONS
CEILING()
What it Does:
Returns number rounded UP, away from zero, to the
nearest multiple of significance
Syntax:
=CEILING(number, significance)
©trumpexcel.com
Notes:• It returns an error if both the number and significance are negative• If number is negative, and significance is positive, the value is rounded up towards zero• If number is negative, and significance is negative, the value is rounded down, away from zero.
FLOOR()
What it Does:
Rounds number DOWN, toward zero, to the nearest
multiple of significance.
Syntax:
=FLOOR(number, significance)
©trumpexcel.com
Notes:• It returns an error if both the number and significance are negative• If number is negative, and significance is positive, the value is rounded down away from zero• If number is negative, and significance is negative, the value is rounded up, towards zero.
INT()
What it Does:
Rounds a number down to the nearest integer
Syntax:
=INT(number)
©trumpexcel.com
Notes:
EVEN()
What it Does:
Returns number rounded up to the nearest even
integer
Syntax:
=EVEN(number)
©trumpexcel.com
Notes:
ODD()
What it Does:
Returns number rounded up to the nearest odd
integer
Syntax:
=ODD(number)
©trumpexcel.com
Notes:
MOD()
What it Does:
Returns the remainder after number is divided by
divisor. The result has the same sign as divisor.
Syntax:
=MOD(number, divisor)
©trumpexcel.com
Notes:• If divisor is 0, MOD returns the #DIV/0! error value
RAND()
What it Does:
Returns an evenly distributed random real number
greater than or equal to 0 and less than 1. A new
random real number is returned every time the
worksheet is calculated.
Syntax:
=RAND()
©trumpexcel.com
Notes:• If you want to use RAND to generate a random number but don't want the numbers to change every time the cell
is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number
• It is a volatile formula (use with caution)
RANDBETWEEN()
What it Does:
Returns a random integer number between the
numbers you specify. A new random integer number
is returned every time the worksheet is calculated.
Syntax:
=RANDBETWEEN(bottom, top)
©trumpexcel.com
Notes:• It is a volatile formula (use with caution)
ROUND()
What it Does:
Rounds a number to a specified number of digits
Syntax:
=ROUND(number, num_digits)
©trumpexcel.com
Notes:• If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.• If num_digits is 0, the number is rounded to the nearest integer.• If num_digits is less than 0, the number is rounded to the left of the decimal point
SUM()
What it Does:
Adds all the numbers that you specify as arguments
Syntax:
=SUM(number1,[number2],...])
©trumpexcel.com
Notes:• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical
values, or text in the array or reference are ignored• If any of the argument is an error, it displays an error
SUMIF()
What it Does:
Adds all the values in a range that meet the specified
criteria
Syntax:
=SUMIF(range, criteria, [sum_range])
©trumpexcel.com
Notes:• Adds values based on a single criteria; for multiple criteria, use SUMIFS• If sum_range argument is omitted, Excel uses the criteria range (range) as the sum range• Blanks or text in sum_range are ignored• Criteria could be a number, expression, cell reference, text, or a formula
• Criteria which are text or mathematical/logical symbols (such as =,+,-,/,*) should be in double quotes• Wildcard characters can be used in criteria• Criteria should not be longer than 255 characters
• If the size (number of cells) in criteria range and sum range are different, the size of criteria range takes precedence
SUMIFS()
What it Does:
Adds the cells in a range that meet multiple criteria
Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1,
[criteria_range2, criteria2], ...)
©trumpexcel.com
Notes:• Adds values based on a single criteria; for multiple criteria, use SUMIFS• Blanks or text in sum_range are ignored• Criteria could be a number, expression, cell reference, text, or a formula
• Criteria which are text or mathematical/logical symbols (such as =,+,-,/,*) should be in double quotes• Wildcard characters can be used in criteria• Criteria should not be longer than 255 characters
• Cells in sum_range are added only when all the conditions are met• Cells in the sum_range argument that contain TRUE evaluate to 1; cells in sum_range that contain FALSE evaluate
to 0 (zero)• Size range of sum_range and all the criteria_range must be equal
SUMPRODUCT()
What it Does:
Multiplies corresponding components in the given
arrays, and returns the sum of those products
Syntax:
=SUMPRODUCT(array1, [array2], [array3], ...)
©trumpexcel.com
Notes:• The size of all the arrays must be the same• Non numeric entries are treated as 0
STAT FunctionsSTAT FUNCTIONS
RANK()
What it Does:
Returns the rank of a number in a list of numbers
Syntax:
=RANK(number, ref, [order])
©trumpexcel.com
Notes:• If Order is:
• 0 or omitted – Highest number gets 1st rank• 1 – Lowest numbers gets 1st rank
• Duplicate values get the same rank.• If there are 2 numbers with the 2nd rank, the next number gets a rank of 4. Similarly if 2 numbers have the
2nd ran, the next number gets a rank of 5
Notes:• Arguments can numbers, named ranges, or cell references that contain numbers• Cells references with text, logical values, or empty cells are ignored, but cells with 0 are counted for averaging
AVERAGE()
What it Does:
Returns the average (arithmetic mean) of the
arguments
Syntax:
=AVERAGE(number1, [number2], ...)
©trumpexcel.com
AVERAGEIF()
What it Does:
Returns the average (arithmetic mean) of all the cells
in a range that meet a given criteria
Syntax:
=AVERAGEIF(range, criteria, [average_range])
©trumpexcel.com
Notes:• Empty cells are ignored in average_range• If the criteria is an empty cell, Excel treats it as 0• If no cell meets the criteria, a #DIV/0! error is returned• Criteria could be a number, expression, cell reference, text, or a formula
• Criteria which are text or mathematical/logical symbols (such as =,+,-,/,*) should be in double quotes• Wildcard characters can be used in criteria
AVERAGEIFS()
What it Does:
Returns the average (arithmetic mean) of all cells that
meet multiple criteria
Syntax:
=AVERAGEIFS(average_range, criteria_range1,
criteria1, [criteria_range2, criteria2], ...)
©trumpexcel.com
Notes:• If there are blanks or text in average_range, it returns a #DIV/0! error• Criteria could be a number, expression, cell reference, text, or a formula
• Criteria which are text or mathematical/logical symbols (such as =,+,-,/,*) should be in double quotes• Wildcard characters can be used in criteria
• Cells in average_range are averaged only when all the conditions are met• Size range of average_range and all the criteria_range must be equal
COUNT()
What it Does:
Counts the number of cells that contain numbers
Syntax:
=COUNT(value1, [value2], ...)
©trumpexcel.com
Notes:• Only numerical values are counted• Dates and text representation of numbers (such as 1 in double quotes – “1”) are counted• Logical Values (TRUE/FALSE) typed directly into the formula are counted
• Cell reference that refers to cells that contain logical values are ignored
COUNTA()
What it Does:
Counts the number of cells that are not empty
Syntax:
=COUNTA(value1, [value2], ...)
©trumpexcel.com
Notes:• Counts all the cells that are not empty• If a formula returns an empty cell, that is counted by COUNTA
COUNTBLANK()
What it Does:
Counts empty cells in a specified range of cells
Syntax:
=COUNTBLANK(range)
©trumpexcel.com
Notes:• Cells with formulas that return "" (empty text) are also counted
COUNTIF()
What it Does:
Counts the number of cells within a range that meets
the given criteria
Syntax:
=COUNTIF(range, criteria)
©trumpexcel.com
Notes:• Criteria could be a number, expression, cell reference, text, or a formula
• Criteria which are text or mathematical/logical symbols (such as =,+,-,/,*) should be in double quotes• Wildcard characters can be used in criteria• Criteria are case insensitive (“Hello” and “hello” are treated as same)
COUNTIFS()
What it Does:
Applies criteria to cells across multiple ranges and
counts the number of times all criteria are met
Syntax:
=COUNTIFS(criteria_range1, criteria1,
[criteria_range2, criteria2]…)
©trumpexcel.com
Notes:• Criteria could be a number, expression, cell reference, text, or a formula
• Criteria which are text or mathematical/logical symbols (such as =,+,-,/,*) should be in double quotes• Wildcard characters can be used in criteria• Criteria are case insensitive (“Hello” and “hello” are treated as same)
• Cells in counted only when all the conditions are met
LARGE()
What it Does:
Returns the k-th largest value in a data set
Syntax:
=LARGE(array, k)
©trumpexcel.com
Notes:• If array is empty, LARGE returns the #NUM! error value.• If k ≤ 0 or if k is greater than the number of data points, LARGE returns the #NUM! error value.
MAX()
What it Does:
Returns the largest value in a set of values.
Syntax:
=MAX(number1, [number2], ...)
©trumpexcel.com
Notes:• Empty cells, logical values, or text in the array or reference are ignored• Arguments that are error values or text that cannot be translated into numbers cause errors
MIN()
What it Does:
Returns the smallest number in a set of values
Syntax:
=MIN(number1, [number2], ...)
©trumpexcel.com
Notes:• Empty cells, logical values, or text in the array or reference are ignored• Arguments that are error values or text that cannot be translated into numbers cause errors
SMALL()
What it Does:
Returns the k-th smallest value in a data set
Syntax:
=SMALL(array, k)
©trumpexcel.com
Notes:• If array is empty, SMALL returns the #NUM! error value.• If k ≤ 0 or if k is greater than the number of data points, SMALL returns the #NUM! error value.