Basic Microsoft Excel formulas

profilekayladolan
cis300-hw2-template.xlsx
Home>Computer Science homework help>Basic Microsoft Excel formulas

Team ID

Homework Assignment 3
CIS-300- xx -4145
(McIntosh)
Team Members Hours
Last Name First Name 0.0
1
2
3
4
Please remember to review the Homework Instructions PDF file at the top of the BB > Assignments > Homework folder.
Important Note: This assignment is made up of 4 Parts - do not change the order!
Fall 2012

Part 1

Instructions: Review the Temperature Statistics table for March 2014 below. Write formulas (in cells I6:I24) that will answer the questions listed in cells J6:J26. State all numeric values to the nearest integer (whole number) unless directed otherwise.
Temperature Statistics March 2014 - Louisville, KY
Answers
Day of the Month Max. Temp. (°F) Min. Temp. (°F) Precipitation Day of the Month 54.4 xxx What was the mean of the high temperatures in March? (rounded to the nearest tenth)
1 56 34 1 75 xxx What was the highest temperature recorded for any day in March?
2 48 20 Rain-Snow 2 64 xxx What was the 8th highest maximum temperature recorded for any day in March?
3 25 15 Snow 3 28 xxx What was the 10th lowest minimum temperature recorded for any day in March?
4 35 13 4 68 xxx What was the maximum temperature on March 10th?
5 44 20 5 32 xxx What was the minimum temperature on March 20th?
6 49 25 6 0 xxx What type of precipitation occured on March 30th?
7 60 28 7 0 xxx What type of precipitation occurred on March 15th?
8 62 33 Rain 8 3 xxx Which day in March had the lowest maximum temperature?
9 51 28 9 28 xxx What day in March had the highest minimum temperature?
10 68 35 10 4 xxx How many days in March had any kind of Snow precipitation?
11 75 48 Rain 11 20 xxx How many days in March had no recorded precipitation?
12 66 30 Rain 12 59.8 xxx What was the mean temperature for all of the days in March that had any kind of Rain precipitation? (rounded to the nearest tenth)
13 44 21 13 54.1 xxx What was the mean maximum temperature for the first half of the month (i.e., days 1-15)? (rounded to the nearest tenth)
14 64 37 14 28.3 xxx What was the mean minimum temperature for the second half of the month (i.e., days 16-31)?
15 65 38 15 16 xxx How many days in March had a maximum temperature of at least 55°?
16 51 29 Rain-Snow 16 14 xxx How many days in March had a minimumm temperature at or below freezing?
17 46 28 17 7 xxx How many days in March had a maximum temperature between 35° and 45°? (inclusive)
18 52 33 18 1 point What percentage of the month of March had any kind of Rain?
19 57 44 Rain 19
20 58 32 20
21 70 42 21
22 60 41 22
23 45 32 23
24 44 26 24
25 39 28 Fog-Snow 25
26 44 21 26
27 62 36 Rain 27
28 67 50 Rain 28
29 50 40 Rain 29
30 58 32 30
31 72 35 31

Part 2

Instructions: As shown in the Classification Table below, contributors are classified as follows: (1) under $500: Supporter (2) $500 to $749.99: Patron (3) $750 to $1,249.99: Fellow (4) $1,250 or more: Blue Chip Write a lookup formula in cell C8 that displays the contibutor's (i.e., Fred Knott's) classification based on any contribution entered in C7. You should try changing the value in cell C7 to test your formula's accuracy.
Classification Lookup Classification Table
$0 Supporter
Name: Fred Knott $500 Patron
Contribution: $700 $750 Fellow
Classification: 1 point $1,250 Blue Chip

Part 3

Instructions: As shown in the Classification Table below, contributors are classified as follows: (1) under $250: Supporter (2) $250 to $749.99: Patron (3) $750 to $999.99: Fellow (4) $1000 or more: Blue Chip Write a lookup formula in cell C8 that displays the contibutor's (i.e., Tal Wilkenfield's) classification based on any contribution entered in C7. You should change the value in cell C7 to test your formula's accuracy.
Classification Lookup Classification Table
$0 $250 $750 $1,000
Name: Tal Wilkenfeld Supporter Patron Fellow Blue Chip
Contribution: $225
Classification: 1 point

