Economics and Statistical Evaluations of Using Microsoft Excel Solver in Pipe Network Analysis
This article has a reply.
VIEW THE REPLYPublication: Journal of Pipeline Systems Engineering and Practice
Volume 7, Issue 4
Abstract
As a follow up to previous studies on numerical analysis of engineering systems, this paper utilizes Microsoft Excel Solver in solving pipe network analysis problems. Continuity and head loss equations were developed from three different pipe networks. Flow and head loss in the pipes were determined using Microsoft Excel Solver and Hardy Cross (standard) methods. These flows and head loss in the pipse were evaluated economically using operational cost and statistically [coefficient of determination (CD), total error, model of selection criterion (MSC), average error, and correlation coefficient (R)]. The study revealed that flow in pipes is a function of the method, withdrawal at the node, and length and diameter of the pipe. Statistical evaluation revealed that CD, total error, MSC, root error, and R were 0.041; 4.55; 0.041; 2.13; and 0.2025; and 1.0000; ; 29.647; ; and 1.000 for Microsoft Excel Solver and Hardy Cross methods, respectively. Economic evaluations showed that cost of head loss per hour and cost per person per day for these systems (for the two communities) were (1) US$999.44 and US$47.49; and (2) US$1219.85 and US$59.04 for Microsoft Excel Solver and Hardy Cross methods, respectively. It was concluded that the Microsoft Excel Solver method is a better choice than Hardy Cross method in pipe network analysis based on higher values of correlation coefficient, CD, and MSC coupled with lower values of total error and root error as well as higher economic value (lower cost of head loss per hour and lower cost of head loss per person per day).
Get full access to this article
View all available purchase options and get full access to this article.
References
Adeniran, A. E., and Oyelowo, M. A. (2013). “An EPANET analysis of water distribution network of the University of Lagos, Nigeria.” J. Eng. Res., 18(2), 1–16.
Babatola, J. O., Oguntuase, A. M., Oke, I. A., and Ogedengbe, M. O. (2008). “An evaluation of frictional factors in pipe network analysis using statistical methods.” Environ. Eng. Sci., 25(4), 539–548.
Chaudhry, H. M. (1979). Applied hydraulic transients, Van Nostrand Reinhold, New York.
Chin, D. A. (2000). Water resources engineering, 1st Ed., Prentice Hall, Englewood Cliffs, NJ.
Dake, N. B. (1983). Essentials of engineering hydraulics, Macmillan, Hong Kong.
EPANET [Computer software]. U.S. EPA, Washington, DC.
de Neufville, M., and James, P. (1969). “Discussion on water distribution systems.” J. Hydraul. Div., 95(1), 484.
Dillingham, J. H. (1967). “Computer analysis of water distribution systems. Part 2.” Water Sewage Works, 114, 43.
Featherstone, R. E., and Nalluri, C. (1982). “Civil engineering hydraulics.” Essential theory with worked examples, ELBS, 1st Ed., Granada Publishing, Billing and Sons, Worcester, U.K.
Guerrero-García, P., and Santos-Palomo, Á. (2008). “Squeezing the most out of the Casio fx-570 ms for electrical/electronics engineers.” Proc., Int. Conf. on Computational and Mathematical Methods in Science.
Hyuk, J. K., and Lee, C. E. (2008). “Reliability analysis of pipe network regarding transient flow.” J. Civ. Eng., 12(6), 409–416.
Kek, S. L., and Tay, K. G. (2008). “Solver for system of linear equations.” Prosiding Seminar Kebangsaan Aplikasi Sains dan Matematik 2008 (SKASM 2008), Penerbit UTHM, Batu Pahat, Malaysia, 605–615.
Krasnov, M., Kiselev, A., Makarenko, G., and Shikin, E. (1990). Mathematical analysis for engineers, Vol. 1, 1st Ed., Mir Publishers, Moscow.
Mailhot, A., Pelletier, G., Noel, J.-F., and Villeneuve, J.-P. (2000). “Modeling the evolution of the structural state of water pipe networks with brief recorded pipe break histories: Methodology and application.” Water Resour. Res., 36(10), 3053–3062.
Ogedengbe, O. (1983). “A computer based analysis of water distribution network.” Nigerian J. Sci., 13(1–2), 187.
Ojo, O. E., Oladepo, K. T., Adewumi, I. K., Oke, I. A., and Ogedengbe, M. O. (2006). “Characterisation of water supply in Osun State of Nigeria.” Ife J. Technol., 15(1), 27–37.
Oke, I. A. (2007). “Reliability and statistical assessment of methods for pipe network analysis.” Environ. Eng. Sci., 24(10), 1481–1490.
Oke, I. A. (2010). “Engineering analysis and failure prevention of a water treatment plant in Nigeria.” J. Fail. Anal. Prev., 10(2), 105–119.
Oke, I. A., et al. (2015a). “Statistical evaluation of mathematical methods in solving linear theory problems: Design of water distribution systems.” Ife J. Sci., 17(2), 255–267.
Oke, I. A., Ojo, S. O., Adeosun, O. O., and Olatunji, S. A. (2015b). “Enhance solutions in pipe network analysis through utilization of Microsoft Excel Solver.” J. Environ. Des. Manage., 7(1), 16–29.
Prasad, T. D., Hong, S. H., and Park, N. (2003). “Reliability based design of water distribution networks using multi-objective genetic algorithms.” KSCE J. Civ. Eng., 7(3), 351–361.
Steel, E. W., and McGhee, J. T. (1979). Water supply and sewerage, 1st Ed., McGraw Hill, Tokyo.
Swamee, P. K., and Sharma, A. K. (1990). “Decomposition of large water distribution system.” J. Environ. Eng., 269–283 .
Swamee, P. K., and Sharma, A. K. (2008). Design of water supply pipe networks, Wiley, NJ.
Tay, K. G. (2006). “How to use calculator Casio FX-570MS in numerical methods.” Penerbit KUiTTHO, Batu Pahat, Malaysia.
Tay, K. G., and Kek, S. L. (2008). “Approximating the dominant eigenvalue using power method through spreedsheet Excel. Prosiding Seminar Kebangsaan Aplikasi Sains dan Matematik 2008 (SKASM 2008).” Penerbit UTHM, Batu Pahat, Malaysia, 599–604.
Tay, K. G., Kek, S. L., and Rosmila, A. K. (2014). “Solving non-linear systems by Newton’s method using spreadsheet Excel.” 〈http://recsam.edu.my/cosmed/cosmed09/AbstractsFullPapers2009/Abstract/Mathematics%20Parallel%20PDF/Full%20Paper/M33.pdf〉 (Dec. 21, 2014).
Ubani, N. O., Dara, E. J., Ugochukwu, C. O., Nwankwojike, B. N., and Iheakaghchi, C. (2013). “Water pipeline network analysis using simultaneous loop flow correction method.” West Afr. J. Ind. Acad. Res., 6(1), 1–19.
Viessman, W., and Hammer, M. (1993). Water supply and pollution control, Harper Collins College, New York.
Watson, T. G., Christian, C. D., Mason, A. J., Smith, M. H., and Meyer, R. (2004). “Bayesian-based pipe failure model.” J. Hydroinf., 6(4), 259–264.
WHO and UNICEF. (2012). “Update progress on sanitation and drinking-water—2012 update (JMP 2012).” 〈http://www.unicef.org/media/files/JMPreport2012.pdf〉 (Oct. 28, 2014).
WHO and UNICEF. (2013). “Update Progress on sanitation and drinking-water—2013 update (JMP 2013).” 〈http://www.unicef.org/wash/files/JMP2013final_en.pdf〉 (Oct. 28, 2014).
Wood, D. J., and Charles, C. O. A. (1972). “Hydraulic network analysis using linear theory.” J. Hydraul. Div., 98(7), 13–15.
Information & Authors
Information
Published In
Copyright
© 2016 American Society of Civil Engineers.
History
Received: Jan 14, 2015
Accepted: Feb 3, 2016
Published online: May 17, 2016
Discussion open until: Oct 17, 2016
Published in print: Nov 1, 2016
Authors
Metrics & Citations
Metrics
Citations
Download citation
If you have the appropriate software installed, you can download article citation data to the citation manager of your choice. Simply select your manager software from the list below and click Download.