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 sitespecific 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:
 Open the following USGS webpage in a browser:
https://waterdata.usgs.gov/nwis/uv?site_no=14144700
 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:
 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.
 Select and copy the data (rows that do not being with “#”) from the page, and paste into a column in an Excel spreadsheet.
 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 “Texttocolumns” feature. Use the following resources to learn about the function:
a.Office Online Help: https://support.office.microsoft.com/enus/article/Splittextintodifferentcells30b14928555041f597ca
7a3e9c363ed7?CorrelationId=5d7ee3f0521148529ccf21a40d5f51f1&ui=en
US&rs=enUS&ad=US
Once you have the data in Excel, use the tools we have covered in class to answer the following:
 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).
 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).
 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
Load Direction  Load Abbreviation  Description 
Vertical  DL  Dead Load: selfweight of bridge 
LL  Live Load: forces applied by moving components (e.g.
trucks, pedestrians, horses) 

DW  Wearing Surface/Utilities: weight of potential future dead load
(e.g., utility conduits, wearing surfaces) 

Horizontal  WS  Wind Load: force of wind on structure 
EQ  Seismic Load  
BR  Vehicle Braking Force 
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^{2}. For LL (in units of kg/m^{2}), 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^{2}) 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 sophomorelevel 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^{2}.
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 nonsuspended 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’ crosssection 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 Ishaped beam section:
Figure 8: Ibeam Crosssection
The crosssection is comprised of 3 rectangles, so the calculations for area, etc. are straightforward. Create a spreadsheet with the following inputs:
Table 2: Inputs
Input  Units  Default
Value 
Description 
in  9.75  Width of top flange (TF)  
in  0.75  Thickness of top flange  
in  0.375  Width of web (W)  
in  10.0  Depth of web  
in  6.0  Width of bottom flange (BF)  
in  0.50  Thickness of bottom flange 
Provide the following outputs in table format in you memorandum:
Table 3: Outputs
Output  Units  Description 
in  Overall crosssection height  
in^{2}  Overall crosssection area  
̿  in  Height of crosssection centroid (see formula below) 
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 crosssection to the center of the given rectangle.
You may find it helpful to provide an intermediate table to assist in your calculations:
Rectangle  ∙  
1 (Top Flange)  
2 (Web)  
3 (Bottom Flange) 
Σ
Total  /10 