Some Functions (and Operators) to Learn

and what to learn about them

Note: In describing what qualifies as valid arguments below, when a type is given, this can be a literal of that type, a reference to a cell containing that type, or, more generally, any expression or function that returns that type.

For example, all of these are valid uses of the INT function:

• INT(5.3)
• INT(10*A1+B1)
• INT(1+ROUND(A1,2))

as long as cells A1 and B1 contain numbers (where these numbers are possibly the returned values of other formulas as well).

Function name	Arguments	What it returns	Nonstandard arguments?
SUM	Up to 30; each can be either a number or a range	The sum of its arguments	Ignores text, blanks, and booleans in any range arguments
PRODUCT	Up to 30; each can be either a number or a range	The product of its arguments	Ignores text, blanks, and booleans in any range arguments
INT	(number)	The integer portion of its argument.	If you give it a non-number as an argument, it returns an error
ROUND	(whatToRound, howManyPlaces)	The first argument rounded to howManyPlaces decimal places.	If you give it a non-number as an argument, it returns an error. Note: =Round(2.5,0) returns 3
MOD	(number, divisor)	The remainder when the first argument is divided by divisor	If you give it non-numbers, it returns an error. divisor must not be zero.
ABS	(number)	The absolute value of its argument (i.e., the number without its sign)	Returns an error if given a non-number as an argument
MAX	Up to 30; each can be either a number or a range	The largest number among its arguments	Ignores text, blanks, and booleans in any range argument
MIN	Up to 30; each can be either a number or a range	The smallest number among its arguments	Ignores text, blanks, and booleans in any range argument
LEN	(text)	The number of characters in its argument	Works for number arguments too by treating them as text.
TRIM	(text)	The text string with leading and trailing blanks and repeated blanks between words removed.	If you give it a number, it returns a text string.
UPPER	(text)	Its argument in uppercase.	No effect on number arguments, but it returns a text string.
LOWER	(text)	Its argument in lowercase.	No effect on number arguments, but it returns a text string.
LEFT	(text, numOfCharacters)	The first numOfCharacters characters of text	If numOfCharacters is too big, you get all of text. Be careful if numOfCharacters is zero or minus!
RIGHT	(text, numOfCharacters)	Like LEFT except we count from the right end of the text string	Same as for LEFT
MID	(text, startPosition, numOfCharacters)	The sub-string of the text string starting at startPosition and going on for numOfCharacters characters.	If numOfCharacters is too big, you get all of the rest of the string.. If too small, watch out!
FIND	(what, where, startAt)	The position of the string what in where starting at startAt	If what is not in where, you get an error.
SUBSTITUTE	(where, old, new, n)	The result of replacing the nth occurrence of old by new in where.	The last argument is optional. If left out, all occurrences are replaced.
X & Y X & Y & Z etc.	X and Y (and Z) must be text (any number of operands may be used)	Concatenates all the text strings (i.e., creates a single text string consisting of all the individual text strings joined together).
X < Y X > Y X = Y X <= Y X >= Y X <> Y	Can be any type of value	A boolean	Works for text as well as numbers. Note: X and Y may display the same, yet not be equal! Figure out what each of these mean.
IF	(testCondition, trueValue, falseValue)	If testCondition is true, IF returns trueValue. Otherwise, it returns falseValue.	The testCondition must return TRUE or FALSE. Zero is interpreted as FALSE, and any other integer as TRUE. If testCondition is a text string, you get an error. Note: trueValue or falseValue can themselves be IF functions.
NOT	(boolean)	The other value	Like IF
AND	(boolean, boolean)	TRUE if both arguments are TRUE. Otherwise FALSE.	Like IF
OR	(boolean, boolean)	TRUE if at least one of its arguments is TRUE. FALSE otherwise.
DATE	(year, month, day)	The serial date for month/ day/ year	Be careful of the cell format.
TODAY	()	The date when last executed.	Changes every day. No arguments.
YEAR	(serialDate)	The serial date’s year.
MONTH	(serialDate)	The serial date’s month.
DAY	(serialDate)	The serial date’s day.
COUNTIF	(range, criterion)	The number of cases in range that meet criterion. Criterion can be a literal value, a cell reference, or a condition like “>32”.	If your criterion is ill-formed, you may get incorrect results. Try it on a range where you know the answer ahead of time.
SUMIF	(range, criterion, rangeToSumFrom)	The sum of items in rangeToSumFrom for cases in range which satisfy criterion.	Works like COUNTIF except that you can specify another range to be summed.
COUNT	(range)	The number of numbers present in range.	Strings, nulls and Booleans are not numbers.
AVERAGE	Up to 30 arguments; each can be either a number or a range	The average of its arguments.	Ignores text, Booleans and blanks. Includes cells containing zero.
STDEVP / STDEV	Same as AVERAGE	The standard deviation of its arguments.	Ignores text, Booleans and blanks. Includes cells containing zero.
VLOOKUP	(what, table, columnToReturn, rangeSearch?)	Depends on rangeSearch?: if rangeSearch? is TRUE (or omitted) you get back the value in columnToReturn in the row of table whose first component is the largest value less than or equal to what. If rangeSearch? is FALSE, it does an exact-match search instead (i.e., you get back the value in columnToReturn in the row of table whose first component exactly matches what).	Works for text values as well as numbers. Note: rangeSearch? can be missing, in which case, it is treated as TRUE. That is, VLOOKUP(what, table, columnToReturn) performs a range search.