Calculating the irradiance from a spectrum in Excel

To calculate irradiance in Excel, you need to integrate the area under the spectral irradiance curve over a specific wavelength range. Here is a guide on how to do this for UV-A, UV-B and UV-C:

• Data preparation for the spectra
  • Column A contains the wavelength (in nm).
  • Column B contains the spectral irradiance (in W/m²/nm).
  • Row 1 contains the column headings Wavelength in nm and Spectral irradiance E [W / ( m² nm)]
• Calculation of the total irradiance
  • Integrate the spectral irradiance over the corresponding wavelength range.

Please note that the irradiance in the examples is exemplary. The irradiance depends, for example, on the distance to the lamp and its power.

Step-by-step instructions for calculating the irradiance in Excel from a spectrum

• Download the file and open it in Excel.
• Determine the desired wavelength ranges according to the CIE International Lighting Vocabulary, these are generally
  • UV-A: 315 nm - 400 nm
  • UV-B: 280 nm - 315 nm
  • UV-C: 100 nm - 280 nm
• Integrate the spectral irradiance
• In EXCEL or other spreadsheet software, use the sum to do this. Formula: = Sum (B2:B100)
• Now you need to take the spectral step size into account. In our examples, this is usually 0.5 nm per cell/row.
• Therefore, multiply the sum in this case by 0.5: Formula: = Sum(B2:B100) * 0.5.
• The result is the irradiance integrated from the spectral irradiance curve.
• The unit changes as a result of the integration. W/m²/nm becomes W/m². This is correct, as it was multiplied by the wavelength in nm.

Note:

In our spectral data, which is available for many lamps and LEDs, the spectral step size is generally constant. This can be obtained, for example, by measuring with a scanning spectrometer or a double monochromator. Array spectrometers, on the other hand, have a spectral step width that changes over the wavelength range.

To take this into account, create an auxiliary column for the spectral step size.
This is called the delta wavelength.

Instructions for calculating the irradiance in Excel for an array spectrometer

• Create a new column C for the delta wavelength:
• In row 3, cell C3, formula: = A3-A2
• Drag the formula to the last row.
• Now adjust the missing cell C2: Formula: = C3
• Here we assume the same spectral step size and avoid an empty cell on the one hand, but also a high numerical value if we would alternatively obtain a difference towards 0 at the end of the column.
• Now multiply the spectral irradiance in column B for each cell by the delta wavelength in column C. Formula: = A2 * C2
• Only now do you calculate the sum in column C. Formula: = Sum(C2:C100)
• The result is again the irradiance that has been integrated from the spectral irradiance curve with non-equal spectral step widths.

If you have any difficulties with the implementation or require further details, please do not hesitate to contact us.