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 diﬀerent answers depending upon diﬀerent 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 Deﬁned 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 Excel’s 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 speciﬁed 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, signiﬁcance) Rounds a number down to the nearest integer or

to the nearest integer multiple of signiﬁcance.

=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.