Part 4

Instructions: Review the Price Quote Table, Price Table, and Discount Table below. Create a series of formulas that will calculate the Total Due from the Item and Quantity entered in cells C6 and C7, repsectively. In your worksheet you should do the following: (1) Enter a formula in cell C9 that looks up the item name (in cell C6) in the Price Table to find the Unit Price. (2) Enter a formula in cell C10 that calculates the Total Before Discount (i.e., Unit Price * Quantity). (3) Enter a formula in C11 that looks up the Total Before Discount in the Discount Table to find the Discount % and show the result in the Percentage number format. (4) Enter formulas in C12, C13, C14, and C16 that calculate the Discount Amount, the Total After Discount, the Sales Tax (based on a 6% sales tax rate), and the final Total Due for the order, respectively. (5) Round the Discount Amount and Sales Tax values to the nearest penny.
Price Quote Price Table
Answers Item Unit Price
Item: Widgets Connectors $2.10
Quantity: 250 Grommits $2.70
Hasps $3.60
Unit Price: xxx $ 2.80 Widgets $2.80
Total Before Discount: xxx $ 700.00
Discount %: xxx 10% Discount Table
Discount Amount: xxx $ 70.00 Amount Discount
Total After Discount: xxx $ 630.00 $0 0%
Sales Tax (6%): xxx $ 37.80 $300 5%
$500 10%
Total Due: xxx $ 667.80 $800 15%

Part 5

Instructions: Review the Price Quote Table, Price Table, and Discount Table below. Create a series of formulas that will calculate the Total Due from the Item and Quantity entered in cells C6 and C7, repsectively. In your worksheet you should do the following: (1) Enter a formula in cell C9 that looks up the item name (in cell C6) in the Price Table to find the Unit Price. (2) Enter a formula in cell C10 that calculates the Total Before Discount (i.e., Unit Price * Quantity). (3) Enter a formula in C11 that looks up the Total Before Discount in the Discount Table to find the Discount % and show the result in the Percentage number format. (4) Enter formulas in C12, C13, C14, and C16 that calculate the Discount Amount, the Total After Discount, the Sales Tax (based on a 6% sales tax rate), and the final Total Due for the order, respectively. (5) Round the Discount Amount and Sales Tax values to the nearest penny.
Price Quote Price Table
Answers Item Connectors Grommits Hasps Widgets
Item: Grommits Unit Price $1.40 $2.70 $3.60 $2.80
Quantity: 100
Unit Price: xxx $ 2.70
Total Before Discount: xxx $ 270.00
Discount %: xxx 0% Discount Table
Discount Amount: xxx $ - 0 Amount $0 $400 $600 $850
Total After Discount: xxx $ 270.00 Discount 0% 5% 10% 15%
Sales Tax (6%): xxx $ 16.20
Total Due: xxx $ 286.20

Part 6

Instructions: Review the Price Quote Table, Price Table, and Discount Table below. Create a series of formulas (similar to those created in Part 4) to complete the worksheet that will allow for multiple items. The Discount should be applied to the total amount for the order.
Price Quote
Item 1: Grommits Price Table
Quantity 1: 400 Item Unit Price
Item 2: Connectors Connectors $2.10
Quantity 2: 300 Grommits $1.80
Item 3: Widgets Hasps $3.60
Quantity 3: 200 Widgets $2.37
Item 4: Hasps
Quantity 4: 100
Answers
Unit Price 1: xxx $ 1.80 Discount Table
Unit Price 2: xxx $ 2.10 Amount Discount
Unit Price 3: xxx $ 2.37 $0 0%
Unit Price 4: xxx $ 3.60 $500 5%
Total Before Discount: xxx $ 2,184.00 $750 10%
Discount %: xxx 15% $750 15%
Discount Amount: xxx $ 327.60
Total After Discount: xxx $ 1,856.40
Sales Tax (6%): xxx $ 111.38
Total Due: xxx $ 1,967.78

