Lighting Design Calculations by Using Excel Spreadsheets – Part Two

In the previous articles in this course “ Advanced Course for Lighting Design - Level I ", I explained the lighting design calculations by using (4) different methods in the following articles: 

  1. Lumen (Room Cavity) method - Part One
  2. Lumen (Room Cavity) method - Part Two
  3. Point by point method,
  4. Watts per square foot method.

Also, I explained lighting design calculations by using CalcuLux Indoor software in the following articles: 

I explained the Lighting Design Calculations by Using Excel Spreadsheets- Part One in the previous article: 

Today, I will Continue explaining the lighting design calculations by using excel spreadsheets as follows.

Second: Siemens lighting calculator

I explain this calculator before, so please review the article " 
Lighting Calculations Spreadsheets".

Third: Internal lighting calculator

I explain this calculator before, so please review the article 
" Lighting Calculations Spreadsheets".

Fourth: The Lighting Calculator Ver.2.20

This calculator is developed by the Australian Building Codes Board (ABCB) to assist in developing a better understanding of energy efficiency parameters. 

Most of the lighting designers were searching for such tool to adjust their designs regarding energy efficiency and allowable illumination power densities.

In brief, it is used to test the quality of your design.

Before using the calculator, you must know the Classifications of buildings and structures according to the national construction code (NCC) and Australian Building Codes Board (ABCB) which are as follows:

1- Principles of classification

The classification of a building or part of a building is determined by the purpose for which it is designed, constructed or adapted to be used.

2- Classifications

Buildings are classified as follows:

Class 1: one or more buildings which in association constitute:

(a) Class 1a: a single dwelling being which include:

(i) a detached house; or

(ii) one of a group of two or more attached dwellings, each being a building, separated by a fire-resisting wall, including a row house, terrace house, town house or villa unit; or

(b) Class 1b: a boarding house, guest house, hostel or the like

(i) With a total area of all floors not exceeding 300 m2 measured over the enclosing walls of the Class 1b; and

(ii) In which not more than 12 persons would ordinarily be resident, which is not located above or below another dwelling or another Class of building other than a private garage.

Class 2: a building containing 2 or more sole-occupancy units each being a separate dwelling.

Class 3: a residential building, other than a building of Class 1 or 2, which is a common place of long term or transient living for a number of unrelated persons, including:
(a) A boarding-house, guest house, hostel, lodging house, backpackers accommodation; or 

(b) A residential part of a hotel or motel; or

(c) A residential part of a school; or

(d) Accommodation for the aged, children or people with disabilities; or

(e) A residential part of a health-care building which accommodates members of staff; or

(f) A residential part of a detention centre.

Class 4: A dwelling in a building that is Class 5, 6, 7, 8 or 9 if it is the only dwelling in the building.

Class 5: An office building used for professional or commercial purposes, excluding buildings of Class 6, 7, 8 or 9.

Class 6: a shop or other building for the sale of goods by retail or the supply of services direct to the public, including:
(a) an eating room, cafe, restaurant, milk or soft-drink bar; or 

(b) a dining room, bar, shop or kiosk part of a hotel or motel; or

(c) a hairdresser’s or barber’s shop, public laundry, or undertaker’s establishment; or

(d) Market or sale room, showroom, or service station.

Class 7: a building which is:
(a) Class 7a: a carpark; or 

(b) Class 7b: for storage, or display of goods or produce for sale by wholesale.

Class 8: a laboratory, or a building in which a handicraft or process for the production, assembling, altering, repairing, packing, finishing, or cleaning of goods or produce is carried on for trade, sale, or gain. 

Class 9: a building of a public nature such as:
(a) Class 9a: a health-care building, including those parts of the building set aside as a laboratory; or 

(b) Class 9b: an assembly building, including a trade workshop, laboratory or the like in a primary or secondary school, but excluding any other parts of the building that are of another Class; or
(c) Class 9c: An aged care building. 

Class 10: a non-habitable building or structure including: 

(a) Class 10a: a non-habitable building being a private garage, carport, shed, or the like; or

(b) Class 10b: a structure being a fence, mast, antenna, retaining or free-standing wall, swimming pool, or the like.

