Method for calculation of lightning risk assessment using MS Excel
Abstract
This work considers the method of building a model for calculating the risk of a lightning strike in a building and its implementation in accordance with current regulatory documents using the MS EXCEL computer program. This technique will be useful for designers who perform a part of lightning protection and during the execution of their projects must calculate the risk of lightning strike. The risk of a lightning strike according to current regulations may consist of the sum of one to four risks: the risk of loss of human life; the risk of losing the ability to provide public services; the risk of loss of cultural heritage and the risk of loss of economic value. This technique is used to calculate the risk of loss of human life as the most important value that can be lost. It provides for the calculation of the risk of lightning strike for public, administrative, industrial or residential buildings, that is, the buildings that the designer most often has to deal with during his work. This technique is not intended to be used to build risk models for buildings or structures of the following purpose: public communications and objects of cultural purpose, because these buildings involve the calculation of the risk of loss of the ability to provide public services or the risk of loss of cultural heritage, respectively. Also, it does not apply to the calculation of buildings with explosive zones, since the risk calculation for them will be specific. In addition, this technique does not provide for calculations of the risk of loss of economic value, since it is not mandatory according to regulatory documents and is carried out additionally at the request of the customer. At the same time, it can be used as a basis for building more complex models suitable for these types of buildings. The work considers the methods of solving the main problems faced by the designer during the calculation of the risk assessment: automation of the calculation of similar formulas, and reduction of most of them to several basic types; obtaining a formula that returns a value or a reference to a value from a table or range, and also performs a search for the specified element in the range and returns the relative position of this element; formulation of the risk assessment procedure in accordance with the regulatory document and determination of how certain input data affect the final result
Keywords
computer simulation; lightning protection; functions in MS EXCEL; formulas in MS EXCEL; designing
References
[1] Araújo, M.A., Flauzino, R.A., Altafim, R.A.C., Batista, O.E., & Moraes, L.A. (2015). Practical methodology for modeling and simulation of a lightning protection system using metal-oxide surge arresters for distribution lines. Electric Power Systems Research, 118, 47-54. https://doi.org/10.1016/j.epsr.2014.07.017.
[2] Bao, J., Wang, X., Zheng, Y., Zhang, F., Huang, X., Sun, P., & Li, Z. (2021). Resilienceoriented transmission line fragility modeling and real-time risk assessment of thunderstorms. EEE Transactions on Power Delivery, 36(4), 2363-2373. https://doi.org/10.1109/tpwrd.2021.3066157. Basic requirements for project and work documentation, SSTU B А.2.4-4:2009.
[3] Elliams, T. (2017). EMC for Product Designers. Elsevier. https://doi.org/10.1016/C2015-005782-3.
[4] Engineering equipment of buildings and facilities. Arrangement of lightning protection of buildings and structures, SSTU B C.2.5-38:2008.
[5] Fernando, J., James, S., Kularatna, N., Ross, A.S. (2019). Design of Transient Protection Systems. Elsevier. https://doi.org/10.1016/C2016-0-00423-0.
[6] Gao, Y., Han, Y., Xiao, F., Chen, C., Zhang, J., Zhang, J., Zhang, Y., & Li, L. (2020). Study on lightning protection scheme of multi-terminal MMC-MVDC distribution system. High Voltage, 5(5), 605-613. https://doi.org/10.1049/hve.2019.0256.
[7] Goh, H.H., Sim, S.Y., Shaari, J., Azali, N.A., Ling, C.W., Chua, Q.S., & Goh, K.C. (2017). A review of lightning protection system–risk assessment and application. Indonesian Journal of Electrical Engineering and Computer Science, 8(1), Article 1. https://doi.org/10.11591/ijeecs.v8.i1.pp221-229.
[8] Horda, I.M., & Flehantov, L.O. (2015). Computer modelling of process of the mechanical motion of body with the help of MS excel means. Information Technologies and Learning Tools, 47(3), Article 3. https://doi.org/10.33407/itlt.v47i3.1245.
[9] Ibrahim, D. (2009). Using the excel spreadsheet in teaching science subjects. Procedia - Social and Behavioral Sciences, 1(1), Article 1. https://doi.org/10.1016/j.sbspro.2009.01.058.
[10] Protection against lightning - Part 1: General principles, SSTU EN 62305-1:2012 (EN 62305-1:2011, IDT).
[11] Protection against lightning - Part 2: Risk management, SSTU EN 62305-2:2012 (IEC 62305-2:2010, IDT).
[12] Protection against lightning - Part 3: Physical damage to structures and life hazard, SSTU EN 62305-3:2021 (EN 62305-3:2011, IDТ; ІЕС 62305-3:2010, MOD).
[13] Protection against lightning - Part 4: Electrical and electronic systems within structures, SSTU EN 62305-4:2012 (EN 62305-4:2011, IDT).
[14] Rules for arranging electrical installations, RAE-2017.
[15] Rules for the construction of electrical installations. Electrical equipment of special installations. (2001). NLALP 40.1-1.32-01 (SRALP 0.00-1.32-01).
[16] Souto, L., Taylor, P.C., & Wilkinson, J. (2023). Probabilistic impact assessment of lightning strikes on power systems incorporating lightning protection design and asset condition. International Journal of Electrical Power & Energy Systems, 148, 108974. https://doi.org/10.1016/j.ijepes.2023.108974.
[17] Surtees, A.J. (2011). A review of IEC 62305–4 protection against lightning part 4: Electrical and electronic systems within structures. 2011 7th Asia-Pacific International Conference on Lightning, 478-481. https://doi.org/10.1109/APL.2011.6110170.
[18] Tack, G., Roselli, R.J., Overholser, K.A., & Harris, T.R. (1995). The use of Microsoft Excel as a user interface for biological simulations. Computers and Biomedical Research, 28(1), Article 1. https://doi.org/10.1006/cbmr.1995.1003.
[19] Uman, M.A. (1994). Natural lightning. IEEE Transactions on Industry Applications, 30(3), Article 3. https://doi.org/10.1109/28.293729.