VCE I. P. & M.

Excel Functions 2004

Unit 4
Outcome 1 Functions

Function
Vlookup

Worksheets
Fee Calculator

IF(AND)

Assignment

Exercise

Nordic Travel Services

6B

IF(OR)

Nordic Travel Services

IF

Motopartz

IF, VLOOKUP Garden Wholesalers

IF, VLOOKUP Tax

VLOOKUP

Fixtures

6A

The data for

worksheets
textbook: L
Greg Bowde

Each function
worksheet. C

Passwords: E
protected to li
data. The pas

04
Software: Microsoft Excel
Acknowledgement:
The data for some illustrations used in these
worksheets has been obtained from the
textbook: Learning Microsoft Excel 2000, by
Greg Bowden, Cambridge 2000.
Geoff Moss
Each function is hyperlinked to the appropriate
worksheet. Click on the function you want to go to.
Passwords: Each spreadsheet has been password
protected to limit accidental erasure of formulae and
data. The password used throughout is: pw

HONEST JOHN'S REAL ESTATE

Selling Price

20000

Change data here

Set Fee:

400.00

On Price:

8000.00

VLOOKUP(B3,Data!A7:C9,2)
VLOOKUP(B3,Data!A7:C9,1)

Price Over:
Rate Over:
Extra Fee:
Total Fee:

12000.00
0.03
360.00
760.00

Table or cell range; In Data Worksheet

Column number with data

VLOOKUP(B3,Data!A7:C9,3)

Lookup functions

Assignment 7
Assignment 11

HOME / COVER

VLOOKUP Function
The Data worksheet contains the table for
the Vlookup function.

Information - see Data worksheet

Table or cell range; In Data Worksheet

Column number with data

Commission Rates
Price
$
0.00
8000.00
50000.00

Set Fee Rate Over

$
%
0.00
0.05
400.00
0.03
1660.00
0.02

Information:

On properties sold by Honest John's the commiss

q 5% on any amount up to $8000
q A set fee of $400, plus 3% on any amount abov
q A set fee of $400, plus 2% on any amount abov

Selected cell range for Fee Calculator is A

Cell Range A7:C9

Fee Calculator
Column Column
1
2
Column 3

Honest John's the commission charged is as follows:

up to $8000
us 3% on any amount above $8000
us 2% on any amount above $50,000

for Fee Calculator is A7:C9

NORDIC TRAVEL SERVICES

Staff Bonuses, June 30, 2004
EMPLOYEE

Adam Able
Bruce Bartram
Cheryl Cantrell
Donald Donut
Ermin Earlybird
Freddo Frog

BONUS
IF(OR)

YEARS OF
SERVICE

OVERTIME
(Hours)

18
7
2
13
26
9

34
85
144
14
102
110

EXTRA
BONUS
$

BONUS
$

340
0
1440
140
1020
1100

Gift

OR

>10 years service

> 100 hours overtime

AND

>9 years service

> 100 hours overtime

Bonus of $10 for each hour of overtime IF

IF(OR(B8>10,C8>100),C8*10,0)
EXTRA BONUS
IF(AND)

Gift from the owner to the staff member IF

IF(AND(B8>9,C8>100),"Gift","")
Condition

If TRUE

If NOT
TRUE

HOME / COVER

>10 years service

> 100 hours overtime

>9 years service

> 100 hours overtime

MOTOPARTZ

Item

Price

Seat Covers
Monogram Carpet Mat Set
Gerni High Pressure Cleaner
Aunger Bonnet Protector
Clarion Speakers
Ferodo FT Disc Pads
Pioneer CD Tuner with MP3

$ 39.99
$ 99.00
$ 209.00
$ 59.99
$ 85.00
$ 29.95
$ 299.00

Staff
Staff
Discount Discount
Rate
Price

0.05
0.05
0.12
0.05
0.05
0.05
0.12

$ 2.00 $
$ 4.95 $
$ 25.08 $
$ 3.00 $
$ 4.25 $
$ 1.50 $
$ 35.88 $

IF(B5<100,5%,12%)
OR
We could obtain the same result by writing the function as:

IF(B5>99.99,12%,5%)
Condition

