Design Calculations of Lightning Protection Systems – Part Thirteen

In Article  " Design Calculations of Lightning Protection Systems – Part Two ", I indicated the lightning protection design process involves a number of design steps as in Fig.1.

Fig.1: The Lightning Protection Design Process

Step#1: Characteristics of the Structure to Be Protected Explained in above Article

Step#2: Risk Assessment Study

Also, In above Article, I indicated that:

Methods Of Calculations For Risk Assessment Study The risk assessment study can be done by (4) different methods as follows: 1- Manual Method (equations and tables method),which will be explained as per: IEC 62305-2, NFPA780. 2-Software Method, 3- Excel Sheets Method, 4-Online Calculators Method.

First: Manual Method (Equations And Tables Method) as per IEC 62305-2

The Manual Method (Equations and Tables Method) for Calculations of Risk Assessment Study as per IEC 62305-2 can be reviewed in the following Articles:

• Design Calculations of Lightning Protection Systems – Part Two

• Design Calculations of Lightning Protection Systems – Part Three

• Design Calculations of Lightning Protection Systems – Part Four

• Design Calculations of Lightning Protection Systems – Part Five

• Design Calculations of Lightning Protection Systems – Part Six

First: Manual Method (Equations And Tables Method) as per NFPA 780

The Manual Method (Equations and Tables Method) for Calculations of Risk Assessment Study as per IEC 62305-2 can be reviewed in the following Articles:

• Design Calculations of Lightning Protection Systems – Part Seven

• Design Calculations of Lightning Protection Systems – Part Eight

• Design Calculations of Lightning Protection Systems – Part Nine

Second: Software Method For Performing The Risk Assessment Study

S/N	Program	By
1	StrikeRisk V5.0	Furse Company
2	RAPAL	Faculty of Engineering- Aristotle University of Thessaloniki
3	Riesgo	Applicationes Tecnologicas Company

I explained the first software: StrikeRisk V5.0 in Article " Design Calculations of Lightning Protection Systems – Part Ten ". 

And I explained the Second software: RAPAL Software in Article " Design Calculations of Lightning Protection Systems – Part Eleven ".

And I explained the third software: Riesgo Software in Article " Design Calculations of Lightning Protection Systems – Part Twelve ".


Today, I will explain the Third method for performing the Risk Assessment Study: Excel Sheets Method.

For more information, please review the following Articles:

• Design Process for Lightning Protection Systems

• Design Calculations of Lightning Protection Systems – Part One

Third: Excel Sheets Method For Performing The Risk Assessment Study

Today, I will introduce two excel sheets for Need for Lightning Protection calculations as follows:

1. IEC62305-2 Need for Lightning Protection,
2. NFPA 780 Simplified Method.

1- IEC62305-2 Need for Lightning Protection Excel Sheet

Today, I will introduce to you, the most powerful excel spreadsheet for Need for Lightning Protection calculations.

Benefits For Using This Excel Spreadsheet List general data for designer and project, Calculate the collection areas for basic and adjacent structures, power lines (if any and up to two lines) and telecommunication line (if any), Calculate the collection areas for different shapes (Rectangular, cylindrical,…) basic and adjacent structures, List all input data (in green cells) that must be known to determine the need for lightning protection, Provide all tables of IEC 62305-2 as dropdown menus for automatic determination of parameter/factors values for the case under design, Provide two maps for assistance in determination of lightning ground flash density (Ng) and isokeraunic number (thunder storm days/year). Provide comments as a help guide which explains the meaning of the used parameters, factors and terms in this excel sheet. Provide the primary risk values automatically in the final results report and make a comparison with the tolerable risk values and assign the need for lightning protection based on this comparison for each type of loss. It saves time and effort lost in doing the calculations by the Manual Method (Equations and Tables Method) As we see in the previous Articles.

This excels spreadsheet includes (6) nos. worksheets and (21) nos. pages as follows:

S/N	Worksheet Name	No. of Pages in Worksheet
1	Cover & General Data	1
2	Structure’s dimension and Areas	3
3	Data Inputs and Factors	2
4	Final Results Report	1
5	Maps	1
6	Locked Data	-
Total		8

First: Cover & General Data Worksheet

Cover & General Data Worksheet

• This worksheet lists the other worksheets in an index as shown in below figure, you can go to a specific worksheet by just clicking on its name.

• You can email me directly by clicking on the phrase “Designed by: Ali Hassan” for any inquiries or notes. 

• This worksheet includes also the general data about the project and the designer which must be filled for every new project designed by this excel sheet. 

Second: Structure’s dimension and Areas Worksheet

Structure’s dimension and Areas Worksheet-1

Structure’s dimension and Areas Worksheet-2

Structure’s dimension and Areas Worksheet-3

• This worksheet calculates the lightning collection areas based for the following:

1. Basic structure,
2. Adjacent structure,
3. Power lines (max. two),
4. Telecommunication line.

And give hints when calculating the collection areas in case of a structure as part of a building.

• The collection areas are calculated based on the following:

1- Type of the structure:
Type of the structure can be selected from the two dropdown menu type of basic structure or type of adjacent structure (in the case of existence of adjacent structure), the type of structures are:

1. Rectangular with a flat roof
2. Rectangular with a pitched gabled roof
3. Cylindrical
4. Complex with a know collection area

2- The entered dimensions of the structure:
You can enter the dimension of the structure under design in the green cells under the selected type of structure.

Important in the adjacent structure page#2 Don’t forget to choose the right answer from the dropdown menu of the question “Does an Adjacent Structure Exist?”. If you select Yes then proceed with calculation steps. But if you, select No all the adjacent collection area will equal zero even if you enter some dimensions in the input green cells.

