LOGO

Chapter VIAdvanced

Functions

1

LOGO

Overview

INTRODUCTION NESTED FUNCTIONS LOOKUP VLOOKUP COUNTIF SUMIF IF ROUND THE PMT, IPMT AND PPMT FUNCTIONS IN

EXCEL

2

LOGO

Introduction

This section Goes a little further and looks at the more advanced types of formulas and functions you can use.

In other words, this section will raise your level from skilled user to advanced user.

Excel has a myriad of functions that you can use in your formulas.

3

LOGO

Nested Functions

When you write a formula, you can use functions as arguments inside other functions. You might

Want to calculate the square root of a sum of a series of cells. It could look like this:

=SQRT(SUM(B2:B20))

4

LOGO

Nested Functions

Nested functions use a function as one of the arguments of another function. You can nest up to 64 levels of functions. The following formula sums a set of numbers (G2:G5) only if the average of another set of numbers (F2:F5) is greater than 50. Otherwise it returns 0.

=IF(AVERAGE(F2:F6)>5,SUM(F2:F6),0)The AVERAGE and SUM functions are

nested within the IF function.5

LOGO

LOOKUP

It returns a value either from a one-row or one-column range or from an array,

the LOOKUP functions has two syntax forms: the vector form the array form.

6

LOGO

Vector form

A vector is a range of only one row or one column.

The vector form of LOOKUP looks in a one row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range.

7

LOGO

Vector form

LOOKUP (lookup_value,lookup_vector,result_vector)

Lookup_value: A value that LOOKUP searches for in the first vector.

Lookup_vector: A range that contains only one row or one column.

Result_vector: A range that contains only one row or column. It must be the same size as lookup_vector.

8

LOGO

Remarks

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.

9

LOGO

Example

10

LOGO

Example

11

