# Excel functions for sound levels

These are the functions included with the dBMacros Excel Add-in.

## 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.

### =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.

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)

A simple function to easily add milliseconds to the entered time.

### =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.

### =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.