Net Price

If TRUE If NOT
TRUE

37.99
94.05
183.92
56.99
80.75
28.45
263.12

Information:
Staff will receive a discount of 5% if they buy goods
valued at less than $100. The discount is 12% for goods
valued at $100 or more.

HOME / COVER

Password

381 Eucalyptus Drive, Rosewood

Ph: 9876 5432

Customer No.:
Customer:
Trade (Y/N)
Date:
Qty

8-Oct-15

Current date: =NOW()

Item No.

Data Validation - Values: Y,y, N or n

Nested IF functions with Vlookup Function. Will

accept null value, numbers and "C" in B7.

Description

Unit Price

Amount
If Function with SUM function.
If null value in B14, then null
value.

Cell Protection: All cells are

protected, except for data cells: C7,
E9 and cell range A14:B29. If
students use cell protection they
should insert password on the
worksheet.

Vlookup Functions

Data Validation - numbers 1001 to 1024

Information
HOME / COVER
Sub-Total:
Sales Tax:
TOTAL:

$
$
$

If Function

pw

381 Eucalyptus Drive, Rosewood

1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024

Saxon Axe
Gardenmaster Axe
Saxon Block Splitter
Gardenmaster Block Splitter
Fiskars Telescopic Spade
Gardenmaster Shovel
Leaf and Grass Rake
Cyclone Hedge Shears
Hortex Loppers
Felco 5 Secateurs
Gardena 15mm Secateurs
Cyclone Handy Pruner
Garden-Rite Large Bypass Secateurs
Hachet
Nylex Premier Reinforced Garden Hose
GardenMate 2 Hour Tap Timer
Daytek Home Handy Barrow
Kelso Master Builder Wheelbarrow
Westmix Wheelbarrow
Tumbleweed Compost Bin
Sherlock Compost Bin
Gardenwise Worm Farm
Pope Duo Watering Set
Ozito 20m Hose Reel

$ 17.49
$ 19.85
$ 19.65
$ 22.95
$ 55.65
$ 23.99
$
3.20
$ 32.99
$ 22.50
$ 49.85
$ 22.89
$ 12.99
$ 13.45
$
5.45
$ 19.99
$ 16.75
$ 87.90
$ 185.00
$ 89.90
$ 54.60
$ 58.99
$ 99.00
$
9.99
$ 99.00

Password
Cell Protection: All cells are
protected. If students use cell
protection they should insert
password on the worksheet.

Information:

1 Customer's name and addres

customer number in cell B7.
2 Product description and unit p
inserting product number in c
3 Data validation in cells B14:B2
and in cell E9 for Y, v, N or n re
4 IF function in cells E14:E30 re
5 IF function in cell E31 respond
6 Cell protection of all cells not

pw

Protection: All cells are

ected. If students use cell
ection they should insert
sword on the worksheet.

Information:
Customer's name and address will be entered by inserting
customer number in cell B7.
Product description and unit price will be entered by
inserting product number in cells B14:B29.
Data validation in cells B14:B29 for range of product numbers
and in cell E9 for Y, v, N or n responses.
IF function in cells E14:E30 responds to cells B14:B29.
IF function in cell E31 responds to cell E9.
Cell protection of all cells not requiring datab entry.
Return to Invoice

381 Eucalyptus Drive, Rosewood

Number
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021

Customer
Ashwood Garden Centre
Burwood Garden Supplies
Curio Clever Gardens
Danish PlantyzaPlenty
Eltham Gardens
Flemington Saleyards Garden Supplies
Grand Gardens
Healthy Garden Products
Interesting Garden Gnomes
Japanese Gardens
Klean Garden Specialists
Mossy Garden Supplies
Naturally Garden Supplies
Orange Tree Gardens
Passion with Gardens
Quality Garden Products & Supplies
Rustic Gardens
Seaside Garden Designs
Tropical Garden Themes
Universal Gardens
Enter value between 1001 and 1002

