• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Lee L. Lowery, Jr., PhD, P.E.
  • Research
  • People
  • Contact

Lee L. Lowery, Jr.

Just another CoE WordPress site

Texas A&M University College of Engineering

Web Problem 4.3 – Determination of Optimum Pump Location Using Excel

Posted on July 27, 2021 by Abigail Stason

The figure below shows the location on a roof where high, medium, and low pressure polyethylene chlorothene lines are routed from a pump to drop points through the roof, for use in the building. Each drop point is fixed at the coordinate given in the table below. The pump itself can be located anywhere on the roof. The lines leading to drop points 1, 3, and 5 are high pressure lines, and cost $13/foot. Medium pressure lines are required at drop points 2 and 4, and cost $8/foot. Drop point 6 requires only a low pressure line costing $2/foot.

High pressure lines come in 20 foot lengths with $80 joints, medium pressure lines come in 40 foot lengths with $50 joints, and low pressure lines come in 50 foot lengths with joints which cost $40 each. Each pipe requires a joint on each end, as well as those needed to connect the individual lengths of pipe from the pump to the drop. Thus a 60 foot long high pressure line will require 4 joints, and a 63 foot long high pressure line will require 5 joints. The pipe vender will sell you any length you need. So if you need 63.6 feet, he will sell you that exact length.

Determine the optimum location for the pump, which will minimize the cost of the lines and joints leading from the pump to the 6 drop points.

Solve this problem using Excel. Set the solution up in a general fashion – i.e. use a table of pipe/joint properties (cost/foot, cost/joint, lengths available, etc.) such that entry changes in the table will be reflected in the final solution for cost. No formal engineering report is required. Also, since you handed in a hand solution to this problem earlier, no hand solution is required with this solution. Submit only a print out of your final answer.

Hints:

  • You may need the truncate function =trunc(cell location)
  • If you look under Excel’s “Tools” you will find a “Solver” which will let you optimize functions.
  • See note below.

Drop
Point

Y Coordinate
(Feet)

X Coordinate
(Feet)

Line
Pressure

Pipe
Cost/Foot
$/Foot

Manufactured
Pipe Lengths
(Feet)

Cost of
Connecting Joints
$/Joint

1

0

0

High

$13

20 $80

2

800

0

Medium

$8

40 $50

3

0

300

High

$13

20 $80

4

400

350

Medium

$8

40 $50

5

800

550

High

$13

20 $80

6

150

650

Low

$2

50 $40

Note to students:  Same note as in Problem 4.2.  Also, if you set this problem up in a general form, this change in parameters will be very simple.  If you hard-coded everything into the equations, it will not be as simple.

Filed Under: Uncategorized

Pages

  • Contact
  • Lee L. Lowery, Jr., PhD, P.E.
  • Office Hours

© 2016–2023 Lee L. Lowery, Jr. Log in

Texas A&M Engineering Experiment Station Logo
  • College of Engineering
  • Facebook
  • Twitter
  • State of Texas
  • Open Records
  • Risk, Fraud & Misconduct Hotline
  • Statewide Search
  • Site Links & Policies
  • Accommodations
  • Environmental Health, Safety & Security
  • Employment