 |
5.13
Using
functions
5.13.3.5
The
AVERAGE
function
Returns
the
average
(arithmetic
mean)
of
the
arguments.
Syntax:
- AVERAGE(number1,number2,
...)
Number1,
number2,
...
are
1
to
30
numeric
arguments
for
which
you
want
the
average.
Remarks
-
The
arguments
must
be
either
numbers
or
names,
arrays,
or
references
that
contain
numbers.
-
If
an
array
or
reference
argument
contains
text,
logical
values,
or
empty
cells,
those
values
are
ignored;
however,
cells
with
the
value
zero
are
included.
-
Tip:
When
averaging
cells,
keep
in
mind
the
difference
between
empty
cells
and
those
containing
the
value
zero,
especially
if
you
have
cleared
the
Zero
values
check
box
on
the
View
tab
(Options
command,
Tools
menu).
Empty
cells
are
not
counted,
but
zero
values
are.
Examples
If
A1:A5
is
named
Scores
and
contains
the
numbers
10,
7,
9,
27,
and
2,
then:
AVERAGE(A1:A5)
equals
11
AVERAGE(Scores)
equals
11
AVERAGE(A1:A5,
5)
equals
10
AVERAGE(A1:A5)
equals
SUM(A1:A5)/COUNT(A1:A5)
equals
11
If
C1:C3
is
named
OtherScores
and
contains
the
numbers
4,
18,
and
7,
then:
AVERAGE(Scores,
OtherScores)
equals
10.5
5.13.3.6
The
SUM
functions
Adds
all
the
numbers
in
a
range
of
cells.
Syntax:
- SUM(number1,number2,
...)
Number1,
number2,
...
are
1
to
30
arguments
for
which
you
want
the
total
value
or
sum.
-
Numbers,
logical
values,
and
text
representations
of
numbers
that
you
type
directly
into
the
list
of
arguments
are
counted.
See
the
first
and
second
examples
following.
-
If
an
argument
is
an
array
or
reference,
only
numbers
in
that
array
or
reference
are
counted.
Empty
cells,
logical
values,
text,
or
error
values
in
the
array
or
reference
are
ignored.
See
the
third
example
following.
-
Arguments
that
are
error
values
or
text
that
cannot
be
translated
into
numbers
cause
errors.
Examples
SUM(3,
2)
equals
5
SUM("3",
2,
TRUE)
equals
6
because
the
text
values
are
translated
into
numbers,
and
the
logical
value
TRUE
is
translated
into
the
number
1.
Unlike
the
previous
example,
if
A1
contains
"3"
and
B1
contains
TRUE,
then:
SUM(A1,
B1,
2)
equals
2
because
references
to
nonnumeric
values
in
references
are
not
translated.
If
cells
A2:E2
contain
5,
15,
30,
40,
and
50:
SUM(A2:C2)
equals
50
SUM(B2:E2,
15)
equals
150
5.13.4
Worksheet
Functions
Using
Function
Wizard
5.13.4.1
The
VLOOKUP
Function
Searches
for
a
value
in
the
leftmost
column
of
a
table,
and
then
returns
a
value
in
the
same
row
from
a
column
you
specify
in
the
table.
Use
VLOOKUP
instead
of
HLOOKUP
when
your
comparison
values
are
located
in
a
column
to
the
left
of
the
data
you
want
to
find.
Syntax:
- VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value
is
the
value
to
be
found
in
the
first
column
of
the
array.
Lookup_value
can
be
a
value,
a
reference,
or
a
text
string.
Table_array
is
the
table
of
information
in
which
data
is
looked
up.
Use
a
reference
to
a
range
or
a
range
name,
such
as
Database
or
List.
-
If
range_lookup
is
TRUE,
the
values
in
the
first
column
of
table_array
must
be
placed
in
ascending
order:
...,
-2,
-1,
0,
1,
2,
...,
A-Z,
FALSE,
TRUE;
otherwise
VLOOKUP
may
not
give
the
correct
value.
If
range_lookup
is
FALSE,
table_array
does
not
need
to
be
sorted.
-
You
can
put
the
values
in
ascending
order
by
choosing
the
Sort
command
from
the
Data
menu
and
selecting
Ascending.
-
The
values
in
the
first
column
of
table_array
can
be
text,
numbers,
or
logical
values.
-
Uppercase
and
lowercase
text
are
equivalent.
Col_index_num
is
the
column
number
in
table_array
from
which
the
matching
value
must
be
returned.
A
col_index_num
of
1
returns
the
value
in
the
first
column
in
table_array;
a
col_index_num
of
2
returns
the
value
in
the
second
column
in
table_array,
and
so
on.
If
col_index_num
is
less
than
1,
VLOOKUP
returns
the
#VALUE!
error
value;
if
col_index_num
is
greater
than
the
number
of
columns
in
table_array,
VLOOKUP
returns
the
#REF!
error
value.
Range_lookup
is
a
logical
value
that
specifies
whether
you
want
VLOOKUP
to
find
an
exact
match
or
an
approximate
match.
If
TRUE
or
omitted,
an
approximate
match
is
returned.
In
other
words,
if
an
exact
match
is
not
found,
the
next
largest
value
that
is
less
than
lookup_value
is
returned.
If
FALSE,
VLOOKUP
will
find
an
exact
match.
If
one
is
not
found,
the
error
value
#N/A
is
returned.
Remarks
-
If
VLOOKUP
can't
find
lookup_value,
and
range_lookup
is
TRUE,
it
uses
the
largest
value
that
is
less
than
or
equal
to
lookup_value.
-
If
lookup_value
is
smaller
than
the
smallest
value
in
the
first
column
of
table_array,
VLOOKUP
returns
the
#N/A
error
value.
-
If
VLOOKUP
can't
find
lookup_value,
and
range_lookup
is
FALSE,
VLOOKUP
returns
the
#N/A
value.
Examples
On
the
preceding
worksheet,
where
the
range
A4:C12
is
named
Range:
VLOOKUP(1,Range,1,TRUE)
equals
0.946
VLOOKUP(1,Range,2)
equals
2.17
VLOOKUP(1,Range,3,TRUE)
equals
100
VLOOKUP(.746,Range,3,FALSE)
equals
200
VLOOKUP(0.1,Range,2,TRUE)
equals
#N/A,
because
0.1
is
less
than
the
smallest
value
in
column
A
VLOOKUP(2,Range,2,TRUE)
equals
1.71
  
| |
