Prerequisites

IMPORTANT information for Office 97/95 users

1. Before attempting to use this spreadsheet, your copy of Excel must have the Analysis ToolPak (ATP) available.
   This can be done by starting Excel, then select Add-Ins from the Tools drop down menu. Now select both the Analysis ToolPak and Analysis ToolPak - VBA options.
   If you do not complete this step, the spreadsheet will just sit there making rude binary noises at you!
   There are a variety of symptoms that could be seen if the ATP is not installed:
   • The cells containing intermediate calculation values will display #NAME? errors
   • When you switch from one sheet to another, the graph disappears to zero
   • If you press one of the spin buttons to change an input field value, the graph disappears to zero
2. For users with non-English installations of Microsoft Office
   WARNING: Analysis Toolpak function names are language specific!
   Therefore, in order for this spreadsheet to operate correctly in your non-English version of Excel, you must install the ATP Translator Tool written by Eric Desart.
   This can be downloaded from here. Operational instructions can be found on Ron de Bruin's website.

If you are an Office 2007 user, then the above instructions do not apply because the Analysis Toolpak functions are available by default.

General Instructions

• The transfer matrix calculations in this spreadsheet are reimplementations of the MATLAB scripts printed in Appendix B of the first edition of Trevor Cox and Peter D'Antonio's book Acoustic Absorbers and Diffusors. Theory, Design and Application .
• The equations for porous absorbers have been taken from, and cross referenced with, chapter five of the above book.
• The calculations for perforated, slotted and micro perforated panels assume that the sound direction is normal (perpendicular) to the panel surface.
• The calculations for porous absorbers allow the angle of incidence to be changed from 0° to 89°.
• The graphs display the absorption curve calculated over 8 octaves in whole, 1/2, 1/3 or 1/6 octave subdivisions.
• Each sheet allows you to define the starting frequency for the 8 octave display.
• If you wish to see the graph displayed over the standard 6 octaves of 125Hz, 250Hz, 500Hz, 1000Hz, 2000Hz and 4000Hz, simply select "Whole Octave" subdivisions and set the start frequency to 62.5Hz.
• Spin buttons have been provided to make it easier to see how the absorption properties are effected by your changes. Personally, I find the "animation" effect very helpful when looking at the overall behaviour of the absorber as one parameter changes.
• Any field with a yellow background may be typed in, or its value changed by pressing the associated spin button. All other fields are read only.
• If you wish to change an input field by a large amount, you can simply type in the new value. You do not have to hold the spin button down whilst the field slowly increments!
• Once a value has been manually entered into an input field, the associated spin button needs to be clicked on a couple of times in order to resynchronize its value with the new value in the input field. If you don't do this, you could encounter a false range check warning message.
• The spin buttons have built-in range checks:
  • This will prevent you changing the value of an input field in such a way as to contradict another input value.
    For instance, it makes no sense to specify holes at 10mm centres, and then set their radius to 6mm.
  • Range checks are only performed if you use the spin buttons, therefore, when you type in values manually, please make sure that you don't create any contradictory situations.
• If, for security reasons, your installation of Excel has Macros disabled, then you will still be able to use the spreadsheet, but with the following limitations:
  • None of the spin buttons will function.
  • The radio buttons to change the graph subdivisions will not function.
  • None of the calculations on sheet Microperforated Panel will function.

Sheet Porous Absorber

• This sheet shows two absorption curves for a single layer of porous absorber with and without an air gap.
• The angle of incidence can be adjusted. This means you can vary the angle at which the sound waves (plane waves) hit the surface of the porous absorber.
• There are several very important points that must be understood about using this feature.
  1. The angle at which sound hits a surface is measured in a way that may seem a little odd at first. The angle is not measured relative to the surface itself, but relative to an imaginary line perpendicular (at 90°) to the surface.
  2. This imaginary line coming out of the surface of the absorber at exactly 90° is known as the surface "normal". All angles of incidence are measured relative to the surface normal.
  3. Sound hitting any surface at an angle of 0° to the normal is known as "normal incidence" sound.
  4. Sound hitting any surface at an angle greater than 0° but less than 90° to the normal is known as "oblique incidence" sound.
  5. As the angle of incidence increases from 0°, the absorber becomes less and less effective. As the angle increases above 60°, you will see that the absorbency drops off rapidly.
  6. No matter what angle you specify, the absorbency curve will be plotted for sound hitting the surface of the porous absorber at that angle only. This is a greatly simplified representation of reality!
  7. In a real life situation however, sound will be hitting the surface of the porous absorber at all angles simultaneously. This is known as "random incidence" sound.
