CIS200 – Homework #1 – Simple Formulas & Functions



CSE 1111 – Homework #1 (15 points)– Simple Formulas & Functions

[pic]

Problem Description:

Your video store is considering buying some PC games. You have obtained prices at both Best Buy and at HR Gregg. Some of these games are previously released (R) and available now, others are not yet released (U) but can still be purchased now for pickup at a later date. Both stores have agreed to give you a discount of 35% if you buy the entire bundle (all games at the same time). This discount is given in cell C3. The sales tax rate is 5.75 % as indicated in cell C2 and named tax.

Please note when writing formulas, to receive full credit you must use correct Excel syntax (ie:use * for multiplication, / for division, etc.). Do not use unnecessary $ or functions. Also note, when writing your formulas, use cell references whenever possible.

1. (1 point) Write a formula in cell E5 that can be copied down the column, to calculate the cost difference of the HR Gregg price as compared with the Best Buy price. (A negative number means the HR Gregg price is more than the Best Buy price).

2. (1 point) Write a formula in cell C11, which can be copied across the row, to calculate the total price of all games from this store (excluding discount). Use a function.

3. (2 points) Write a formula in cell C12, which can be copied across the row, to calculate the cost of the order if you purchased all games (discounted bundle price, excluding tax).

4. (2 points) Write a formula in cell C13, which can be copied across the row, to determine the bundle costs after tax. Round the amount to the nearest cent and use the named range in your formula.

5. (1 point) Write a formula in cell F5, which can be copied down the row and across the column into range F5:G9, to determine this game’s percent of total price for all games (w/o discount) from the corresponding store. Example: cell F5 would be the game Civilizations cost as a percentage of the total price for all games from Best Buy.

6. (1 point) Write a formula in cell C16 to calculate the total number of different games in the game bundle.

7. (1 point) Write a formula in cell C17 to calculate the average price of the game bundle including tax (average of Best Buy and HR Greg).

8. (1 point) Write a formula in cell C18 to calculate the cost of the least expensive game from either store.

9. (2 points) Write a formula in cell C19 to determine the number of items which are less expensive at Best Buy.

10. (3 points) Write a formula in cell F17 to determine the total cost of Released games (R) at BestBuy. Write the formula so that it can be copied to the range F17:G18 to indicate the total costs before tax for Released (R) and Unreleased games (U) for both BestBuy and HHGregg. For example, cell G18 should display the total costs of unreleased games for HHGregg.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download