Publishers of technology books, eBooks, and videos for creative people

# Numbers for iPad: Formulas and Functions

• Print
This chapter is from the book

## 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()