• Built in limits for spin buttons:

  Dimension	Units	Min	Max	Δ
  Absorber thickness	mm	0.1	500.0	0.1
  Flow resistivity	rayls/m	5	50,000	5
  Cavity depth	mm	0.1	500.0	0.1
  Starting Frequency	Hz	2	79	1
  Angle of Incidence	°	0	89	1

Sheet Perforated Panel

• This sheet shows the absorption curve of a perforated panel Helmholtz resonator, behind which is an absorbent layer.
• The position of the absorbent layer can be either directly behind the perforated panel, or against the rigid backing.
• Three plots are shown on the graph. These are absorption curves for:
  • The absorber directly behind the perforated panel, with the air gap between the porous layer and the rigid backing.
  • The absorber against the rigid backing, with the air gap between the porous layer and the perforated panel.
  • The entire cavity filled with porous absorber. In this case, the specified thickness of the porous layer is ignored, and the absorption is calculated based on the entire air gap being filled with absorbent.
• Comment on minimum perforation size:
  • The perforations in the panel are assumed to be circular and greater than 1mm in diameter.
  • For perforations less than 1mm in diameter, the impedance of the viscous air/panel boundary layer becomes significant, and the transfer matrix functions implemented here become inaccurate.
  • In a micro-perforated situation (diameter < 1mm), a separate absorbent layer behind the panel is no longer required since the absorption is provided by the air in the holes. See sheet Microperforated panel for details of this type of absorber.
• Built in limits for spin buttons:

  Dimension	Units	Min	Max	Δ
  Panel thickness	mm	1.0	50.0	0.1
  Repeat distance	mm	1.0	50.0	0.1
  Hole radius	mm	0.5	25.0	0.1
  Cavity depth	mm	1.0	500.0	0.1
  Absorber thickness	mm	1.0	500.0	0.1
  Flow resistivity	rayls/m	5	50,000	5
  Starting Frequency	Hz	2	79	1

Sheet Slotted Panel

• This sheet shows the absorption curve of a slotted panel Helmholtz resonator, behind which is an absorbent layer. The position of the absorbent layer can be either directly behind the slotted panel, or against the rigid backing.
• Three plots are shown on the graph. These are absorption curves for:
  • The absorber directly behind the slotted panel, with the air gap between the porous layer and the rigid backing.
  • The absorber against the rigid backing, with the air gap between the porous layer and the slotted panel.
  • The entire cavity filled with porous absorber. In this case, the specified thickness of the porous layer is ignored, and the absorption is calculated based on the entire air gap being filled with absorbent.
• Built in limits for spin buttons:

  Dimension	Units	Min	Max	Δ
  Panel thickness	mm	1.0	50.0	0.1
  Slot distance	mm	1.0	100.0	0.1
  Slot width	mm	1.0	100.0	0.1
  Cavity depth	mm	1.0	500.0	0.1
  Absorber thickness	mm	1.0	500.0	0.1
  Flow resistivity	rayls/m	5	50,000	5
  Starting Frequency	Hz	2	79	1

Sheet Microperforated Panel

• This sheet shows the absorption curve of a microperforated panel. The whole purpose of this type of panel is to create an absorber in which no porous layer is required; thus, a transparent absorber can be created that is both acoustically functional, and aesthetically pleasing.
• Since a layer of porous material is not required, this sheet shows only one plot.
• Built in limits for spin buttons:

  Dimension	Units	Min	Max	Δ
  Panel thickness	mm	0.1	50.0	0.1
  Repeat distance	mm	0.5	50.0	0.1
  Hole radius	mm	0.01	2.5	0.01
  Cavity depth	mm	0.5	250.0	0.1
  Starting Frequency	Hz	2	79	1
  Angle of Incidence	°	0	89	1

Sheet Flow Resistivity

• On this sheet, various equations are implemented for estimating the flow resistivity of a porous absorber.
• In order to use these formulæ, two values need to be known (or guessed!) - the material's porosity and the fibre diameter.
• Below the equations, there is an explanation of what the flow resistivity of a porous absorber actually represents, and why it is such an important parameter.
• There is also a brief introduction to the work of Delany and Bazley. Since their formulæ for estimating the (complex) impedance and wave number of a porous absorber are consistently used throughout the entire spreadsheet, I thought it appropriate to provide a short description of the purpose served by these calculations, and how they derive their values.
• Built in limits for spin buttons:

  Dimension	Units	Min	Max	Δ
  Porosity	None	85.0%	99.99%	0.01
  Fibre diameter	μm	1.0	10.0	0.1

