Excel Homework # 2
This homework counts for 25% of the homework grade.
Print out the solution to each problem below using Excel.
1. The number 76.9299872 is in a cell but it has been reformatted so that it shows as $76.93.  What number is used by Excel to make calculations?
2. What is the best way to get to cell AV345?
3. What is the best way to enter 7.897x10-11?
4. The data range has been adjusted such that rows d2, d7 and d8 have been deleted. The original summation formula looked like this:  =SUM(d1:d12) .  Has the formula changed, if yes, write out the new formula.
5. The data range has been adjusted such that rows d2, d7 and d8 have been erased. The original summation formula looked like this: =SUM(d1:d12) .   Has the formula changed, if yes, write out the new formula.
6. I want to enter 555 minus 3434. How should I enter this into a cell? Write down the formula and displayed value.
7. I want to enter the phone number 555-3434. How should I enter this into a cell? Write down the formula and displayed value.
8. Program the following mathematical expressions in Excel using the Math & Trig Functions.  After each expression below, write down the formulas used and the resulting values:
inverse cosine (in degrees) of 0.876:
tangent of 90 degrees:
square root of 45.6:
Ln 100:
exp -5.5:
23.5671.567:
9. Write a formula that will add up the first 10 rows of column A , divide the results by PI and multiply the results by the average of the first 6 rows of column HH.
10. Using the Statistical Functions, find the mean and standard deviation of the following BOD's (in mg/l): 223, 212, 214, 198, 199, 205.  Show all formulas and resulting values.  If you use a range for the values, then indicate the range used.
11. Write an Excel equation in the cell below that will display 100 if cell C3 is greater than zero and display 50 for any other value in C3.
Cell F5:
 
12. The syntax for the CHOOSE function is shown below.  Write a formula in cell B4 that checks the value in cell E2 and displays:  ABC if E2 is 1, DEF if E2 is 2 and GHI if E2 is 3.

CHOOSE(index_number,value1,value2,value3,...)
Cell B4:
 
13. The syntax for the Payment function is shown below.  Write a formula in cell E6 that calculates the payment for the annual interest rate shown in cell D1, for the number of monthly payments shown in cell D2, and for the principle amount shown in cell D3.

PMT(rate,number_periods,principle)
Cell E6:
 
14. The formula for the flow rate of a fluid in a circular conduit flowing full is shown below.  Write an Excel equation in the cell below that will calculate the flow rate (Q) of a pipeline in gpm (gallons/minute) for the velocity (V) in fps (feet/second) in cell B2 and the diameter (d) in inches in cell B3.
Qgpm = 352.5 * Vfps * dft2
Cell D5:
  
15. Program the following problem in Excel. Fill in the formula and value below.
Given: Basin V =2'x2'x3', P=150ft.lb/s, µ=2.73E-5 lbs/ft2
Find: G, G = (P/µV)0.5  Answer: G = 676 s-1 if you do it correctly.
For the problems below, perform the work in Excel,
print out the results and attach to this homework.
16. Create the following table in Excel and turn in a printout of your formatted table.
Given: Table Below.  Find: Compute the total weight. Answer: 331,270 lbs.
Item
Number of
Loads
(#)
Average
Volume
(yd3)
Specific
Weight
(lb/yd3)
Total Weight
col 2x3x4
(lbs)
Compactor trucks
10
16
500
 
Pickup trucks
18
3
100
 
Private cars
56
1
220
 
Broken concrete
2
45
2,595
 
Total (lb/day)
 
 
 
 
17. Create the following table in Excel and turn in a printout of your formatted table.
Given: A town consists of 10,000 which is broken down as 12% low rise apartment, 48% older homes, 33% typical homes and the remainder trailer park.
Find: Estimate the ADF based on residential components. Answer: 553,000 gal/day.
Component
Percent
of Flow
(%)
No. of People
% x 10,000
(pop)
Flow Rate
T2-9, p.27
(gpd/unit)
Total Flow
col3 x col4
(gpd)
Low rise apt.
12
1200
65
 
Older homes
48
4800
45
 
Typical homes
33
3300
70
 
Trailers
 
700
40
 
Total
100
10,000
 
 
18. Graph the following, use a pie chart.
Options
Students
Environmental
167
General
111
Survey
5
19. Graph the following using a semi-log scatter graph.
Year
Population
1960
2,134
1970
4,231
1980
8,329
1999
16,543
20. Using any data you wish, create 5 charts that have not been used in problems above:
Excel Chart Wizard
21. Make a list of at least 10 of your music CD's (or whatever). Include Album Title in one column and Artist Name in the next column.  Sort it by Album Title.  Format the table using lines and colors. Then copy the table and sort the list by Artist Name. Print out both versions.