Microsoft Office Tutorials and References

In Depth Information

**Using Other Functions**

Remember that the thing you are looking up

(the employee’s name in this example) must be

contained in the first column/row. The number

6 tells Excel to look up data in the sixth column

of the Range for the matching employee. The

sixth column in this case is the Emergency Phone

Number column, which is exactly what we want.

INT

Need to round a result down to the nearest

integer? Then the INT function is for you. The

syntax is =INT(Number). Now typically, you won’t

enter an actual number as the argument for the

INT function, because I’m guessing you could

probably round it down to the nearest integer

in your head. Instead, you will probably enter a

cell address, like this: =INT(G4). Assuming G4

contains the value 102.31, the result is 102. If

G4 contains the value, 102.72, the result is still

102 since INT always rounds
down
.

You can also nest a formula or a function with INT

if you like. For example, consider the formula

=INT(SUM(G10:J32)). It tells Excel to total the

values in the range G10:J32, and then round

them down to the nearest integer. Consider this

formula: =INT(A2/40). It tells Excel to take the

value in cell A2, divide it by 40, and then round

the result down to the nearest integer.

Figure 3-19

Look up data in a large database of information

using VLOOKUP or HLOOKUP.

ROUND

The ROUND function adjusts a value to a specific

number of digits. Now, there are several reasons

why you might want to round the results of your

formulas, such as to stop your worksheets from

driving you crazy. Let me explain.

Using Mathematical Functions

You already know some mathematical operations

for use in formulas (+, –, *, /, and ^), but what

about the Mathematical functions? Which ones

might you find the most useful for analysis of

data? Well, you are already pretty familiar with

one Mathematical function, SUM, which is used

to add a group of cells. Let’s take a look at some

others.

When a cell is formatted to a specified number

of decimal places, the display of that value is the

only thing affected. The actual value in the cell

is still used in all calculations. For example, if a

cell contains the value 13.45687, and you decide

to display only the first two decimal places, the

value 13.46 will display in the cell, but the value

13.45687 will be used in all calculations,

including totals, which is where the “driving you crazy”

part comes in. Take a good look at the worksheet

shown in Figure 3-20.