Important in the Lines (power and/or Telecommunication) page#3 Don’t forget to choose the right answers from the dropdown menus of the three questions: Does a Power Line#1 Exist? Does a Power Line#2 Exist? Does a Telecommunication Line Exist? If you select Yes for the above questions then proceed with calculation steps. But if you, select No all the adjacent collection area will equal zero even if you enter some dimensions in the input green cells. If you don’t know the length of the line enter 1000. Use the hints given when calculating the collection areas in case of a structure as part of a building.

Third: Data Inputs and Factors Worksheet

Data Inputs and Factors Worksheet-1

Data Inputs and Factors Worksheet-2

• This worksheet includes the following Data:

1. Data Inputs in Green Cells,
2. Values of Parameters/Factors to be selected from the dropdown menus,
3. Calculated Parameters/Factors.

These data are categorized under the following categories:

1. Structure's Attributes,
2. Environmental Influences,
3. Service Lines,
4. Protection Measures,
5. Loss of Human Life (L1),
6. Loss of Service to The Public (L2),
7. Loss of Irreplaceable Cultural Heritage (L3).

Important When you select the cell of any Parameter/Factor, a comment will appear to explain the meaning of this Parameter/Factor or to help you selecting the right value. If you don’t know the value of Ng (lightning ground flash density), enter the value of Td (thunder storm days/year). The NG Map and isokeraunic maps may vary each year and you must get the most recent one as possible for better results. See NG Map and isokeraunic maps in Worksheet "Maps" for guidance. If the selected value from dropdown menu results in a value like “select value from 0.005 to 0.001” as in dropdown menus of probability PSPD and probability PEB, then you must enter your value in the cell to the right which has a comment showing this action.

Fourth: Final Results Report Worksheet

Final Results Report Worksheet

• This worksheet helps you to calculate the following:

1. Number of dangerous events per year,
2. Probability of damage to structure,
3. Loss of Human Life (L1),
4. Loss of Service to the Public (L2),
5. Loss of Irreplaceable Cultural Heritage (L3),
6. Risk Components for R1,
7. Risk Components for R2,
8. Risk Components for R3,
9. Primary Risks Values R1, R2, R3,
10. Need for Lightning Protection Result.

• The Need for Lightning Protection Result for each type of Risk will be one of the following:

1. Install lightning protection measures in order to reduce the calculated Risk R, this phrase will appear in red color as a warning sign.
2. Structure is adequately protected for this type of loss and no lightning protection is required, this phrase will appear in green color as it is an approval for the design.

Fifth: Maps Worksheet

Maps Worksheet

This worksheet show two maps for assistance in determination of lightning ground flash density (Ng) and isokeraunic number (thunder storm days/year).

Important The NG Map and isokeraunic maps may vary each year and you must get the most recent one as possible for better results.

Sixth: Locked Data Worksheet

This worksheet includes the tables used in this excel sheet which extracted from IEC 62305.

2- NFPA 780 Simplified Method Excel Sheet

• The Lightning Risk Calculator for Initial Screening is based on the Lightning Risk Assessment methodology of the NFPA 780 version 2011, the American Standard for the Installation of Lightning Protection Systems. 

• The risk assessment assists you in determining the need of a Lightning Protection System (LPS) and the risk of damage due to lightning.

Important The methodology considers only the damage caused by direct strike to the building or structure.

This excel sheet includes the following parts:


1- Lightning Flash Density (Ng) Part

In this part enter the yearly number of flashes to ground per km²

Parts#1 and 2

2- Equivalent Collective Area (Ae) Part 

Use this part for:

• Calculation of the Equivalent Collective Area (Ae) for a Rectangular Structure based on the entered dimension (length, width and height).

• Calculation of the Equivalent Collective Area for a Structure with protrusions (Ae) as follows:

1. Calculation of the Equivalent Collective Area (Ae) for the Structure without protrusions,
2. Calculate the collective area of the protrusion (Ae’),
3. The maximum value of (Ae or Ae') will be taken into account.

3- Lighning Strike Frequency (Nd) Part

It includes:

1. Factor C1 dropdown menu where C1 = facilitating environmental coefficient depending on the location of structure and the objects within the distance 3H from the structure that can affect the Ae.
2. Nd calculation.

Parts#3, 4 and 5

4- Tolerable Lightning Frequency (Nc) Part

It includes:

1. C2 = Structural coefficient dropdown menu,
2. C3 = Structure Contents Coefficient dropdown menu,
3. C4 = Structure Occupancy Coefficient dropdown menu,
4. C5 = Lightning Strike Consequence Coefficient dropdown menu,
5. Nc calculation.

5- Conclusion Part

The following risk evaluation should be considered as initial screening method. In this part, Comparison is made between Nd and Nc as follows:

Nd ≤ Nc LPS OPTIONAL

Nd > Nc LPS NEEDED

To download your copy of IEC62305-2 Need for Lightning Protection Excel Sheet, please click on the link. To download your copy of NFPA 780 Simplified Method Excel Sheet, please click on the link. Note: Non-registered members in Electrical-Knowhow website will not be able to open and use this excel spreadsheet. To register as a member of Electrical-Knowhow website, do the following: Click on the phrase “Join this site" in bottom of the home page, above the images of our members. After finishing your registration send email to ali1973hassan@yahoo.com, asking for your password and I will send it with email reply. Notes for downloading our files: click on the file name that you want to download, a new page will open, don't care about the message "these files are temporary unavailable", Look in the upper right corner of the page, you will find an arrow point to down,  Click this arrow the download will start automatically, After downloading, you will need to enter your password again to open the file.

In the next Article, I will explain how to perform the Risk Assessment Study by using Online Calculators Method. Please, keep following.

Back To Course Lightning-2: Lightning Protection System Design and Calculations