Sheet Configuration

• It is on this sheet that you can configure the air temperature and barometric pressure. These are then used to calculate all the variables related to the properties of air.
• The dimensions required as input for each absorber design are shown on the different diagrams.
• Built in limits for spin buttons:

  Dimension	Units	Min	Max	Δ
  Air temperature	°C	-50.0	50.0	0.1
  Atmospheric pressure	Bar	0.800	1.200	0.001

Sheet Acknowledgements

This sheet contains the names and contact details of those individuals who kindly spent some their time to review the pre-release version of this spreadsheet.

All source material is listed here. I believe that all the sources quoted here are accurate, reputable and respected in the field of acoustics.

Version History

Changes in V1.60 - 27^th December, 2020

Conversion of Excel file to Office 365 format

Changes in V1.59 - 19^th Feb, 2012

1. The "Resonant Frequency" field on the Perforated and Slotted Panel sheets has been deleted. This is due to the fact that there was an error in the calculation. Thanks to Ward de Ceuninck of Hasselt University, Belgium for pointing this out.

New features in V1.58 - 4^th Mar, 2011

1. Calculations on the Microperforated Panel sheet now support the European use of the comma as a decimal separator instead of only a dot. Thanks to Christian Borg for making a small, but significant alteration to the VBA coding necessary to achieve this.

New features in V1.57 - 27^th Jan, 2011

1. On sheet Configuration, corrected a discrepancy in the description of "Hole Diameter" for the perforated and microperforated panel diagrams.
   Thanks to Martin �jes for spotting this discrepancy.

New features in V1.56 - 22^nd Feb, 2010

