VLook UP Exercise

Spreadsheet Exercises

Using the VLOOKUP() statement

1. Students are graded by A,B,C,D,E,F according to their
marks as follows

100-95 A
94-90 B
89-85 C
84-80 D
79-75 E
74 and below F

Design a spreadsheet that takes the students name and mark out of 20
and allocates the grade using a vlookup() statement. Place
sample data in your spreadsheet to test your solution

2. A company employs workers on a commision basis.
If they sell less than $10,000 a month they earn $1,000, if they sell over
$10,000 they earn 15% of their sales but if they earn over $50,000 they
only get $7,500 flat.

Design a spreadsheet that will calculate the wages for employees working
for the company and enter sample data to test your solution using
a vlookup() statement..

3. A farmer is testing four types of drench. The drench
costs different amounts and the farmer wants to calculate how much it will
cost to drench his mobs.

Cydectin 20c per sheep
Invoctomax 25c per sheep
Protectall 15c per sheep
Killverm 18c per sheep

fill in the spreadsheet to calculate the cost per mob using vlookup()
statements based on some data cells.

	A	B	C	D	E
	Mob	Size	Drench	Cost per sheep	Total cost
1	Flats	200	cydectin
2	NE paddock	75	invectomax
3	2 yo ewes	163	protectall
4	Malcolm	170	invectomax
5	W paddock culls	82	killverm

Modify your spreadsheet so that the drench cells are validated against
the data cell list

4. An airline flies to the destinations shown. The
cost of tickets and times of departure and arrival are shown below

A	B	C	D
Destination	Departure	Arrival	Cost
Sydney	10.00	13.30	$200.00
Adelaide	10.30	11:30	$180.00
Melbourne	11.15	14.15	$150.00
Darwin	09:00	15:00	$220.00
Alice Springs	09:45	12:00	$195.00
Perth	09:30	16:00	$300.00

Using the information in a set of data cells, design a spreadsheet that
will retrive the departure and arrival times and give the cost for passengers
when they order a ticket using vlookup() statments.

Validate the passenger's destination against the destination list

5. Make up some AFL player details and fill in the
table below

A	B	C	D	E	F
Name	Possessions	Effective Disposals	Marks	Handballs	Howlers

A commentator relies upon an assistant selecting a player, when he has
possession, to display the stats for the player.

Design a spreadsheet using a vlookup() statement to retrieve the player's stats. Validate the player selection against the players names to make it easier for the assistant.

Commentator's screenshot

A	B	C	D	E	F
Name	Possessions	Effective Disposals	Marks	Handballs	Howlers
	.	.	.	.	.