Reference no: EM132705299
Problem 1 (VBA sub - targetCounter)
Add the screenshot of your VBA code to your final Excel file.
Create a VBA sub that gets a range and a single number from a worksheet as arguments and returns the number of instances that the number is found in the range. The range is a two dimensional range that you pre-specify in your VBA sub.
Declare all your variables.
Example can be seen below: In this example if you check the range (the matrix and count the number of time that number 4 is repeated, you can see 5 times). Check your sub for a few different number.
Please make sure that you are using the half-written code. You need to fill out the places that has Hint: the written comments within the code explain what you need to do.
Problem 2
Use the Excel sheet provided
The deflection of a uniform beam subjected to a triangular load (as shown in the Figure below) is given by the following equation:
y(x) = wo/120EIL( -x5 + 2L2 x3 - L4 x)
where E is Young's modulus (kN/cm2), I is the area moment of inertia (cm4) of the beam cross section, L is the length of the beam (cm), w0 is the total load on the beam, x is the distance from the left support, and y(x) is the deflection at any point x along the beam.
a) Plot the deflection along the length of the beam. Create a plot naming all the axis.
b) Determine the maximum deflection of the beam and the position x (a.k.a the point where dy/dx =0) where this deflection occurs as a problem of root finding utilizing Newton-'s method. For your first iteration choose a reasonable starting value based on your plot from a. The parameter values are
Problem 3
Use the Excel sheet provided
Add the screenshot of your Solver window to your final Excel file.
The flow Q (m3/sec) in an open channel can be predicted with the Manning equation
Q = 1/n Ac R2/3 S1/2
where n = Manning roughness coefficient (a dimensionless number used to parameterize the channel friction), Ac = cross-sectional area of the channel (m2), S = channel slope (dimensionless, meters drop per meter length), and R = hydraulic radius (m), which is related to more fundamental parameters by
R = Ac/P
where P = wetted perimeter (m). As the name implies, the wetted perimeter is the length of the channel sides and bottom that is under water. For example, for a rectangular channel, it is defined as
Suppose that you are using this formula to design a lined canal (note that farmers line canals to minimize leakage losses). Given the parameters n = 0.035, S =0.003, and Q =1 m3/sec, determine the values of B and H that minimize the wetted perimeter. Note that such a calculation would minimize cost if lining costs were much larger than excavation costs.
Problem 4 - Optimization
Use the Excel sheet provided
Add the screenshot of your Solver window to your final Excel file.
Design a water pipeline to bring water from a spring to a small village. Use a minimal cost design. The table below details the diameters, associated installed costs per meter, and availabilities. The village is 3000 m away from the spring. The available head is 15 m, the design flow is 10 liters per second, and the Hazen Williams roughness coefficient is 120. The decision variables are the lengths of each diameter to use. Non-integer lengths, 123.45 m, are acceptable. Note that not all diameters are expected in the final solution, and several of these lengths may be 0. Note also that there is a limitation on the length of each diameter that is available for the project.
|
Diameter (in)
|
Cost ($/installed m)
|
Length available (m)
|
|
2
|
$1.50
|
1500
|
|
4
|
$5.00
|
1000
|
|
6
|
$7.50
|
1000
|
|
8
|
$10.00
|
1000
|
|
12
|
$25.00
|
1000
|
|
24
|
$50.00
|
1000
|
Minimal cost pipelines such as this use the larger diameter near the source, and decrease the diameter toward the village. Besides the length, the other major constraint is the total headloss. The headloss for each length of pipe of a given diameter is
hL =c(Q/HW)1.85 D-4.87 L
where hi, is the headloss in meters c is a factor based on the units of the problem, 1730 for the units described below Q is the flow in liters per second
HW is the Hazen Williams roughness coefficient
D is the pipe diameter in inches
L is the length of the pipe in meters
The total headloss is just the sum of all the individual headlosses:
hLTotal = ∑i=1n hLi
where n is the number of diameters used.
Given this formulation, the problem can be set up and solved as a linear program. Create such a program in Excel with Solver.
a) What are the lengths and total project cost for the optimal design?
b) Which constraints are binding? What does that mean?
Attachment:- Screen Shots.rar