excel functions explained...date & time functions date & time functions date() what it does:...

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.