Chapter 3 More Excel56
Logical Functions is is a small group of functions, seven in all, that allows us to
perform logical operations and test for conditions. e group also includes the IF func-
tion which is useful for producing different answers depending upon different condi-
tions.
Lookup and Reference Functions is group consists of 18 functions that are pri-
marily meant for use in conjunction with lists and tables to locate information. An
example of a function from this group is the VLOOKUP function, which acts somewhat
as an address directory where you look up the address of a person given the name.
Math and Trigonometry Functions is is a large group of functions, 58 in all, that
are meant for performing common mathematical and trigonometric calculations. e
SUM function is an example from this category.
Statistical Functions is is the largest category, consisting of 80 functions, attesting
to its usefulness and widespread application. ese functions are designed to allow users
to perform statistical analysis of data. An example from this group is the MODE function
which returns the most common value in a data set.
Text Functions ere are 27 functions in this group that are designed to operate on
text strings. An example from this group is the LEN function that returns the number
of characters in a text string.
User Defined Functions In many situations, you may want to create your own func-
tion for a task that cannot be suitably done with functions from the above groups. You
create such functions using VBA. You use these functions just like Excels built-in
functions.
Common Function Examples
Table 3.3 shows examples of some common functions. The left column shows the func-
tion name and its arguments. The right column provides a short description of the value
returned, that is, the answer, by the function.
We will now look at two illustrative examples to see how some of the above formulas
can be used.
Example 3.2 Calculating Distances
This example exercise deals with the concept of distance between a pair of points. Each
point is specified by two numbers that represent its x and y coordinates. We are familiar
with the Euclidean distance between two points, which is nothing but the length of
Common Function Examples 57
the line joining the two points. The Euclidean distance between two points is calcu-
lated by the following relationship:
Euclidean Distance (Point 1, Point 2) = [(x
1
x
2
)2 + (y
1
y
2
)
2
]
1/2
The above formula means that we take the difference in the x coordinates of two points
and square it. Then we do the same for the y coordinates, i.e., take the difference and
square it. Next, the two squared values are added and the square root of the sum is then
Table 3.3 Examples of Common Excel Functions
Function Description
AVERAGE(number1, number2,…) Returns the average of its arguments.
=AVERAGE(2.6,10,-3) returns 3.2.
ABS(number) Returns the absolute value of its argu ment.
=ABS(-23.56) returns 23.56.
SUMSQ(number1, number2,…) Returns the sum of the squares of the arguments.
=SUMSQ(5,2,4) returns 45.
ROUND(number, num_digits) Rounds a number to the specified num ber of
digits after the decimal point.
=ROUND(4.6737,0) returns 5.
=ROUND(4.6737,1) returns 4.7.
=ROUND(4.6737,2) returns 4.67.
INT(number) Rounds a number down to the nearest integer.
=INT(3.7) returns 3.
=INT(5/3) returns 1.
FLOOR(number, significance) Rounds a number down to the nearest integer or
to the nearest integer multiple of significance.
=FLOOR(4.67,2) returns 4.
=FLOOR(4.67,0.25) returns 4.5 because 4.5
is the nearest integer multi ple (18 times) of 0.25
that is smaller than 4.67.
MOD(number, divisor) Returns the remainder after division.
=MOD(4,3) returns 1.
=MOD(5,3.3) returns 1.7.
MAX(number1, number2,…) Returns the largest number from the argument
list.
=MAX(12,-23,43) returns 43.
PRODUCT(number1, number2,…) Returns the product of all the numbers given as
arguments.
=PRODUCT(5,2.2,-4) returns –44.

Get Computing with Excel and VBA now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.