Revised on 8-10-2007
Statistical Functions:
MAX(number1,number2,...)Returns the largest value in a set of values. Logical values and text will be ignored.
MIN(number1,number2, ...)
Returns the smallest number in a set of values. Logical values and text will be ignored.
LARGE(data array,k)
Returns the k-th data in a set of descending data array.
SMALL(data array,k)
Returns the k-th data in a set of ascending data array.
SUM(number1,number2, ...)
Returns the sum of all the numbers.
SUMPRODUCT(array1, array2, array3,...)
Returns the sum of the products of the corresponding elements in array1, array2, ...
For example, SUMPRODUCT({1,2,3},{5,6,7}) = 1 5 + 2 6 + 3 7 = 38.
SUMSQ(number1,number2, ...)
Returns the sum ofsquares of all the numbers.
AVERAGE(number1,number2, ...)
Returns the average (arithmetic mean) of the arguments.
COUNT(cell1, cell2, ...)
Counts the number of cells that containing numbers.
COUNTA(cell1, cell2, ...)
Counts the number of cells that containing numbers and labels.
COUNTBLANK(cell1, cell2, ...)
Counts the number of cells that do not contain anything.
SUMIF(evaluate_range,criteria,sum_range)
Adds the cells specified by a given criteria.
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given criteria.
RANK(number,ref,order)
Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list.
FREQUENCY(data_array, bins_array)
Calculates how often numbers occur within a set of data, and then returns a vertical array of counts.
MathematicalFunctions:
ROUND(number,num_digits)Rounds a number to a specified number of digits. Negative number of num_digits represents digits on the integral part.
ROUNDUP(number,num_digits)
Round-ups a number to a specified number of digits. Negative number of num_digits represents digits on the integral part.
ROUNDDOWN(number,num_digits)
Round-downs a number to a specified number of digits. Negative number of num_digits represents digits on the integral part.
TRUNC(number) / INT(number)
Truncate a number to a whole number.
SQRT(number)
Returns the square root of apositive number.
MOD(number1, number2)
Returns the remainder when number1 is divided by number2.
QUOTIENT(number1, number2)(not supported by Excel)
Returns the quotient when number1 is divided by number2.
ABS()
Returns the absolute value of a number.
RAND()
Returns a random number that is greater than or equal to zero and less than one. No argument is required.
Text Functions:
LEN(text)Returns the number of characters in a text string.
LEFT(text,num_chars)
Returns the first (or leftmost) character or characters in a text string.
RIGHT(text,num_chars)
Returns the last (or rightmost) character or characters in a text string.
MID(text,start_num,num_chars)
Returns a specific number of characters from a text string, starting at the position you specify.
UPPER(text)
Converts letters of the text to uppercase.
LOWER(text)
Converts letters of the text to lowercase.
PROPER(text)
Converts letters of the text to proper case.
FIND(find_text,within_text,start_num) / SEARCH(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 leftmost character of within_text.
TEXT(number,num_format)
Convert a number into text with specific format.
VALUE(text)
Convert a text into numeric type.
CODE(char)
Returns the ASCII code of a character.
CHAR(number)
Returns the character which ASCII code is equal to the number.
TRIM(string)
Trim all unnecessary spaces including leftmost space and rightmost space.
EXACT(string1, string2)
Compare two strings. Returns TRUE if the string are identical, else returns FALSE.
ISBLANK(cell)
Returns TRUE if the cell contains nothing, else returns FALSE.
CONCATENATE(string1, string2, …)
Returns a string that formed by connecting a set of strings.
Date Functions:
DATE(year,month,day)Returns the serial number of a particular date.
NOW( )
Returns the serial number of the current date and time.
YEAR(serial_number)(out of syllabus)
Returns the year corresponding to serial_number.
MONTH(serial_number)(out of syllabus)
Returns the month corresponding to serial_number.
DAY(serial_number)(out of syllabus)
Returns the day of the month corresponding to serial_number.
Logical Functions:
AND(logical1,logical2, ...)Returns TRUE if all its arguments are TRUE; returns FALSE if one or more arguments is FALSE.
OR(logical1,logical2,...)
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
NOT(logical)
Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.
IF(logical_test,value_if_true,value_if_false)
Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
Reference Functions:
CHOOSE(index, argument1, argument2, argument3,...)Returns the corresponding argument according to the index value. The index may be a number or a formula that must be lying between 1 and 29. The argument type may be number, label or cell range.
For example, SUM(CHOOSE(3,A1:A5,B2:B3,C1:C8,A2:B3)) will returns the sum of the range C1:C8.
LOOKUP(value, lookup_vector, result_vector)
Search the first occurrence of value in the lookup_vector and returns the corresponding value in result_vector. Both lookup_vector, result_vectormust in same dimension. If no match found, error value returned.
VLOOKUP(value, lookup_table, col_num, range)
Search the first occurrence of value in the first column of lookup_table and returns the value in the column (specified by col_num) of lookup_table. If range is FALSE, exact match is desired.
HLOOKUP(value, lookup_table, row_num, range)
Search the first occurrence of value in the first row of lookup_table and returns the value in the row (specified by row_num) of lookup_table. If range is FALSE, exact match is desired.