Excel Functions

IF Function

An IF function is made up of 3 parts:

A condition

What to do if the condition is met

What to do if the condition is not met.

An example of an IF function is shown below:

TIP: =IF(A5=”Pineapple”,TRUE,FALSE).

=IF (A5=” Pineapple”, TRUE, FALSE)  

Breakdown of the formula

The first part of the formula (the condition)

The first part of the formula (A5=Pineapple) is a condition. In this example it is testing to see if

cell A5 contains pineapple.

The second part of the formula

The second part of the formula (True) is telling Excel what to do if the above

condition is met. In this case, then it should write true

but if A5 does not contain pineapple it should write false.

Nested IF Function

Nested IF functions contain a function within another function. They can handle MORE

than two outcomes (A limitation of the normal IF Function)

Sometimes nested IF functions can contain several functions nested within each other.

How to do it:

(1) For this task, three conditions exist:

>=90 should display ‘Distinction’ (Greater than or equal to 90)

>=70 should display ‘Excellent’ (Greater than or equal to 70)

>=60 should display ‘Good’ (Greater than or equal to 60)

Place the cursor into cell D3 and change the formula so that it becomes:

=IF(C3>=90,”Distinction”, IF(C3>=70”Excellent”, IF(C3>=60”Good”)))

(2) For this task, three conditions exist:

>=20 should display ‘Gold’ (Greater than or equal to 20)

>=10 should display ‘Silver’ (Greater than or equal to 10)

<5 should display ‘No Medal’ (Less than 5)

Place the cursor into cell F2 and change the formula so that it becomes:

=IF(D3>=20,”Gold”, IF(D3>=10,”Silver”, “No Medal”))

Using Lookups

The term ‘Look up’ as used in the examinations, means to look up (search) from a list.

There are two main LOOKUP functions that can be used within Excel. These are:

HLOOKUP

VLOOKUP

HLOOKUP (Horizontal Lookup)

HLOOKUP is a function that performs a horizontal look-up of data.

HLOOKUP should be used when the values you wish to compare your data with are stored in a single row.

VLOOKUP

VLOOKUP (Vertical Lookup)

VLOOKUP is a function that performs a vertical look-up of data.

VLOOKUP should be used when the values you wish to compare your data with are stored in a single column.

The values to be looked up are stored in the columns to the right of the comparison

values. This is shown in the example below

Find the Vlookup  of the profit for shoe

A                                              B                                  C                                  D

=Vlookup(A3,A3:D8,4,2) the value should be 5,000

Formula breakdown;  A3 is the cell that contain shoe, A3:D8 is the whole table that contains the items, 4 is the row number of the profit, 2 is exact match to give the same amount.