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

Home > Articles

  • Print
  • + Share This
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...) circle-a.jpg.

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

  • + Share This
  • 🔖 Save To Your Account