Project 2_Formulas, Solver, and Problem Solving (2)
docx
School
Wayne State University *
*We aren’t endorsed by this school
Course
125
Subject
English
Date
Feb 20, 2024
Type
docx
Pages
5
Uploaded by JusticeSquirrelMaster981
ENGR-125
Project 2: Excel Formulas, Solver and Problem Solving
Due Date: February 4, 2024
Part A. Excel Formulas and Charts: Please use the data in the attached Spreadsheet
“Traffic_Air Pollution Data_Project2.xlsx”
Problem 1. Fill out the following Table:
Car Traffic
Truck Traffic
Wind Speed
NOx
PM2.5
Average
Standard Deviation
Minimum
First Quartile
Median
Third Quartile
Maximum
Problem 2. Calculate the 0, 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85, 90,
95, and 100 percentiles for Car and Truck traffic. Paste the table in this document.
Problem 3. Filter your data for 8 am and the season of Winter. Then copy the car traffic and
NOx to a new sheet. Create a scatter plot of NO
x
(in y-axis) versus car (in x-axis). Add a
trendline to your chart. Which trendline has the highest R
2
value. Also, add the equation and R
2
-
value to your chart. Paste your chart in this document. Estimate the value of NO
x
for a Car traffic
of 800 counts per hour.
Problem 4. Use the histogram in “Data Analysis” ToolPak and create a bar chart for the
histograms of Car traffic, and NO
x
.
Problem 5. Use the Vlookup function to perform the following tasks:
Find the count of trucks when number of cars was 122 counts.
Find the value of NO
x
when number of trucks were maximum.
Part B. Excel Solver and Engineering Problems
Problem 1. Find the roots of the following equations
X
3
cosx
+
x
2
+
5
=
0
a)
To find the root, we can plot the equation in Excel and visually find the roots.
b)
Use Goal Seek to find the roots of these equations.
Problem 2. Formulate and solve an optimization
problem
A tank is to be constructed that will hold 500 m
3
when filled. The shape is to be cylindrical, with a
hemispherical top. Costs to construct the cylindrical portion
will be $300/m
2
, while costs for the hemispherical portion
are slightly higher at $400/m
2
. Calculate the tank dimensions that will result in the lowest
dollar cost. Volume Of cylinder is= π R
2
H
Volume of hemisphere is=
2
3
π R
3
Surface area of cylinder is=
2
πRH
Surface area of hemisphere is=
2
π R
2
a) Express total volume as a function of H
(height) and R
(radius). b) Find H
(height) in terms of R
(radius) using the equation in part (a). Remember, the total
volume is 500 m
3
.
c) Express the cost of construction in terms of H
and R
. Cost =300*(Area of cylinder) +400*(Area of hemisphere)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
d) Create a spreadsheet in Excel to calculate H
(height) and Cost for the following values of R
.
Find the value of R
for the lowest cost. Plot a chart in Excel and visually observe where the
minimum cost is.
R (m)
1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0
H(m)
Cost ($)
Problem 3.
Use the Excel Solver to Find the value of “R” for minimum cost in Problem 2.
Problem 4. Solve a system of linear equations
Example of 4 unknowns and 4 equations:
x + 2y - 3z + 4w = 12
2x + 2y - 2z + 3w = 10
0 + y + z + 0 = -1
x - y + z - 2w = -4
Use Excel Solver to find the values for x, y, z and w.
Problem 5. Transportation Problem
. Remember, these days packages from Amazon are coming from all over the US. Have you ever wondered why is this thing coming from this city? A company has two warehouses in Detroit and Dearborn which supply products to cities with demand: Chicago, Toronto, and Buffalo. The following table show the shipping costs between origins and destination cities. Destination
Origin
Chicago
Toronto
Buffalo
Detroit
$9
$25
$12
Dearborn
$7
$20
$13
Here is a breakdown of supply and demand.
Supplies
s1 Dearborn 400
s2 Detroit 600
Demands
d1 Chicago 200
d2 Toronto 300
d3 Buffalo 500
Use the Excel Solver to find the optimal flow of goods between origin and destination cities to
have a minimum shipping cost.