Part 7

Instructions: Review the Price Quote Table, Price Table, and Discount Table below. Create a series of formulas (similar to those created in Part 5) to complete the worksheet that will allow for multiple items. The Discount should be applied to the total amount for the order.
Price Quote
Item 1: Grommits Price Table
Quantity 1: 400 Item Connectors Grommits Hasps Widgets
Item 2: Connectors Unit Price $1.40 $2.70 $3.60 $2.80
Quantity 2: 300
Item 3: Widgets
Quantity 3: 200
Item 4: Hasps
Quantity 4: 100 Discount Table
Answers Amount $0 $400 $650 $950
Unit Price 1: xxx $ 2.70 Discount 0% 5% 10% 15%
Unit Price 2: xxx $ 1.40
Unit Price 3: xxx $ 2.80
Unit Price 4: xxx $ 3.60
Total Before Discount: xxx $ 2,420.00
Discount %: xxx 15%
Discount Amount: xxx $ 363.00
Total After Discount: xxx $ 2,057.00
Sales Tax (6%): xxx $ 123.42
Total Due: xxx $ 2,180.42

Part 8

Instructions: (a) Enter a lookup formula in cell C8 that displays the Price of a ticket based on the Age and Ticket Location entered by a person in C6 and C7, respectively. (b) As in Part (a) enter a lookup formula in cell C9 that displays the Price of a ticket based on the Age and Ticket Location entered by a person in C6 and C7, respectively. However, in this case have the formula dispay an appropriate error message if the person enters an "Invalid Location" in Cell C7. Hint: Use the ISNA function.
Ticket Price Quote Ticket Price Table
Answers Ticket Location Age
Age: 18 18 and Under Over 18
Ticket Location: Premier Premier $60 $75
Price (a): xxx $ 60.00 Main Floor $40 $50
Price (b): 1 point Balcony $25 $35
Standing Room $10 $15

Part 9

Instructions: Review the Final Grades Table and Grade Distribution Table below. Enter a lookup formula in cell D7 that displays Adam Bucker's Final Course Grade. Make sure you use relative and/or absolute references, then apply the Fill-Down feature to complete cell D8:D20 so that all students receive the proper grade.
Final Grades
Student Name Total Points Grade Grade Distribution Table
Bucker, Adam 519 1 point Total Points Final Grade
Eldridge, Yolanda 641 xxx 0 F
Fickler, Marie 802 xxx 600 D
Grisham, Marica 950 xxx 700 C
Hendrix, Erik 675 xxx 800 B
Johnson, Brian 866 xxx 900 A
Knight, Billy 530 xxx
Matthews, Mary 835 xxx
Parm, David 877 xxx
Potts, Susie 754 xxx
Randolph, Karen 861 xxx
Reems, Harold 619 xxx
Smith, Tammy 507 xxx
Vernersha, Laura 878 xxx

Part 10

