CSE200 AU05
KREEVES QUIZ#3 SEAT# ____________
Lecture: MW
Lab section (check one) 1 pt: _______ R
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!
|
country |
rock |
christian |
|
10% |
20% |
30% |
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. TYPE IN ANY ORDER
