Shortcuts

AVERAGE

AVERAGE (number1, [number2], ...)

Returns the arithmetic mean (the sum of all items divided by the total number of items) of its arguments. You can average the values in a column by clicking the column as the argument.

AVERAGEIF

AVERAGEIF(range,criteria,average_range)

Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

COUNT

COUNT(value1,value2,...)

Counts the number of cells that contain non-false values. Use COUNT to get the number of entries in a field that's in a range or array of numbers

COUNTNUM

COUNTNUM(value1, value,...)

Counts the number of cells that contain numbers. Use COUNTNUM to get the number of entries in a number field that’s in a range or array of numbers.

COUNTA

COUNTA(value1,value2,...)

Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

COUNTIF

COUNTIF(range,criteria)

Counts the number of cells within a range that meet the given criteria.

LARGE

LARGE(array,k)

Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score.

MAX

MAX(number1,number2,...)

Returns the greatest value of a set of numbers. Calculate the maximum value in a column by clicking the column as the argument.

MEDIAN

MEDIAN(number1,number2,...)

Returns the number in the middle of a group of numbers. Half of the numbers are greater than the median, half are less than the median. If there is an even number of numbers, the average of the two median numbers is returned. Calculate the median of a column by clicking the column as the argument.

MIN

MIN (number1, number 2...)

Returns the smallest value of a set of numbers. Calculate the minimum by clicking the column as the argument for this function.

MODE

MODE(number1,number2,...)

Returns the most frequently occurring, or repetitive, value in an array or range of data.

SMALL

SMALL(array,k)

Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set.

STDEV

STDEV(number1,number2,...)

Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

TRUNC

TRUNC (number)

Truncates a number to an integer by removing all decimal points from the number.

ADDYEARS

ADDYEARS (start_year, years_to_add)

Adds the number of years to the date. If the resulting date is invalid for the new year (that is Feb-29 in a non-leap-year), then the date is wrapped to the next month . The extra days are added to the beginning of the month. For example, Feb-29 in a non-leap-year would become Mar-1.

DATE

DATE(year,month,day)

Returns the sequential serial number that represents a particular date.

DATEVALUE

DATEVALUE(date_text)

Converts a date in a cell that uses the Text cell format into a sequential serial number that you can use in date calculations.

NOW

NOW( )

Returns the serial number of the current date and time. If the cell format was General before the function was entered, the result changes the cell formatting to Time.

SUBTRACT MONTHS

SUBTRACTMONTHS (start_date, months_to_sub)

Subtracts the specified number of months from the date. If the resulting date is invalid (for example, Feb. 30), then the date is wrapped to the next month. The extra days are added to the beginning of the month. For example, a result of Feb-30 would be wrapped to either Mar-2 or Mar-1 (depending on leap year).

SUBTRACTYEARS

SUBTRACTYEARS (start_year, years_to_sub)

Subtracts the specified number of years from the date. If the resulting date is invalid for the new year (that is Feb-29 in a non-leap-year), then the date is wrapped to the next month . The extra days are added to the beginning of the month. For example, Feb-29 in a non-leap-year would become Mar-1.

TIME

TIME(hour,minute,second)

Returns the decimal number for a particular time. The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

TIMEVALUE

TIMEVALUE(time_text)

Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.).

TODAY

TODAY( ).

Returns the serial number of the current date. If the cell format was General before the function was entered, the result changes the cell formatting to Date.

WORKDAY

WORKDAY(start_date,days,holidays)

Adds the number of days specified in the days_to_add argument to the start_date argument. The only days counted are Monday through Friday. If the start date specified is either a Saturday or Sunday, then the start date is shifted to either the following Monday, or the previous Friday. How the date is shifted depends on whether the days_to_add value is positive or negative.

ABS

ABS (number)

Returns the absolute value of a number.

EXP

EXP (number)

Returns e raised to the power of a given number.

LN

LN(number)

Returns the natural logarithm of a number.

LOG

LOG(number)

Returns the logarithm of a number to a specified base.

LOG10

LOG10(number)

Returns the base-10 logarithm of a number.

POWER

POWER(number ,number)

Returns the result of a number raised to a power

PRODUCT

PRODUCT(number1, number2)

Multiplies its arguments

ROUND

ROUND(number1, num_digits)

Rounds a number to a specified number of digits. To round a whole number, use 0 as the number of digits, for example, ROUND(1.1414, 0) displays 1. If the value of the digit's argument is > 0 (for example, 1, 2, 3,), the result is rounded to that number of decimal places. If the value of the digits argument is < 0, the number is rounded to some multiple of 10. For example, -1 rounds to the nearest multiple of 10, -2 rounds to the nearest multiple of 100, and so on. So, ROUND (32, -1) rounds to 30.

ROUNDUP

ROUNDUP(number1, num_digits)

Rounds a number up, away from 0.

SQRT

SQRT(number)

Returns a positive square root.

SUM

SUM (number1, number2, ...)

Adds the specified numbers. When working in a cell or summary row, you can click a column to include the sum of the values in the column.

SUMIF

SUMIF(range,criteria,sum_range)

Adds the cells specified by a given criteria.

SUMPRODUCT

SUMPRODUCT(array1,array2,array3,...)

Returns the sum of the products of corresponding array components

IRR

IRR(values, guess)

Returns the internal rate of return for a series of cash flows represented by the numbers in values.

NPV

NPV(rate, value1,value2,...)

Calculates the present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).

AND

AND(logical1,logical2,...)

Returns TRUE if all its arguments are TRUE; returns FALSE if one or more arguments are FALSE.

IF

IF (condition, result_if_true, result_if_false)

Compares the relation between two columns (conditions) and gives one result if the condition is true and another result if the condition is false. The result is displayed in the column where you put the formula (for example, column C). The result can be displayed as a number, text, the result of another formula,. Or, it can be the a result of another IF statement. Placing the formula =IF (A>B, A, B) in column C compares the conditions in columns A and B and places the larger number in column C. If you want the result displayed in words, enclose the text in quotation marks. For example, the following formula, entered in column C, compares the values in columns A and B, and displays the word "yes" if A is greater than B and "no" if A is not greater than B: =IF (A>B, "yes", "no")

ISBLANK

ISBLANK(value)

Returns the logical value TRUE if value is a reference to an empty cell; otherwise, it returns FALSE.

NOT

NOT(logical)

Reverses the value of its argument. Use NOT when you want to make sure that a value is not equal to one particular value.

OR

OR(logical1,logical2,...)

Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

CONCATENATE

CONCATENATE(text1,text2,...)

Joins several text strings into one text string.

FIND

FIND(find_text,within_text,start_num)

Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text. You can also use SEARCH to find one text string within another, but unlike SEARCH, FIND is case sensitive and doesn't allow wildcard characters.

LEFT

LEFT(text,num_chars)

Returns the first character or characters in a text string, based on the number of characters you specify.

LEN

LEN(text)

Returns the number of characters in a text string.

LOWER

LOWER(text)

Converts all uppercase letters in a text string to lowercase.

MID

MID(text,start_num,num_chars)

Returns a specific number of characters from a text string, starting at the position that you specify, based on the number of characters that you specify.

RIGHT

RIGHT(text,num_chars)

Returns the last character or characters in a text string, based on the number of characters you specify.

SEARCH

SEARCH(find_text,within_text,start_num)

Returns the number of the character at which a specific character or text string is first found, beginning with start_num. Use SEARCH to determine the location of a character or text string within another text string.

TRIM

TRIM(text)

Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that has irregular spacing.

UPPER

UPPER(text)

Converts text to uppercase