Microsoft Office Tutorials and References

In Depth Information

**Working with Functions**

Figure 3-10

Common Math, Trig, and Statistical functions

Function

Category

Description

AVERAGE(
number1
[,

number2
,
number3
, ...])

Statistical

Calculates the average of a collection of

numbers, where
number1, number2,
and so forth are

either numbers or cell references. Only
number1

is required. For more than one cell reference or

to enter numbers directly into the function, use

the optional arguments
number2, number3,
and

so forth.

COUNT(
value1
[,
value2
,

value3
, ...])

Statistical

Counts how many cells in a range contain numbers,

where
value1, value2,
and so forth are text,

numbers, or cell references. Only
value1
is required. For

more than one cell reference or to enter numbers

directly into the function, use the optional

arguments
value2, value3,
and so forth.

COUNTA(
value1
[,
value2
,

value3
, ...])

Statistical

Counts how many cells are not empty in ranges

value1, value2,
and so forth, or how many numbers

are listed within
value1, value2
, and so forth.

INT(
number
)

Math & Trig

Displays the integer portion of a number,
number
.

MAX(
number1
[,
number2
,

number3
, ...])

Statistical

Calculates the maximum value of a collection of

numbers, where
number1, number2,
and so forth

are either numbers or cell references.

MEDIAN(
number1
[,

number2
,
number3
, ...])

Statistical

Calculates the median, or middle, value of a

collection of numbers, where
number1, number2
, and so

forth are either numbers or cell references.

MIN(
number1
[,
number2
,

number3
, ...])

Statistical

Calculates the minimum value of a collection of

numbers, where
number1, number2,
and so forth

are either numbers or cell references.

RAND()

Math & Trig

Returns a random number between 0 and 1.

Math & Trig

ROUND(
number
,
num_digits
)

Rounds a number to a specified
number
of digits,

where number is the number you want to round and

num_digits
specifies how many digits to which you

want to round the number.

SUM(
number1
[,
number2
,

number3
, ...])

Math & Trig

Adds a collection of numbers, where
number1
,

number2
, and so forth are either numbers or cell

references.

For example, the AVERAGE function calculates the average value from a collection of

numbers. The syntax of the AVERAGE function is

AVERAGE(
number1
ƒ[,ƒ
number2
number3
,ƒ...])
,ƒ

where
number1
,
number2
,
number3
, and so forth are either numbers or cell references

to numbers. The following formula uses the AVERAGE function to calculate the average

of 1, 2, 5, and 8, returning the value 4:

=AVERAGE(1,ƒ2,ƒ5,ƒ8)

However, functions usually reference values entered in the worksheet. So, if the

range A1:A4 contains the values 1, 2, 5, and 8, the following formula also returns a

value of 4:

=AVERAGE(A1:A4)