CIS200 – Homework _1 – Simple Formulas _ Functions.doc
Document Sample
Name __________________________ Lecture Time _____________
CSE101 – Homework #3 – Boolean Logical Functions
Rates!
A B C D E F G H
1 Costs Discounted Costs
2 Rental Agency Insurance Full-size Mid-size Compact Full-size Mid-size Compact
3 Avis TRUE $ 550.00 $ 350.00 $ 150.00 $ 440.00 $ 280.00 $ 150.00
4 Hertz TRUE $ 450.00 $ 425.00 $ 250.00 $ 360.00 $ 340.00 $ 250.00
5 Enterprise FALSE $ 350.00 $ 300.00 $ 275.00 $ 280.00 $ 300.00 $ 275.00
6 Budget FALSE $ 600.00 $ 300.00 $ 200.00 $ 480.00 $ 300.00 $ 200.00
Analysis!
A B C D E F G H
1 Budget $ 400.00
2 Can Afford Need to buy # cars
3 Rental Agency Full-Size Mid-Size Compact insurance separately affordable Todd Frank
4 Avis FALSE TRUE TRUE FALSE 2 TRUE TRUE
5 Hertz FALSE FALSE TRUE FALSE 1 FALSE FALSE
6 Enterprise TRUE TRUE TRUE TRUE 3 FALSE FALSE
7 Budget FALSE TRUE TRUE TRUE 2 FALSE FALSE
8
9 Can I get all Mid-Size cars from all of the companies? FALSE
10 Are there any companies that you can afford to rent a full-sized car from? TRUE
11 No cars within budget? FALSE
12 Who will you choose? Enterprise
Problem Description:
You are thinking about renting a car for a week to use on your vacation. To help determine which car to
rent, you have called four competing rental agencies and listed their prices for Full-size, Mid-size, and
Compact cars. Sheet rates! contains the weekly rental prices for each car type at each rental agency.
Column B indicates whether or not the rental company includes vehicle damage insurance in their prices.
On sheet analysis! you will complete the necessary analysis to determine which car is best for you.
Included on sheet analysis! is the amount you have budgeted for weekly car rental. You have named this
cell (C1) budget .
Please be sure to use the sheet name and an exclamation point in front of the cell reference when referring
to cells on different worksheets.
1. (1 point) Write a formula in cell analysis!B4, which can be copied across the row and down the
column to determine (True/False) if this rental car is affordable (within your budget) for your
vacation.
=Rates!C3 <= budget (also Rates!C3 <= Analysis!$C$1
2. (1 point) Write a formula in cell analysis!E4, which can be copied down the column to determine
(True/False) if the you need to purchase insurance separately for this vehicle rental from Avis. Note
that you only have to purchase separate insurance if the company has not included insurance in the
rental fee.
= NOT(Rates!B3)
3. (2 points) Write a formula in cell analysis!F4, which can be copied down the column, to determine
the number of types of cars that are affordable from each company.
Cse101 Hw3c.doc
=Countif(B4:D4, True)
4. (1 point) Write a formula in cell analysis!G9, to determine (True/False) if all Mid-size cars are within
your budget. Use a Boolean function.
=And(C4:C7)
5. (1 point) Write a formula in cell analysis!G10, to determine (True/False) if there are any companies
that you can afford to rent a full-sized car from.
=OR(B4:B7)
6. (3 points) Write a formula in cell analysis!G11 to determine (True/False) if none of the cars from any
rental agency are within your budget.
=NOT(OR(B4:D7)) also And(not(b4), not(b5)….would need all cells listed
7. (3 points) Your friend Todd is also interested in renting a car and also has a budget of $400. In
addition to budget constraints, Todd also has a few requirements for the agency he rents from. Write a
formula in cell analysis!G4, which can be copied down the column, to determine (True/False) if this
rental agency meets Todd’s criteria.
Todd does not want to buy insurance separately. Thus he only rents a car from a company if that
company has already included insurance fee in the rental costs.
Todd prefers either a mid-sized or a full-sized car. So he will only rent from a company that he
can afford either a mid-sized or a full-sized car.
= AND(Rates!B3, Or(B4,C4)) or AND(Not(E4), Or(B4,C4))
8. (2 points) After helping out your friend Todd, your friend Frank was so impressed that he has asked
you to determine which rental agencies meet his criteria. Write a formula in cell analysis!H4, which
can be copied down the column, to determine (True/False) if this rental agency meets Frank’s criteria.
Frank wants a mid-size car that costs less than $300 per week
Alternatively Frank would accept a Compact car that costs less than $200 per week.
=OR(rates!D3<300, rates!E3<200)
9. (3 points) After talking to a travel agent, they told you that if you were to rent the car through them,
that they could get you a 20% discount if the cost of the car was at least $350. In cell rates!F3 write a
formula, which can be copied down the column and across the row, to determine the discounted cost
of the rental.
=If(C3<350,C3, 0.8*C3) or =If(C3<350,1, 0.8)*C3
10. (3 points) After thinking about it for a while, you have decided to rent a full-size vehicle. Much to
your dismay you have found out that Avis and Hertz will not be able to provide you with a car at all.
Of the two remaining agencies, you would like to write a formula in cell analysis!G12 to determine
which one to rent from. You prefer to rent from Budget (as you have had experience from them
before), so if the discounted cost from Budget is within your $400 rental budget, the formula should
display “Budget.” If you can’t afford Budget’s car, the formula should display “Enterprise” if
Enterprise’s discounted cost is affordable. If neither company is affordable, the formula should
display “None”.
=IF(rates!F6<=budget, "Budget", IF(rates!F5<=budget, "Enterprise", "None"))
Cse101 Hw3c.doc
Shared by: yan198555
Related docs
Other docs by yan198555
