CSE200 AU05 KREEVES QUIZ#3 SEAT# ____________

Lecture: MW 8:30-10:18am NAME _____________________________________________

Lab section (check one) 2 pt: _______ R 9:30-11:18am _______ R 11:30-1:18pm

UNITS

AVERAGE(number1,number2,…)

MAX(number1,number2,…)

MIN(number1,number2,…)

FV(rate, nper, pmt, pv, type)

NPER(rate, pmt, pv, fv, type)

PMT(rate, nper, pv, fv, type)

PV(rate, nper, pmt, fv, type)

RATE(nper, pmt, pv, fv, type)

QUIZ3

Circle your answer

HLOOKUP(lookup_value, table_array, row_index_no, range_lookup_type)

VLOOKUP(lookup_value, table_array, col_index_no, range_lookup_type)

1. (8 pts) Write an Excel formula in cell F4, which can be copied down and across to cell H8, to determine the equivalent cost of the Simple Plan cd in euro. Note: conversions are in the units! worksheet.

=$C4/units!A$3

2. (8 pts) Write an Excel formula in cell I4, which can be copied down to I8, to determine the average track size in centimeters for the Simple Plan cd. Note: conversions are in the units! worksheet. Hint1: you want to find cm/track! Hint2: the # tracks needs to be used for this problem; don’t forget it represents the # tracks in the cd.

=units!B$5 / units!B$7 / B4

=1 / ( B4 * units!B$7 / units!B$5)

3. (8 pts) Write an Excel formula in cell B10 (not shown) to determine the monthly payments you will need to give to the bank when you open your new music store. You are borrowing $50,000 for 5 years with an interest rate of 4.5% compounded quarterly. Hint: there are 3 months per quarter.

=PMT(0.045/4,5*4,50000)/3

4. (8 pts) Write an Excel formula in cell B11 (not shown) to determine if (true or false) you invested the payment in question #3 at 10% compounded monthly you would have enough money to pay for your music store 3 years down the road.

=FV(0.1/12,3*12,B10)>=50000

5. (8 pts) Write an Excel formula in cell B12 (not shown) to determine the artist that has the largest number of tracks on their cd. Note: the answer is Shania Twain.

=VLOOKUP(MAX(B4:B8),B4:D8,3,FALSE) true ok as well!

6. (8 pts) I want to set up certain discounts for buying cds based on the type of music where country cds have a 10% discount, rock cds have a 20% discount and christian cds have a 30% discount. Fill in the worksheet below such that an HLOOKUP can be used to determine the discount for a specific type of music cd.

country	rock	christian
10%	20%	30%