The Colorado River Drainage Basin covers parts of seven western states. A series

Question

The Colorado River Drainage Basin covers parts of seven western states. A series of dams has been constructed on the Colorado River and its tributaries to store runoff water and to generate low-cost hydroelectric power. The ability to regulate the flow of water has made the growth of agriculture and population in these arid desert states possible. Even during periods of extended drought, a steady, reliable source of water and electricity has been available to the basin states. Lake powell is one of these reservoir for 8 years from 2000 to 2007. These data are shown in table 8.11. Use the data in the file to answer the following questions:

a)Determine the average elevation of the water level of each year and for the 8-year period over which the data were collected.

b)Determine how many months each year exceed the overall average for the 8-year period

c)Create a report that lists the month (number) and the year for each of the months that exceed the overall average. For example, June is month 6.

d)Determine the average elevation of the water for each month for the 8-year period.

2000

2001

2002

2003

2004

2005

2006

2007

January

3680.12

3668.05

3654.25

3617.61

3594.38

3563.41

3596.26

3601.41

February

3678.48

3665.02

3651.01

3613

3589.11

3560.35

3591.94

3598.63

March

3677.23

3663.35

3648.63

3608.95

3584.49

3557.42

3589.22

3597.85

April

3676.44

3662.56

3646.79

3605.92

3583.02

3557.52

3589.94

3599.75

May

3676.76

3665.27

3644.88

3606.11

3584.7

3571.60

3598.27

3604.68

June

3682.19

3672.19

3642.98

3615.39

3587.01

3598.06

3609.36

3610.94

July

3682.86

3671.37

3637.53

3613.64

3583.07

3607.73

3608.79

3609.47

August

3681.12

3667.81

3630.83

3607.32

3575.85

3604.96

3604.93

3605.56

September

3678.7

3665.45

3627.1

3604.11

3571.07

3602.20

3602.08

3602.27

October

3676.96

3663.47

3625.59

3602.92

3570.7

3602.31

3606.12

3601.27

November

3674.93

3661.25

3623.98

3601.24

3569.69

3602.65

3607.46

3599.71

December

3671.59

3658.07

3621.65

3598.82

3565.73

3600.14

3604.96

3596.79

  Note: This problem should be solved using the find function, the mean function, and the length function. Programmers with previous experience may be tempted to use a loop structure, which is not required.

2000

2001

2002

2003

2004

2005

2006

2007

January

3680.12

3668.05

3654.25

3617.61

3594.38

3563.41

3596.26

3601.41

February

3678.48

3665.02

3651.01

3613

3589.11

3560.35

3591.94

3598.63

March

3677.23

3663.35

3648.63

3608.95

3584.49

3557.42

3589.22

3597.85

April

3676.44

3662.56

3646.79

3605.92

3583.02

3557.52

3589.94

3599.75

May

3676.76

3665.27

3644.88

3606.11

3584.7

3571.60

3598.27

3604.68

June

3682.19

3672.19

3642.98

3615.39

3587.01

3598.06

3609.36

3610.94

July

3682.86

3671.37

3637.53

3613.64

3583.07

3607.73

3608.79

3609.47

August

3681.12

3667.81

3630.83

3607.32

3575.85

3604.96

3604.93

3605.56

September

3678.7

3665.45

3627.1

3604.11

3571.07

3602.20

3602.08

3602.27

October

3676.96

3663.47

3625.59

3602.92

3570.7

3602.31

3606.12

3601.27

November

3674.93

3661.25

3623.98

3601.24

3569.69

3602.65

3607.46

3599.71

December

3671.59

3658.07

3621.65

3598.82

3565.73

3600.14

3604.96

3596.79

Explanation / Answer

a)

You can use average function here. average(range of cells).

3678.12 3665.32 3637.94 3607.92 3579.90 3585.70 3600.78 3602.36 3619.75

b)

you can use countif(rangeofcells,">=averagevalue")

36

c)

you can use = cell >=averagevalue

2000 2001 2002 2003 2004 2005 2006 2007
January TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
February TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
March TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
April TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
May TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
June TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
July TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
August TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
September TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
October TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
November TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE
December TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE

The following are the months.

01,02,03,04,05,06,07,08,09,10,11,12 for the month 2000

01,02,03,04,05,06,07,08,09,10,11,12 for the month 2001

01,02,03,04,05,06,07,08,09,10,11,12 for the month 2002

d)

You can use the formula = cell >=averagevalue

2000 2001 2002 2003 2004 2005 2006 2007
January 60.37 48.30 34.50 -2.14 -25.37 -56.34 -23.49 -18.34
February 58.73 45.27 31.26 -6.75 -30.64 -59.40 -27.81 -21.12
March 57.48 43.60 28.88 -10.80 -35.26 -62.33 -30.53 -21.90
April 56.69 42.81 27.04 -13.83 -36.73 -62.23 -29.81 -20.00
May 57.01 45.52 25.13 -13.64 -35.05 -48.15 -21.48 -15.07
June 62.44 52.44 23.23 -4.36 -32.74 -21.69 -10.39 -8.81
July 63.11 51.62 17.78 -6.11 -36.68 -12.02 -10.96 -10.28
August 61.37 48.06 11.08 -12.43 -43.90 -14.79 -14.82 -14.19
September 58.95 45.70 7.35 -15.64 -48.68 -17.55 -17.67 -17.48
October 57.21 43.72 5.84 -16.83 -49.05 -17.44 -13.63 -18.48
November 55.18 41.50 4.23 -18.51 -50.06 -17.10 -12.29 -20.04
December 51.84 38.32 1.90 -20.93 -54.02 -19.61 -14.79 -22.96

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.