Instructions: Review the Allied Student Cell Phone Table, Charge Table, and Assumptions Table. According to the Charge Table, on the Allied Student Cell Phone Plan: (1) If you use less than 450 minutes in a month, you must pay $29.99 in usage charge. (2) If you use between 450 and 900 minutes, you pay $39.99 plus $0.13 per minute for each minute over the minimum in usage charge. (3) If you use more than 900 minutes, you pay $49.99 plus $0.07 per minute for each minute over the minimum in usage charge. In addition, the processing charge per month and sales tax percentage must be applied as listed in the Assumptions Table. Enter formulas in the Allied Student Cell Phone Table that allows a person to enter the minutes used (in cells C6:E6) and then calculate the usage charges, the processing charge, the tax amount, the total charge, and the average cost per minute.
Answers
Allied Student Cell Phone Charge Table Allied Student Cell Phone
Minutes Base Excess Per Minute
Minutes Used: 250 750 1,500 0 $29.99 $0.00 Minutes Used: 250 750 1,500
500 $39.99 $0.13
Base Charge: xxx xxx xxx 1,000 $49.99 $0.07 Base Charge: $ 29.99 $ 39.99 $ 49.99
Excess Minutes Charge: xxx xxx xxx Excess Minutes Charge: $ - 0 $ 32.50 $ 35.00
Processing Charge: xxx xxx xxx Processing Charge: $6.95 $ 6.95 $ 6.95
Charges Before Taxes: xxx xxx xxx Assumptions Charges Before Taxes: $ 36.94 $ 79.44 $ 91.94
Processing charge per month: $6.95
Tax Amount: xxx xxx xxx Sales tax %: 6% Tax Amount: $ 2.22 $ 4.77 $ 5.52
Total Charge: xxx xxx xxx Total Charge: $ 39.16 $ 84.21 $ 97.46
Average cost per minute: xxx xxx xxx Average cost per minute: $ 0.16 $ 0.11 $ 0.06

Part 11

Instructions: Complete the worksheet below as follows: (1) Use the Overtime Threshold (hours) to calculate Regular Pay. (2) Use the Overtime Rate to calculate Overtime Pay. Overtime is pay for all hours over the Overtime Threshold (hours). (3) Calculate Gross Pay which is the sum of the Regular Pay and the Overtime Pay. (4) Calculate Taxable Pay which is Gross Pay minus (Deduction Per Dependent times the Number of Dependents). (5) Calculate Withholding Tax which is based on the Taxable Pay and the Tax Table (H16:I20). Use a lookup function to determine the tax rate, then multiply it by the Taxable Pay. (6) Calculate Social Security Tax which is a fixed percentage (cell C19) of Gross Pay. (7) Calculate Net Pay which is the Gross Pay minus the Withholding and Social Security Taxes. (8) Enter formulas in F13:L13 to calculate the totals. Make sure to make careful use of relative and absolute references and use the Fill-Down (or Fill-Over) feature whenever possible. If you use these features properly you should have to write formulas in only 8 cells (and then be able to Fill-Down or Fill-Across to complete the remaining cells).
Employee Payroll Table
Last Name Number of Dependents Hourly Wage Hours Worked Regular Pay Overtime Pay Gross Pay Taxable Pay Withholding Tax Soc Sec Tax Net Pay
Barber 1 $ 23.00 51 xxx xxx xxx xxx xxx xxx xxx
Grauer 1 22.00 41 xxx xxx xxx xxx xxx xxx xxx
Plant 1 19.00 40 xxx xxx xxx xxx xxx xxx xxx
Pons 0 20.00 46 xxx xxx xxx xxx xxx xxx xxx
Spitzer 2 20.00 45 xxx xxx xxx xxx xxx xxx xxx
Stutz 1 18.00 55 xxx xxx xxx xxx xxx xxx xxx
Yanez 1 20.00 33 xxx xxx xxx xxx xxx xxx xxx
Totals xxx xxx xxx xxx xxx xxx xxx
Assumptions Taxable Pay Tax Rate
Overtime Threshold (hours) 40 $0 15%
Overtime Rate 1.5 $250 20%
Deduction per dependent $ 75.00 $500 24%
Social Security tax rate 7.65% $750 29%
$1,000 33%
Answers
Employee Payroll Table
Last Name Number of Dependents Hourly Wage Hours Worked Regular Pay Overtime Pay Gross Pay Taxable Pay Withholding Tax Soc Sec Tax Net Pay
Barber 1 $ 23.00 51 $ 920.00 $ 379.50 $ 1,299.50 $ 1,224.50 $ 404.09 $ 99.41 $ 796.00
Grauer 1 22.00 41 880.00 33.00 913.00 838.00 243.02 69.84 600.14
Plant 1 19.00 40 760.00 - 0 760.00 685.00 164.40 58.14 537.46
Pons 0 20.00 46 800.00 180.00 980.00 980.00 284.20 74.97 620.83
Spitzer 2 20.00 45 800.00 150.00 950.00 800.00 232.00 72.68 645.33
Stutz 1 18.00 55 720.00 405.00 1,125.00 1,050.00 346.50 86.06 692.44
Yanez 1 20.00 33 660.00 - 0 660.00 585.00 140.40 50.49 469.11
Totals $ 5,540.00 $ 1,147.50 $ 6,687.50 $ 6,162.50 $ 1,814.61 $ 511.59 $ 4,361.30