Formula Description (Result

=LOOKUP(4.19,A2:A6,B2:B6) Looks up 4.19 in column A, and returns the value from column B that's inthe same row (orange)

=LOOKUP(5.00,A2:A6,B2:B6) Looks up 5.00 in column A, matches the next smallest value (4.19), andreturns the value from column B that's in the same row (orange)

=LOOKUP(7.66,A2:A6,B2:B6) Looks up 7.66 in column A, matches the next smallest value (6.39), andreturns the value from column B that's in the same row (blue)

=LOOKUP(0,A2:A6,B2:B6) Looks up 0 in column A, and returns an error because 0 is less than thesmallest value in the lookup_vector A2:A7 (#N/A)

LOGO

Array form

The array form of LOOKUP looks in the first row or column of an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns.

12

LOGO

Array form

Use this form of LOOKUP when the values that you want to match are in the first row or column of the array.

Use the other form of LOOKUP when you want to specify the location of the column or row.

13

LOGO

Array form

LOOKUP(lookup_value,array)

Lookup_value: A value that LOOKUP searches for in an array.

Lookup value can be a number, text, a logical value, or a name or reference that refers to a value.

Array: A range of cells that contains text, numbers, or logical values that you want to compare wit lookup_value.

14

LOGO

Example

15

LOGO16

Formula

=LOOKUP(A2,{0,60,70,80,90},{"F","D","C","B","A"})

=LOOKUP(A3,{0,60,70,80,90},{"F","D","C","B","A"})

=LOOKUP(A4,{0,60,70,80,90},{"F","D","C","B","A"})

=LOOKUP(A2,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

=LOOKUP(A3,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B- ","B","B+","A-","A","A+"})

=LOOKUP(A4,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

LOGO

VLOOKUP (Vertical Lookup)

VLOOKUP finds values in a table using a

search value.

The syntax is:=VLOOKUP ( lookup_value;

table_array; col_index_num;equals)

17

LOGO

VLOOKUP (Vertical Lookup)

18

LOGO

VLOOKUP (Vertical Lookup)

In Figure there is an example of the membership index. To the left is the actual table in the cell range A4:D11 with members listed.

To the right of the table, there is something which can look up members using the number typed into cell F4.

To be able to look up members' names, the formula in cell G4 is as follows:

=VLOOKUP (F4, A4: D13, 2, FALSE) To look up address and telephone number the

col_index_num should be 3 and 4 respectively.

19

LOGO

VLOOKUP (Vertical Lookup)

Equals: A logical value that specifies

whether you want VLOOKUP to find an

exact match or an approximate match:

If TRUE or omitted, an exact or approximate

match is returned.

If FALSE, VLOOKUP will only find an exact

match.

20

LOGO

VLOOKUP (Vertical Lookup)

Equals: A logical value that specifies

whether you want VLOOKUP to find an

exact match or an approximate match:

If TRUE or omitted, an exact or approximate

match is returned.

If FALSE, VLOOKUP will only find an exact

match.

21

LOGO

VLOOKUP (Vertical Lookup)

If you did not find what you searched show Message Employee Not found.

=IF(ISNA(VLOOKUP(L10,City,2,FALSE)) =

TRUE, "Employee not found", VLOOKUP(L10,City,2,FALSE))

22

LOGO

COUNTIF

Counts the number of cells within a range

that meet the given criteria.

SyntaxCOUNTIF(range,criteria)

23

LOGO

COUNTIF

24

LOGO

COUNTIF

25

Formula Description (result)=COUNTIF(A2:A5,"apples") Number of cells with apples in the first column

above (2)

=COUNTIF(A2:A5,A4) Number of cells with peaches in the first column above (1)

=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2) Number of cells with oranges and apples in the first columnabove (3)

=COUNTIF(B2:B5,">55") Number of cells with a value greater than 55 in the secondcolumn above (2)

=COUNTIF(B2:B5,"<>"&B4) Number of cells with a value not equal to 75 in the secondcolumn above (3)

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")

Number of cells with a value greater than or equal to 32 andless than or equal to 85 in the second column above (3)

LOGO

COUNTA

Counts the number of cells that are not empty

Use COUNTA to count the number of cells

that contain data in a range or array.

SyntaxCOUNTIF(range,criteria)

26

LOGO

COUNTA

27

LOGO

COUNTBLANK

Counts empty cells in a specified range of cells.

Syntax

=COUNTBLANK(range)Range is the range from which you want to

count the blank cells.

=COUNTBLANK(A2:B5)

28

LOGO

SUMIF

Adds numbers in a table together on condition that they, or other values in same row, meet a certain criterion

Syntax

=SUMIF (range,criteria,sum_range)

29

LOGO

SUMIF

30

LOGO

SUMIF

=SUMIF(A2:A5,">160000",B2:B5) Sum of the commissions for property

values over 160,000 (63,000)=SUMIF(A2:A5,">160000") Sum of the property values over 160,000

(900,000)=SUMIF(A2:A5,"=300000",B2:B3) Sum of the commissions for property

values equal to 300,000 (21,000)

31

LOGO

IF

Use IF to conduct conditional tests on values and formulas, This function returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Up to seven IF functions can be nested to construct more elaborate tests.·

SyntaxIF(logical_test,value_if_true,value_if_fal

se)32

LOGO

IF

33

=IF(A2<=100,"Within budget","Over budget")

If the number above is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget" (Within budget)

=IF(A2=100,SUM(B5:B15),"")

If the number above is 100, then the range B5:B15 is calculated. Otherwise, empty text ("") is returned ()

LOGO

IF

34

=IF(A2>89,"A",IF(A2>79,"B",IF(A2>69,"C",IF(A2>59,"D","F"))))

Assigns a letter grade to the first score (F)=IF(A3>89,"A",IF(A3>79,"B",IF(A3>69,"C",

IF(A3>59,"D","F"))))Assigns a letter grade to the second score

(A)=IF(A4>89,"A",IF(A4>79,"B",IF(A4>69,"C",

IF(A4>59,"D","F"))))

LOGO

Rounding Numbers

35

Rounds a number to a specified number of digits, for the number argument you can either enter a number or a cell reference. For the num digits argument you need to specify the number of digits to which you want to round.

=ROUND (number,num_digits) If num_digits is greater than zero, then number is rounded to the specified number of

decimalplaces. If num_digits is 0, then number is rounded to the nearest integer. If num_digits is less than 0, then number is rounded to the left of the decimal point.

LOGO

Rounding Numbers

36

Examples:=ROUND (123.45,1) will equal 123.5=ROUND (123.45,0) will equal 123=ROUND (123.45,-2) will equal 100

LOGO

The PMT, IPMT and PPMT Functions in

Excel

37

This section summarizes how to use the three Excel payment functions, PMT, IPMT and PPMT to calculate loan, interest and capital repayments for each repayment period and help you understand how the proportions of interest and capital repayments change over the life of the

loan.

LOGO

The PMT, IPMT and PPMT Functions in

Excel

38

The parts of the PMT function are =PMT (Interest,periods, Amount).Suppose we enter the annual interest rate

as 4% in cell B2, the term in years as 25 in cell B3, and the loan amount 100,000 in cell B4. We want to calculate the monthly

repayment amount in cell B5.

LOGO

The PMT, IPMT and PPMT Functions in

Excel

39

Because we want to work out the monthly repayments, we need to enter the interest rate per month, and the loan term in months. So the interest rate will be B2/12 and the term will beB3*12

To calculate the monthly repayment in cell B5 we type the following:

=PMT (B2/12, B3*12,B4) and press the Enter key. The resulting outgoing

monthly payment will show as -527.84.To make this a positive use=-PMT(B2/12, B3*12, B4)

LOGO

The PMT, IPMT and PPMT Functions in

Excel

40

The parts of the IPMT functions are =IPMT (Interest, period number, periods,

Amount).You can see the extra element "period number"

in this function compared to the PMT function. In this example we want to calculate the interest

payment for period 1 (the first month).

LOGO

The PMT, IPMT and PPMT Functions in

Excel

41

Using the Excel PPMT Function to calculate the first month's capital repayment

The PPMT function works in the same way but calculates the capital repayment for each period

for the capital loan taken out over a term at a fixed interest rate. The parts of the PPMT

functions are =PPMT (Interest, period number, periods,

Amount).

LOGO42