Excel Formulas and Functions for Productive Spreadsheets

Returns the largest value in a range of cells.

=MAX(number1, [number2], …)

Returns the smallest value in a range of cells.

=MIN(number1, number2, [number], …)

Counts the number of cells in a range that meet specified criteria.

=COUNTIF(range, criteria)

Returns the sum of a range of cells that meet specified criteria.

=SUMIF(range, criteria,  [sum_range])

Returns the average of a range of cells that meet specified criteria.

=AVERAGEIF(range, criteria,  [average_range])

Returns the population standard deviation of a range of numbers.

=STDEV(number1,  [number2], …)

Returns the population variance of a range of numbers.

=VAR(number1, number2,  number3, …)

Returns the sample variance of a range of numbers.

=VARP(number1,  [number2], …)

Returns the nth percentile of a range of numbers.

=PERCENTILE(array,k)

Returns the nth quartile of a range of numbers.

=QUARTILE(array, quart)

Returns the skewness of a range of numbers.

=SKEW(number1, [number2], …)

Returns the kurtosis of a range of numbers.

=KURT(number1, [number2], …)

Returns the correlation coefficient of two ranges of numbers.

=CORREL(array1, array2)

Returns a value along a linear trend.

=FORECAST(x,known_y’s, known_x’s)

Returns the slope of a linear regression line.

=SLOPE(array1, array2)

If a certain condition is true, then a certain value is returned.

=IF(condition, value if true, value if false)

This function returns TRUE if all of the conditions are met.

=AND(condition1, condition2, …)

This function returns TRUE if any of the conditions are met.

=OR(logical1, [logical2], …)

This function returns the opposite of a condition.

=NOT(logical)

This function returns a specified value if an error occurs.

=IFERROR(value, value_if_error)

This function returns the minimum value of cells that meet a certain condition.

=MINIF(range, criteria, [sum_range])

This function returns the position of a value in a range.

=MATCH(lookup_value, lookup_array, [match_type])

Rounds a number to a specified number of digits.

=ROUND(number, num_digits)

This function rounds a number up to the nearest integer or multiple of the specified number.

=CEILING(number, significance)

This function rounds a number down to the nearest integer or multiple of the specified number.

=FLOOR(number, significance)

This function combines multiple strings into a single string.

=CONCATENATE(text1, [text2], …)

This function returns the length of a string.

=LEN(text)

This function returns a specified number of characters from the left side of a string.

=LEFT(text, [num_chars])

This function returns a specified number of characters from the right side of a string.

=RIGHT(text, [num_chars])

Combines date digits into one cell.

=DATE(year, month, day)

Combines time digits into one cell.

=TIME(hour, minute, second)

Combines two or more text strings into one string.

Looks for a value in the leftmost column, and returns a value in the same row from another column.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]