Part 12

Instructions: Complete the worksheet below as follows: (1) The Fuel Required (in pounds) for each flight depends on the Aircraft Type and the number of Flying Hours. Use a lookup function in the formula to determine the pounds required per hour based on the Aircraft Type then multiply the result by the number of Flying Hours to calculate the amount of Fuel Required for each flight. (2) Reserve Fuel is equal to the Fuel Required times the Percent of flying fuel required for reserves. (3) Holding Fuel is equal to the Fuel Required times the Percent of flying fuel required for holding. (4) Total Fuel is the sum of the Fuel Required, the Reserve Fuel, and the Holding Fuel. (5) The estimated fuel Cost for each flight is the Total Fuel times the Price per Pound. However there is a price break if the Fuel Required reaches or exceeds a Threshold number of pounds for price break. (6) Enter formulas in E12:I14 that calculate the appropriate statistics. Make sure to make careful use of relative and absolute references and use the Fill-Down (or Fill-Over) feature whenever possible. If you use these features properly you should have to write formulas in only 8 cells (and then be able to Fill-Down or Fill-Across to comlete the remaining cells).
Fuel Estimates of Popular Flights
Aircraft Type Trip Flying Hours Fuel Required Reserve Fuel Holding Fuel Total Fuel Cost
A-320 LGA - MIA 3.25 xxx xxx xxx xxx xxx
A-320 LAX - SFO 1.25 xxx xxx xxx xxx xxx
B-737 ATL - MIA 2.00 xxx xxx xxx xxx xxx
ERJ-190 JFK - ORD 2.75 xxx xxx xxx xxx xxx
B-757 LGA - ATL 2.50 xxx xxx xxx xxx xxx
B-737 LAX - LAS 1.25 xxx xxx xxx xxx xxx
CRJ-900 LAX - PHX 1.25 xxx xxx xxx xxx xxx
Totals xxx xxx xxx xxx xxx
Mean xxx xxx xxx xxx xxx
Maximum xxx xxx xxx xxx xxx
Aircraft Type Range (miles) Fuel Consumption (lbs/hr) Fuel Information
Threshold number of pounds for price break 6,000
A-320 3,449 5,732 Price per pound if threshold is reached $ 4.25
B-737 2,174 5,732 Price per pound if threshold is not met $ 4.75
B-757 3,449 7,937 Percent of flying fuel required for reserves 20%
CRJ-900 2,113 3,500 Percent of flying fuel required for holding 10%
ERJ-190 2,610 4,079
Answers
Fuel Estimates of Popular Flights
Aircraft Type Trip Flying Hours Fuel Required Reserve Fuel Holding Fuel Total Fuel Cost
A-320 LGA - MIA 3.25 18,629 3,726 1,863 24,218 $ 102,925.23
A-320 LAX - SFO 1.25 7,165 1,433 717 9,315 39,586.63
B-737 ATL - MIA 2.00 11,464 2,293 1,146 14,903 63,338.60
ERJ-190 JFK - ORD 2.75 11,217 2,243 1,122 14,582 61,975.31
B-757 LGA - ATL 2.50 19,843 3,969 1,984 25,795 109,629.81
B-737 LAX - LAS 1.25 7,165 1,433 717 9,315 39,586.63
CRJ-900 LAX - PHX 1.25 4,375 875 438 5,688 27,015.63
Totals 79,858 15,972 7,986 103,815 $ 444,057.82
Mean 11,408 2,282 1,141 14,831 $ 63,436.83
Maximum 4,375 875 438 5,688 $ 27,015.63