3- Multiple classifications

Each part of a building must be classified separately, and including:

(a) it includes:

(i) where parts have different purposes — if not more than 10% of the floor area of a storey, being the minor use, is used for a purpose which is a different classification, the classification applying to the major use may apply to the whole storey; and

(ii) the provisions of (i) do not apply when the minor use is a laboratory or Class 2, 3 or 4 part; and

(b) Classes 1a, 1b, 7a, 7b, 9a, 9b, 9c, 10a and 10b are separate classifications; and

(c) a reference to:

(i) Class 1: is to Class 1a and 1b; and

(ii) Class 7: is to Class 7a and 7b; and

(iii) Class 9: is to Class 9a, 9b and 9c; and

(iv) Class 10: is to Class 10a and 10b; and

(d) A plant room, machinery room, lifts motor room, boiler room or the like must have the same classification as the part of the building in which it is situated. 

4- Parts with more than one classification

(a) Notwithstanding paragraph for multiple classifications, a building or part of a building may have more than one classification applying to the whole building or to the whole of that part of the building.

(b) If a building or part of a building has more than one classification applying to the whole building or part in accordance with (a), that building or part must comply with all the relevant provisions of the BCA for each classification.

Worksheets of The Lighting Calculator Ver.2.20

When opening the calculator, the first screen will provide six navigation options (as in the above image) as follow:

1- Residential lighting calculator NCC volume two, class 2 SOUs and class 4 parts 

This calculator can be used, under NCC volume two, for a class 1 building and class 10a building associated with a class 1 building. It can also be used, under NCC volume one, for a sole-occupancy unit of class 2 building or for a class 4 part of a building.

2- Non Residential lighting calculator NCC volume one 

This calculator can be used for buildings of classes 3 and 5-9 and for the common areas of a class 2 building. It cannot be used for class 2 SOUs or for class 4 part of a building.

3- Illumination power density adjustment factors for a control device

Both calculators provide kinks to this screen, which outlines the information contained in NCC volumes one and two. When using this screen, it is important to note the letter located on the left hand side of each adjustment factor because this letter is used to identify adjustment factors in the drop down menus provided by the calculations.

4- Multiple lighting system calculator NCC Volume one 

Use this calculation sheet to calculate the illumination power load when multiple lighting systems serve the same space.

5- Help Screen 

This option navigates to the help page with its tips using the calculator and links to screenshots with explanatory notes.

6- Worksheet Screen

The worksheet has been made available to record notes or to make other calculations. The use of the worksheet is optional and responsibility for its contents and consequences remains entirely with the user.


Additional information is provided in a table beyond the right hand side of each calculator. This is for information only and is intended to offer the user a greater understanding of the figures involved in producing the final outcome values. The values relate to the individual rows of the related calculator but the information is not designed to be printed out.

How to use The Lighting Calculator Ver.2.20?

1- Adjust the calculator displaying forms to be suitable for your case study as per the following screenshot. 

2- Determine the building class from the classifications explained in above.

3- Adding and changing lighting details: 

  • Enter the description of the building 
  • Enter the classification of the building 
  • Enter the number of table rows preferred 
  • Input the lighting systems, one row at a time, filling in all columns except for: 
  • The perimeter of the space and floor to ceiling height columns, if the room aspect ratio is not going to be used (volume one only); and 
  • The adjustment factor columns, if only one or no adjustment factor is going to be used. 

4- If there are any errors or alert massages regarding the input data, solve it as per the following screenshot:

5- Review the outcome data and solve any problems regarding any fails of design process as per the following screenshots:

6- Print the calculator form by using the File / print menu or the print button on the top icon bar. Noting that:

  • The print area has been preset to allow printing as one or more A4 pages (landscape format), depending on the number of rows displayed in the form. 
  • Some margins may need adjustment to suit some printers. 

Free download:

If you want to download a free copy of The Lighting Calculator Ver.2.20, just click on the link.

In the next article, I will explain Lighting Design Calculations by using On-line Tools. Please, keep following.

1 comment:

  1. What should be Unifrmit ratio Emin/Eav according to NEC?