Excel & Word Assignment | Homework Help Websites

Problem 1 (3 points)

The United States Geological Survey (USGS) is a government agency that provides terabytes of data regarding geography, soil, and water resources for locations across the United States. As civil and environmental engineers, this data can be useful for determining site-specific characteristics.

One type of USGS data that is commonly used is from gauges placed along many major river systems.  These gauges usually record river height, and sometimes report flowrates.

For this problem, you will download a month of river gauge data from the Portland, OR station on the Columbia River at Vancouver, WA. To download the data, follow these instructions:

1. Open the following USGS webpage in a browser:

https://waterdata.usgs.gov/nwis/uv?site_no=14144700

2. Download all the available data for the period from 11/01/2018 to 11/30/2018. The appropriate options to select are shown below in Figure 1:
3. The resulting webpage will include all of the data in a “tab separated” format. Read through the data description at the top of the page – it will describe which columns include the discharge, velocity, and gage height data.
4. Select and copy the data (rows that do not being with “#”) from the page, and paste into a column in an Excel spreadsheet.
5. The data will all show up in a single column, i.e., Excel will not parse the numbers into their own columns. To get the data split up into columns, you need to use the “Text-to-columns” feature. Use the following resources to learn about the function:

a.Office Online Help: https://support.office.microsoft.com/en-us/article/Split-textintodifferent-cells-30b14928-5550-41f5-97ca-

7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-

US&rs=en-US&ad=US

Once you have the data in Excel, use the tools we have covered in class to answer the following:

1. Does there appear to be a correlation between discharge and gage height? Explain. Provide a single graph with both data to support your conclusion (you do not need to use data over the entire time period to support your conclusion if a shorter time period would make it clear).
2. Does there appear to be a correlation between discharge and velocity? Explain. Provide a single graph with both data to support your conclusion (you do not need to use data over the entire time period to support your conclusion if a shorter time period would make it clear).
3. Use a pivot table to answer the following question: What is the average daily fluctuation in water level at the Columbia River at Vancouver between November 1 and 30 in 2018?

In your memorandum, provide your answers with the accompanying graphs (where applicable) to the questions above.

Problem 2 (2 points)

The Bridges to Prosperity (B2P) Design Manual vol.3, p. 6 covers the design loads that must be applied to the bridges. You can download a copy of this design manual from the following website: www.jotformpro.com/B2P/Manuals_English or you can download one from D2L under “Reference Materials”.

The most common types of loads that a bridge experiences are shown in the table below.

Table 1: Common Design Loads for Bridges

For your calculations, you will only need to consider the vertical loads DL and LL.

For DL, the B2P Design Manual provides that a reasonable assumption is 100 kg per meter of bridge length:

Figure 2: B2P Design Manual Dead Load Instructions (vol. 3, p. 6)

But this is just 100 kg/m². For LL (in units of kg/m²), the magnitude of the distributed force is dependent on the length of the span:

Figure 3: B2P Design Manual LL Calculations (vol. 3, p. 6)

Note the minimum and maximum magnitudes of LL specified in the manual. (Also note that the standard width of a B2P bridge deck is 1 meter, so the LL calculated above will become in units of /.)

In you memorandum, provide a table and graph that shows the DL, LL, and total distributed load          (                                                                            in            units      of            kg/m²) for span lengths from 10m to 60m, in 5m increments.

Problem 3 (2 points)

Many of the B2P design calculations are dependent on the force carried by the cables. This intuitively makes sense because the cables are the main mechanism for carrying the load from the decking to the foundations.

The first calculation of cable tension that the B2P Design Manual has you complete is the horizontal component of the tension in the main span, _ℎ (sometimes also shown as _ℎ). A diagram showing the horizontal component of tension is provided in Figure 4, below.

Figure 4: Horizontal Cable Tension (B2P vol. 3, p. 7)

The theory required to solve for the horizontal tension is covered in the sophomore-level course Statics (EAS212). However, understanding the theory is not necessary to apply the relatively straightforward formula provided in the B2P Design Manual:

Figure 5: Horizontal Tension Calculation (B2P vol. 3, p. 7)

Refer to the B2P Design Manual for determining the inputs to the equation. Note that you already calculated  for different span lengths in Problem 2 in units of kg/m².

Take the spreadsheet that you developed for Problem 2 and add the ability to calculate the horizontal tension for each of the given span lengths.

In your memorandum, provide [only] a graph of horizontal tension vs. span length for this problem.

Problem 4 (3 points)

Depending on the project site, a non-suspended bridge is a more effective design choice. Most of these bridges will either be a girder or truss structure. The difference between the two types of bridges is illustrated below in Figure 6: Truss Bridge and Figure 7: Girder Bridge.

Figure 6: Truss Bridge

Figure 7: Girder Bridge

Regardless of the type of bridge, certain properties of the steel components’ cross-section need to be calculated. These include: area, centroid location, and moment of inertia (a stiffness property).

For this problem, you will calculate the area and centroid location for a common I-shaped beam section:

Figure 8: I-beam Cross-section

The cross-section is comprised of 3 rectangles, so the calculations for area, etc. are straightforward. Create a spreadsheet with the following inputs:

Table 2: Inputs

Provide the following outputs in table format in you memorandum:

       Table 3: Outputs

The formula for calculating the centroid ̅ is as follows:

∑                                                                               1                    2                                     3

Where  is the area of the given rectangle, and  is the distance from the bottom of the cross-section to the center of the given rectangle.

You may find it helpful to provide an intermediate table to assist in your calculations:

Σ