1. Corrected a bug in the implementation of Eq 6.36 (Pg 181, Cox & D'Antonio).
   Many thanks to Dr Jeong-Woo Kim of UTRC, Hartford, CT for spotting the subtle (but significant) discrepancy between Eq 6.36 as printed in Trevor Cox's book and the equation in Maa's original paper.
   Trevor Cox has been notified of this discrepancy, since it does not appear in the book's errata.

New features in V1.55 - 5^th Dec, 2009

1. Corrected a bug in the zbesselj() function.

New features in V1.54 - 28^th Sep, 2009

1. Small (but significant) correction to the calculation on row 33 "Ratio of wavenumbers (K_air:K_{air x} )" of sheet "Porous Absorber".
   Thanks to Ben McClure for tracking down this problem.

New features in V1.53 - 26^th Aug, 2009

1. Excel 2007 supported (mostly!)
2. Dropped support for Excel 95
3. The version of this spreadsheet in Excel 2007 format no longer requires a reference to the Analysis Toolpak functions as these are now built-in to standard Excel.
4. Correction to the implementation of Eq 6.11 on sheet "Slotted Panel".
5. Correction to the implementation of Eq 5.29 "Mass term for air in slots" on sheet "Porous Absorber"
   Thanks to David Oliva Elorza from the Finnish Institute of Occupational Health for identifying this error.
6. Correction to the resonant frequency calculation on sheet "Perforated Panel"
   Thanks to Per Sj�sten from Sound Processing AB in Sweden and Aitor Arumburu from San Sebastian, Northern Spain (Basque Country) for identifying this error.
7. Correction to the calculation for "End Corrected Panel Thickness (t')" on both the "Perforated Panel" and "Slotted Panel" sheets

New features in V1.52 - 12^th Mar, 2007

1. Removal of sheet protection on all calculation sheets.
   This spreadsheet was built using an English installation of Microsoft Office and consequently, the English Analysis ToolPak function names were used in all the cell calculations.
   Unfortunately, Microsoft foolishly decided to translate all ATP function names into the target language for non-English versions of Excel. As with all politically correct decisions, this has ended up creating more division than unity because now all spreadsheets that use the ATP become non-functional in any installations of Excel other than those in the original language.
   To combat this problem, Eric Desart created the ATP Translator tool that dynamically adjusts the ATP function names from any one of 11 source languages into the language of the local Excel installation. However, this functionality relies on the fact that the sheets within the spreadsheet are not protected. Hence the removal of sheet protection.
2. There are no functional changes to this version of the spreadsheet.

New features in V1.51 - 17^th Jan, 2006

1. Two corrections made to the calculations on sheet Microperforated Panel. This sheet now produces exactly the same results as the MATLAB script 6_3 in Appendix B of Cox & D'Antonio.
2. Angle of incidence field added to sheet Microperforated Panel.
3. Layout of sheets Perforated Panel and Slotted Panel rearranged slightly to increase size of chart area.
4. Various small performance and accuracy improvements made to all panel calculation sheets.
5. Small improvements made to the VBA implementation of the complex Bessel function.

New features in V1.5 - 5^th May, 2005

1. Sheet Microperforated Panel added. This sheet references equations in chapter 6 of Cox & D'Antonio and has been checked for accuracy by comparing its results with those produced by the MATLAB script in Appendix B.6
2. The lower limit built into the "Start graph at" spin button has been lowered to 2Hz on all relevant sheets.
3. The sheet names for Perforated Absorber and Slotted Absorber have been renamed to Perforated Panel and Slotted Panel.
4. Grammatical correction to the wording on sheet Flow Resistivity.
5. Correction to units of the input field "Absorber flow resistivity" from "rayls" to "rayls/m".
6. New diagram added on sheet Configuration to illustrate the dimensions of the microperforated panel.

New features in V1.4 - 29^th October, 2004

1. The absorption plots for a Perforated Absorber and a Slotted Absorber have been extended to allow performance comparisons to be made depending on the position of the porous layer within the structure of the absorber.
2. The calculations for a Slotted Absorber have been reworked to use a transfer matrix.
3. Equation references added for sheets Perforated Absorber and Slotted Absorber.
4. Correction to the formula for Rschevkin's term for the end correction of a perforated membrane.
5. Slight rewording of the explanations on sheet Flow Resistivity.
6. New diagrams added on sheet Configuration to illustrate the different porous layer positions.

New features in V1.31 - 12^th July, 2004

1. Correction to the formula for calculating the speed of sound. Thanks to Dr. Andreas Foerster for bringing this to my attention.

New features in V1.3 - 2^nd July, 2004

1. For sheet Porous Absorber, the angle of incidence can now be adjusted from 0° to 89°. It must be clearly understood that the resulting absorbency curve is applicable only for sound hitting the surface of the porous absorber at that one angle. This is a greatly simplified version of reality!
2. Sheet Flow Resistivity added. This implements various equations for estimating the flow resistivity of a porous absorber.
3. There is some explanatory text that describes what flow resistivity actually is, and why it is such an important parameter when calculating the acoustic properties of a porous absorber.
4. There is also a brief description of Delany and Bazley's empirical formulæ for estimating the (complex) impedance and wave number of a porous absorber. Delany and Bazley's formulæ are used extensively throughout the spreadsheet, so it seems appropriate to add some explanation about the purpose they serve and how they derive their values.

New features in V1.2 - 1^st July, 2004

1. Spreadsheet renamed from Helmholtz Calculator to Porous Absorber Calculator.
2. Sheet Porous Absorber added. Equation references have been included against the calculation values to provide a cross reference with chapter 5 of Cox and D'Antonio's book and make it easier to follow the working.
3. Each sheet has an extra graph showing the real and imaginary parts of the overall impedance (scroll down). Zero crossing of the imaginary part usually indicates the frequency at which the system will resonate and therefore achieve maximal absorption.
4. Reflection factor and absorption coefficient calculations have been altered to come into line with the material in chapter 1 of the above book.
5. Sheet Intermediate Values has been dropped. All the intermediate calculations steps are now found on the respective sheets (scroll right).

New features in V1.1 - 18^th May, 2004

1. Several corrections made to the slotted absorber calculation!
2. Dimensions can be viewed in inches
3. Graphs can be plotted in whole, half, third or sixth octave sub-divisions.

First release V1.0 - 14^th May, 2004

1. Correction to the max/min limits of various spin buttons.
2. Octave bands changed to 1/3 subdivisions
3. Input changed to millimetres instead of fractions of metres.

Prerelease V0.9 - Released to reviewers only

1. Absorption curve plotted over 8 octaves (1/6 octave bands) of a perforated Helmholtz absorber.
2. Absorption curve plotted over 8 octaves (1/6 octave bands) of a slotted Helmholtz absorber.
3. Input distance available only as fractions of metres.