CIS200 MIDTERM
Spring 2000 KReeves MTWF 8:30am
Seat #______Points: ____ /250
Name ______
Circle Lab Day/Time:
Wed 9:30am Wed 11:30am Thur 7:30am
Instructions:
· Do not open your exam until it is time to begin.
· Put away all books, papers, and calculators.
· Read each question carefully and fill in the answer. Answers must be legible or they will be marked incorrect.
· Please make sure you have all pages before you start this exam.
FORMULAS (Required arguments are shown in bold)
Reference Functions
HLOOKUP(lookup_value, table_array, row_index_no, range_lookup)
VLOOKUP(lookup_value, table_array, col_index_no, range_lookup)
Financial Functions
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)
Logical Functions
AND(logical1, logical2,…)
NOT(logical)
OR(logical1, logical2,…)
IF(logical_test,value_if_true, value_if_false)
Statistical Functions
AVERAGE(number1, number2, …)
MAX(number1, number2, …)
MIN(number1, number2, …)
Math Functions
COUNTIF(range, criteria)
SUM(number1, number2, …)
SUMIF(range, criteria, sum_range)
We are tracking three friends who are entered in the Olympic trials track meet. They are trying to earn the right to go to the Olympics in Paris, France or the Worlds in Mexico City.
Below is an explanation for each worksheet:
· TIMES - This worksheet has values entered in each cell which pertains to how fast a person completed the race for the given event. Each race time in measured in seconds and the distance of each event is measured in meters. If the value in the cell is 0, this means that the person did not participate in that event. The 100 and 200 meter events are considered short distance events, and the 400 meter events are considered long distance events.
· ANSWERS – This worksheet shows you where to place the answers for each test question 1-7, as well as, the actual value of each answer.
· QUALIFY - In order to go to the Worlds track meet, an athlete must run equal to or better than the qualifying time in 2 or 3 events. In order to go to the Olympic track meet, an athlete must run equal to or better than the qualifying time in 4 or 5 events. The qualifying time is given in this worksheet and associated with the appropriate event in the range A2:E3. Also defined in this worksheet, is the award system for the Olympic trials track meet where a special colored ribbon is given to those athletes who have qualified in a given number of events.
· UNITS – Contains some conversion factors you may need.
· EXPENSES – Now that we know who is going to the Olympics (those athletes who qualified in 4 or 5 events), how much is it going to cost to get there?! There are four categories of cost associated with the trip:
1. Training à training fees are calculated using the training cost and the total number of hours needed to train (see UNITS! worksheet)
2. Event entry fees à the entry fee cost per event is given in the UNITS! worksheet
3. Uniform à costs are given
4. Travel & lodging à costs are given
Some of these values are calculated through a test question (original cell is highlighted in gray); the rest are given.
REMEMBER:
Ø Use cell references whenever possible.
Ø Use sheet names only when necessary.
Ø Make sure that you write all formulas so that they will work even if the data changes.
Ø Partial points may be deducted if your answer is incredibly long and could have been significantly shorter.
1. (15 pts) Write an EXCEL formula in cell ANSWERS!B3 to show whether or not Kayleah participated in the 100 m Run. If she did participate, put a TRUE in the cell. If she did not participate in the event, put a FALSE in the cell. Copy the formula across and down in range ANSWERS!B3:D7 to fill in for each person and event.
______
2. (15 pts) Write an EXCEL formula in cell ANSWERS!G9 to determine if Kayleah participates in at least one running event and at least one hurdle event. Copy the formula across the row to cell ANSWERS!I9.
______
3. (15 pts) Write an EXCEL formula in cell ANSWERS!G10 to determine if Kayleah participates in all of the short distance events but none of the long distance events. Copy the formula across the row to cell ANSWERS!I10.
______
4. (25 pts) Write an EXCEL formula in ANSWERS!G3 to determine if Kayleah has a qualifying time for the 100 Run. Copy the formula across and down in range ANSWERS!G3:I7 to fill in for each person and event.
______
5. (10 pts) Write an EXCEL formula in ANSWERS!G12 to determine how many events Kayleah qualified for. Copy the formula across the row to cell ANSWERS!I12.
______
6. (20 pts) Write an EXCEL formula in ANSWERS!G13 to determine what color of ribbon, if any, that Kayleah was awarded. Leave the cell blank if a ribbon was not earned. Copy the formula across the row to cell ANSWERS!I13.
7. (20 pts) The athlete goes to the “Olympics” if they made the qualifying time in 4 or 5 events, the “Worlds” if they made the qualifying time in 2 or 3 events, and stays “Home” if they made the qualifying time in less than 2 events. Write an EXCEL formula in ANSWERS!G14 to determine which track meet, if any, Kayleah will participate in next. Copy the formula across the row to cell ANSWERS!I14.
8. (10 pts) Define “Goal Seek”.
9. (5 pts) In a reference function, there is a 4th argument that is optional. What does it mean when this argument has a TRUE value?
10. (25 pts) Write an EXCEL formula to determine how fast, in miles/hour, Kayleah was running during the 100 Run?
______
11. (15 pts) Write an EXCEL formula in cell EXPENSES!B3 to determine the training cost for Allison. Training costs are the same for everybody. Copy the formula across the row to cell EXPENSES!F3.
12. (10 pts) Write an EXCEL formula in cell EXPENSES!B5 to determine the entry fees for Allison. Copy the formula across to cell EXPENSES!F5.
______
13. (20 pts) Write an EXCEL formula in cell EXPENSES!H3 to determine the total expenses for people who qualified with exactly 4 events.
______
14. (5 pts) Write an EXCEL formula in cell EXPENSES!H9 to determine the total expenses for the whole team.
______
15. (20 pts) Allison’s parents borrowed the total expense amount for her trip to the Olympics. The loan is a 3 year loan with a 7% per year compounded quarterly interest rate. What are the monthly payments Allison’s parents are paying to send their daughter to the Olympics?
______
16. (20 pts) The Olympic coaches know that if they can get the Olympic Committee to invest money now instead of 2 years down the road for the next crop of USA Olympic hopefuls, they will save money. If the coaches estimate they will need $250,000 to send the next set of Olympic athletes to the Olympics, and the current interest rate is 6% compounded monthly, what is the amount of money they need to invest today?
______