Lesson 11: Expanding Skills with Functions and Objects

Outline• Use INT, ROUND, and TRUNC functions• Use the FV financial function• Insert hyperlinks into the document• Create nested functions• Use Lookup functions

Using the INT Function • The INT function, inside of the Calc and Trig

category, shows the non decimal portion of a value.

• INT is short for “integer”• The INT function truncates or “cuts off” all digits

after the decimal point

Using the INT Function

Using the ROUND Function• ROUND adjusts a value of a specified digit.• The value is made larger or smaller, depending on

the digit being rounded.• ROUND has two arguments:• The value/cell to be rounded• The number of digits used for rounding

Use TRUNC function• The TRUNC function truncates all digits after a

specified number of digits• With this function, you can specify that some

decimal places be maintained, unlike with the INT function

Using the FV function• The FV (future value) function can be used to determine the

value of a savings plan at some point in the future• This function assumes that periodic, constant payments are

made and the interest rate is fixed

Creating Hyperlinks● A hyperlink is a clickable object or text that, when

clicked:○ Displays another file○ Opens another program○ Shows a web page○ Displays an e-mail address

● To create a hyperlink:○ Click the Hyperlink button on the Insert command tab○ Right-click the cell and choose Hyperlink from the shortcut

menu○ Press [Ctrl]+[K]

Working with Nested Functions● A Nested Function is a function within another

function● The argument for the main function is another

function● Use a combination of the Function Arguments

Dialogue Box, the formula bar, and the Name Box to build nested functions

Working with Nested Functions

Example of a Nested Function=IF(SUM(B2:B4)>100,Average(F2:F4),0

=IF(SUM(B2:B4)>100 Logical_test

AVERAGE(F2:F4) Value_if_true

0 Value_if_false

Working with Nested Functions

Using Lookup Functions● Lookup functions displays a result by scanning

columns or rows in a lookup table● A lookup table is a cell range● VLOOKUP scans columns in a lookup table● HLOOKUP scans rows in a lookup table● A lookup table can be vertical or horizontal

Using VLOOKUPThe VLOOKUP is

looking for a match in the TCodes range (on a different sheet) for the contents of the Code column (I4.) It checks the second column in

TCodes to find the diagnosis name to be

shown in cell J4 (Astigmatism)

Using HLOOKUPThe HLOOKUP function is

looking for a match in the TDiscounts range

(on a different sheet) for the contents of the

Discount row. It checks the second row in

TDiscounts to find the discount percent to be

shown in cell D6.