These are the functions included with the dBMacros Excel Add-in.
These functions are free! No license is required. Simply install the Add-in on the Downloads page.
Basic functions
=dBAverage(dBValues) or =Leq(…)
A decibel version of Excel’s =AVERAGE(…) function
This performs a decibel logarithmic average on the input values. Only by counting cells which contain data and assuming that all sound levels are of equal duration.
Useful for calculating a custom time period Leq from many rows of noise data
e.g. =dBAverage(A1:A10), =dBAverage(30,40,35)
=dBAvBlanks(dBValues)
This is an alternative to the dBAverage function which includes blank cells in the count, i.e. as if the sound during the blank rows are insignificant in level.
Useful for calculating a custom time period LAeq after events were removed by clearing the cell values. This calculation will always be lower than, or identical to, the =dBAverage(…) result
=dBSum(dBValues)
A decibel version of Excel’s =SUM(…) function
This performs a decibel logarithmic sum on the input values.
Useful for turning spectrum bands into an overall Leq level
e.g. =dBSum(A1:A10), =dBSum(30,40,35)
=dBSubtract(dBValue1, dBValue2)
Subtract 1 decibel from another i.e. dBValue1 – dBValue2
e.g. =dBSUBTRACT(A1, A2)
=dBDivide(dBValues, DivideBy)
Divide a decibel value by the given value.
Useful for calculating the individual level of multiple sources of equal sound power
e.g. =dBDIVIDE(A1,10)
Weighting functions
=AWeight(dBvalue, CentreFrequency)
Apply A-Weighting to the given decibel value based on the centre frequency.
“CentreFrequency” can be in the following formats:
- 1000
- 1000Hz
- 1000 Hz
- 1k
- 1 k
- 1kHz
- 1 kHz
- 1.0k
- 1.0 k
- 1.0kHz
- 1.0 kHz
e.g. =AWEIGHT(A1,”31.5Hz”)
For just the weighting value use =AWeightValue(CentreFrequency)
For C-Weighting use =CWeight(…)
=dBSumA(dBValues, CentreFrequency, [Octaves])
Applies incrementing A-Weighting to octave or 1/3rd octave spectrum data and then performs a decibel logarithmic sum.
Useful for turning spectrum bands into an overall LAeq in one step.
Essentially, it combines the =AWeight(…) and =dBSum(…) functions.
The A-Weighting frequency band increments starting with the first CentreFrequency. The optional octaves value will define how frequency bands are incremented.
The default value for Octaves is 3, which steps up 1/3 rd octave bands, e.g. 31.5Hz, 40Hz, 50Hz, etc.
An input of 1 for Octaves, to step up whole octave bands, e.g. 31.5Hz, 63Hz, 125Hz, etc.
2 is a special case for converting 1/3rd octaves to A-Weighted 1/1 octave bands, e.g. =dBSumA(A1:C1, 31.5, 2) where A1, B1 and C1 are the values for the third octave bands 25Hz, 31.5Hz and 40Hz
For C weighting use =dBSumC(…) instead
Statistical functions
Important: When using the statistical functions with source data over a short measurement period (i.e. not many rows of data) or a long time interval (e.g. above 1 second per row of data), the results might differ slightly from the sound level meter. This is because a sound level meter calculates the statistics, such as the L90, from the “Fast” time weighted levels (125ms).
=LPercentile(dBValues, Percentile) or =LX(…)
This is similar to Excel’s =Percentile(…) function, but with a “Percentile” value that complies with the sound level meter norms, e.g. 10, 50, 90
Note: The function is not =LN(…) which is already in use for performing the natural logarithm
=LPercentile90(dBValues)
Useful for BS4142 assessments when it’s necessary to calculate a background level LA90 from raw data. It is identical to =LPercentile(dBValues, 90)
Note: The function is not =L90(…) which is already in use for referencing the cell L90
Noise Mapping functions
The following functions are useful for noise mapping.
=Lw(…) or =SoundPower(SoundPressure, Distance, [Q, Facade, isLineSource])
=Lp(…) or =SoundPressure(SoundPower, Distance, [Q, Facade, isLineSource])
Calculate Sound Power Level (Lw) based on Sound Pressure Level (Lp) and vice versa assuming only reduction in level due to distance. The formula includes:
Distance in m.
Optional Q value:
- 1 for Whole space
- 2 for Half space
- 4 for Quarter space
- 8 for 8th Space
The default is 1.
Optional Facade of True/False to add 3dB
The default is False
Optional isLineSource value of True/False
The default is False (point source)
e.g. =SoundPower(A1,10,1)
=dBDistance(dBValue, Distance, TargetDistance)
Adjust a sound pressure level for distance attenuation using the inverse square law. Input the known value, the distance at this value and the target distance in metres.
e.g. =dBDistance(60,10,100)
Time functions
When dealing with milliseconds in Excel, you can sometimes encounter rounding errors due to the way Excel stores time data and calculates milliseconds.
=TimeExtra(hour, minute, second, millisecond)
Expanding upon Excels native =TIME(hour, minute, second) function for constructing time data from hours, minutes and seconds.
=TimeExtra adds a fourth variable for milliseconds.
e.g. =TIMEEXTRA(0,0,0,100)
=TimeAddMS(TimeValue, milliseconds)
A simple function to easily add milliseconds to the entered time.
e.g. =TimeAddMS(A2, 100)
=TimeRoundToMs(…) and =TimeRoundToSecond(…)
These are obvious enough and can be used to tidy up time data with milliseconds that have fallen foul of the rounding errors in Excel.
Advanced functions
=dBAvDurations(dBValues, durationValues)
Calculate the average decibel level with respect to period lengths. Note: duration values should be valid time values.
Useful for calculating an LAeq from multiple LAeqs of different time lengths.
e.g. =dBAVDurations(B:B, A:A)
=dBAvFilter(TimeValues, dBValues, TimeStart, TimeEnd, [DurationValues, InspectResult])
Calculate the average decibel level but only when considering records between two times of the day.
timeStart and timeEnd can be time as a string (e.g. “10:00”) or hour as a number (e.g. 10).
If timeStart > timeEnd then the period outside of this range is considered instead.
timeValues should be start times that stop before the hour changes.
Optional durationValues will adjust for the duration of the time period. This is the same as =dBAVDurations(…)
This is a helper function used by the Lden functions: =Lday(…), =Levening(…) and =Lnight(…)
e.g. =dBAVFilter(A:A, B:B, “10:00”, “11:00”)
=Lden(TimeValues, dBValues, [DurationValues, NightHour, InspectResult])
Calculate a decibel average with a penalty added to the 4 evening hours (+ 5dB) and 8 night-time hours (+ 10dB).
Useful for performing Lday(…), Levening(…) and Lnight(…) functions and adding penalties in one step.
Requires valid time data (assumes start time).
Optional durationValues for adjusting for variable period lengths, otherwise all levels are assumed equal in time length.
NightHour is the number of the hour when night-time begins and defaults to UK times where Night starts at hour 23 and the periods are:-
Evening – 19:00 to 23:00
Night – 23:00 to 07:00
Note: Lden is intended to be derived from a year of measurements.
Use inspectResult=True to check lengths and get more information about the calculation result.
e.g. =Lden(A:A, C:C)
=LdenPeriod(TimeValue, NightHour)
This will return the value “Day”, “Evening” or “Night” for any timestamp. Used by the Lden function.
=CNEL(…) and =CNELPeriod(…)
These are similar to the Lden functions except the periods are for the “Community Noise Equivalent Level” where the evening is 3 hours long, the night is 9 hours long and the night starts at 10pm.
These functions are free! No license is required. Simply install the Add-in on the Downloads page.