Computational Exercise 9

Computational Exercise 9

1. Create a new worksheet called “Making charts”. Prepare a scatter chart for the following data:

Put time on the horizontal axis, flowrate on the left vertical axis, and pressure on the right vertical axis.  Format the horizontal axis as logarithmic.  Include axis titles identical to the column headings above.  Format your data series for Q with black-filled circular markers and solid black lines.  Format your data series for P with red-filled square markers with solid red lines.  Format axis numbers as Times New Roman bold 16 pt and axis titles as Times New Roman bold 18 pt font.  Add exponential trend lines, equations, and R² values for Q and for P.  Format the trend line for Q as a black dashed line.  Format the trend line for P as a dashed red line.  Format trend line equations and R² values as Times New Roman bold 12pt font; use black font for Q and red font for P.

2. Create a new worksheet called “Solving equations”. Use Excel and solver to find a positive and negative value of x that makes y = 0 in the following equation:

3. Create a new worksheet called “Interpolation”. Given the following data:

Use linear interpolation to complete the following table (show all your work on the Excel worksheet):

4. Create a new worksheet called “Numerical integration”.Use numerical integration with the trapezoidal rule to compute the total flowrate, Q at a location in the Columbia River. The river is 1200 ft wide and 24 ft deep.  Report your answer in cfs and acre-ft/year.

5. Create a new worksheet called “Working with data”. Go to this website and download the 2005 data for “Corvallis Muni”.

http://rredc.nrel.gov/solar/old_data/nsrdb/1991-2010/hourly/siteonthefly.cgi?id=726945

1. Delete all columns except for these three:

The first column contains the year, month, and day.  The second column contains the hour of day.  The third column contains the solar radiation data (the units for ETR should be read as “Watt hours per meter squared’).

1. Prepare a vertical bar chart that shows the average ETR for each hour of the day for all days in January Your bar chart will have hour of day on the horizontal axis; you will have 24 vertical bars, one for each hour of the day. The height of the first bar (vertical axis) should show the average ETR for hour 0 for all days in January, etc.).  Format your chart in the usual way.
2. Prepare a vertical bar chart that shows the average ETR for each hour of the day for all days in July.Your bar chart will have hour of day on the horizontal axis; you will have 24 vertical bars, one for each hour of the day. The height of the first bar (vertical axis) should show the average ETR for hour 0 for all days in July, etc.).  Format your chart in the usual way.
3. Find the ten days in the entire record with the ten largest ETR values at 12:00 (noon).

Rubric:

Problem 1 (4 points): 3 points will be awarded for plotting the graph as guided. 1 point will be awarded for correct formatting. -1.5 point will be awarded if graph is incomplete. Zero grade will be awarded if not attempted.

Problem 2 (4 points): Use solver to summarize the exact values of ‘x’ for which ‘y=0’. Cells that are used to run solver must contain relevant formulae. Full credit will be given for correct output. -2 points will be awarded for incomplete work. Zero grade will be awarded if not attempted.

Problem 3 (4 points): Build the linear interpolation calculator. Full credit will be given for correct values of temperature. -2 points will be awarded for incomplete work. Zero grade will be awarded if not attempted.

Problem 4 (4 points): Compute the total flow rate as guided. Full credit will be given for correct value of flow rate. -2 points will be awarded for incomplete work. Zero grade will be awarded if not attempted.

Problem 5 (4 points): 1.5 point will be awarded for the right plotting of each vertical bar chart. 1 point will be awarded for summarizing the correct data set of 10 days with largest ETR values.