Address
1156 High Street Road, Ashwood 3147
65 Burwood Road, Burwood 3125
75 Mountain Highway, Bayswater 3153
365 Main Road, Lower Plenty 3093
1004 Main Street, Etham 3095
65 Epsom Road, Flemington 3031
465 Boronia Road, Wantirna 3152
12 The Mall, Wantirna 3152
76 Main Street, Croydon 3136
88 Dublin Road, Ringwood East 3135
87 Main Street, Lilydale 3140
54 Hull Road, Croydon, 3136
123 Yarra Road, Croydon North 3136
324 Mountain Highway, The Basin 3154
34 Kelletts Road, Rowville 3178
765 Burwood Highway, Ferntree Gully 3156
75 Stud Road, Bayswater 3152
54 Dorset Road, Boronia 3155
125 Stud Road, Knoxfield 3180
11 High Street Road, Wantirna 3152

An entry like this can act for data validation where numeric an
values are accepted in the data field, B7, in the Invoice works

Return to Invoice

Password pw
Cell Protection: All cells are
protected. If students use cell
protection they should insert
password on the worksheet.

idation where numeric and non-numeric

, B7, in the Invoice worksheet.

TAX SCHEDULE - USING VLOOKUP

Tax Rates 2000-2001
Income Base Tax Rate
0
0
0.00
6000
0
0.17
20000
2360
0.30
50000
11380
0.42
60000
15580
0.47

Annual Income:

Tax Payable:

Vlookup Function:
VLOOKUP(G3,A6:C10,2)+(G3-VLOOKUP(G3,A6:C

Enter your Income Below:

1 Find Base amount of tax as shown in column 2 o

Weekly:

2 Find difference between Annual Income and low

3 Multiply difference by relevant rate in column 3.

Annual:

Home / Cover

Password: pw

IF(B16<>"",B16*52,B18)
Converts weekly income to annual
If no weekly income, uses annual Income

:C10,2)+(G3-VLOOKUP(G3,A6:C10,1))*VLOOKUP(G3,A6:C10,3)

unt of tax as shown in column 2 of table range A6:C10.

between Annual Income and lower income in column 1.

nce by relevant rate in column 3.

Tax Information Sheet

Tax Information
1. No income tax is payable on incomes up to the $6000 threshold
2. On Income greater than $6000 but less than $20,000 the rate of taxation is 17% on any income in excess of $6000

3. On Income greater than $20,000 but less than $50,000, a base tax amount of $2360 is payable, PLUS 30% of income

4. On Income greater than $50,000 but less than $60,000, a base tax amount of $11380 is payable, PLUS 42% of incom

5. On Income greater than $60,000, a base tax amount of $15580 is payable, PLUS 47% of income in excess of $60,00

Home / Cover

Return to Tax Worksheet

Sports Fixture
Match

Venue

1
10
9
8
7

v
v
v
v
v

2
3
4
5
6

Reds
Rosellas
Lions
Tigers
Kangaroos

v
v
v
v
v

Blues
All Blacks
Roosters
Crows
Wallabies

Heathmont
Boronia
Croydon North
Wantirna
Parkwood

10
9
8
7
6

v
v
v
v
v

1
2
3
4
5

Rosellas
Lions
Tigers
Kangaroos
Wallabies

v
v
v
v
v

Reds
Blues
All Blacks
Roosters
Crows

Boronia
Croydon North
Wantirna
Parkwood
Warrandyte

v 10
v 1
v 2
v 3
v 4

Lions
Tigers
Kangaroos
Wallabies
Crows

v
v
v
v
v

Rosellas
Reds
Blues
All Blacks
Roosters

Croydon North
Wantirna
Parkwood
Warrandyte
Croydon Hills

9
8
7
6
5

Password: pw

VLOOKUP Function
Function looks up table range in the Teams
spreadsheet for the teams and the venue.
Venue is location of the first-named team.
Go to Teams
data Worksheet

Home / Cover

1
2
3
4
5
6
7
8
9
10

TEAMS
Reds
Blues
All Blacks
Roosters
Crows
Wallabies
Kangaroos
Tigers
Lions
Rosellas

LOCATION
Heathmont
Ringwood
Ringwood East
Croydon
Croydon Hills
Warrandyte
Parkwood
Wantirna
Croydon North
Boronia

Return to
Fixtures