- Formula Basics
- Summarizing Data Quickly
- Elements of Formulas
- Entering Formulas
- Determining the Order of Operations
- Understanding Cell References
- Using Cell References
- Understanding Comparison Operators
- Using Comparison Operators
- Understanding Functions
- Using Functions
- Copying and Moving Formulas
- Handling Errors
Understanding Functions
You can do a lot by using only constants, operators, and cell references, but Numbers’ real power comes from its functions: built-in, specialized, named operations that you can use in your formulas. Numbers gives you more than 250 functions, ranging from simple ones that sum or average numbers to complex ones that do financial and engineering calculations. See the appendix for a complete list of Numbers’ functions.
In addition to working with numbers, functions can do calendar arithmetic, make logical decisions, search and transform text, and look up values in lists. Table 4.6 lists some of the most commonly used functions.
Table 4.6 Commonly Used Functions
Function |
Description |
AND/OR/NOT |
Creates a conditional formula that results in a Boolean value |
AVERAGE |
Calculates the arithmetic mean of a group of numbers |
CONVERT |
Converts a number from one measurement system to another system |
COUNT |
Counts the number of dates or numbers in a range |
DATEDIF |
Calculates the time difference between two dates |
FIND/SEARCH |
Finds one text string within another |
IF |
Creates a conditional formula that results in another calculation |
INT |
Rounds a number down to the nearest integer |
ISERROR |
Determines whether a value is an error |
MIN/MAX |
Returns the smallest/largest value of a group of numbers |
NETWORKDAYS |
Calculates the number of working days between two dates |
NOW |
Returns the current date and time |
NPV |
Calculate the net present value of an investment |
RAND |
Returns a uniform random number between 0 and 1 |
REPLACE/SUBSTITUTE |
Replaces one text string with another |
ROUND |
Rounds a number to the spe-cified number of decimal places |
SQRT |
Returns the square root of a number |
SUM |
Calculates the sum of a group of numbers |
TRIM |
Removes extra spaces from text |
UPPER/LOWER/PROPER |
Changes the case of text |
VALUE |
Converts text to a number |
VLOOKUP/HLOOKUP/LOOKUP |
Looks up values in a list |
Each function has a name followed by zero or more comma-separated arguments enclosed in parentheses. You use arguments to provide the values that the function needs to do its work. The CONVERT function, for example, takes a number in one measurement system and converts it to another system. Its syntax—which gives a function’s name and the names and order of its arguments—is
CONVERT(convert-num, from-unit, to-unit)
Using CONVERT with sample arguments gives the formula
=CONVERT(100, "C", "F")
This formula displays 212 in a cell—100 degrees Celsius expressed in the Fahrenheit scale, and
=CONVERT(25, "km", "mi")
displays the number of miles in 25 kilometers (15.53427...) .
The number and types of arguments vary by function. You can type arguments directly into the formula or use cell references for some or all arguments. Arguments can be constants, operator expressions, cell references, or other functions. Text arguments must go inside quotation marks (but don’t put cell references inside quotation marks—they aren’t considered to be text even if their cells contain text). Here are a few examples of valid arguments:
=CONVERT(60+40, "C", "F")
=CONVERT(A2, B2, C2)
=CONVERT(A2+10, B2, UPPER("f"))
=CONVERT(SUM(D2:D10)+SUM(F2:F10)−273.15, B2, UPPER(LEFT("fahrenheit",1)))
Functions that take no arguments need no user-supplied data to do their work. The TODAY function, for example, returns today’s date:
=TODAY()