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

Search JabSto ::

Custom Search