How to calculate SPL with filters in Excel

Status
This old topic is closed. If you want to reopen this topic, contact a moderator using the "Report Post" button.
Hi there,
Maybe this is stupid question but I have never been good in math.
I made xls spreadsheet where I can calculate SPL, excursion, impedance and power of driver in sealed boxes in function of frequency. I am struggled becouse I also would like to calculate transfer function of various filters in Excel and to plot its influence on SPL curves. Unfortunately I cannot find clear and easy to implement set of formulae to calculate it in function of frequency giving Q, f and n of the filters. Do you know good source of such formulae? Thanks!

What I need to do (this is only an example):

filter:
Butterworth
order:
2
corner freq:
10 Hz

freq. raw SPL filtered SPL
10 Hz 60 dB 55 dB
11 Hz 62 dB 53 dB
12 Hz 64 dB 51 dB
13 Hz 66 dB 49 dB

Thanks!
 
This guy talks about doing complex number calculations in Excel:

Complex Numbers in Excel | EngineerExcel

If you don't want to go through all that, you could use these formulas (see enclosed picture).
 

Attachments

  • FilterCalculations.png
    FilterCalculations.png
    21.3 KB · Views: 179
This guy talks about doing complex number calculations in Excel:

Complex Numbers in Excel | EngineerExcel

If you don't want to go through all that, you could use these formulas (see enclosed picture).

Hi, this works nice, thanks! However it is still only 2nd order. Are there a formulae which involves filter order into calculations? For example when designing odd order Butterworth slopes?
 
I became very skilled at Excel filter calculations when I wrote my "Active Crossover Designer" Excel tools. See my web page:
the Active Crossover Designer web page

You need to be able to calculate the filer response using complex numbers. The complex number includes an amplitude and phase component. At the end you back out the scalar quantities magnitude and phase from the complex number and convert magnitude to SPL and phase to degrees.

Excel has a set of built in complex number functions that are very complete, but a bit clumsy to work with. My ACD spreadsheets do not include any hidden sheets and all formulas can be looked at. You are welcome to use them as a resource on how to do filter calcs in Excel, and then create your own version or feel free to use ACD.
 
Great Thanks Charlie!

Your formulas and overal level of Excel operations inside are well beyond my level of understanding and imagination. :) I've got few inspirations from you for troubleshooting and handling of my simple formulas.

I don't know how you've made log-scaled x axis but there it is (I wasn't able to do it on my own) so I've borrowed your graph layout to my spreadsheet if you don't mind. :)

To everyone. I am working on one tool which can help user to design his own magnetic circuit and voice coil inside of driver. It can approximate magnetic flux density inside the gap from given neo magnet ring (or slug) dimensions. User can enter gap dimensions and VC parameters like wire gauge, number of layers, conductor type and shape, VC height etc. Then every parameter can be calculated like BI, Re, geometric Xmax, Qes etc... Then, such a driver with spec'ed by the user cone and motor properities can be simulated in sealed box and compared with production driver's database. It is still much work to be done...
 
Last edited:
Hi, this works nice, thanks! However it is still only 2nd order. Are there a formulae which involves filter order into calculations? For example when designing odd order Butterworth slopes?

If you have the filters in factored form, that is, a cascade of 2nd order and first order sections, then you can just add the dB's provided by each section.

For a single order lowpass filter and highpass filter, we have as you see in the attached picture, where f3 is the 3 dB corner frequency. Of course, if you're looking for a general approach, Charlie's way may be the one for you.
 

Attachments

  • FirstOrderFilters.png
    FirstOrderFilters.png
    20.3 KB · Views: 115
I managed to create this excel tool which is simulating various drivers in sealed boxes. You can apply various filters, low pass, high pass, Linkwitz transform and even set auto-gain for LT output to not exceed input level. Drivers are simulated in two channels (red, green) which can be compared and amp/box channels can be joined to quick comparison of drivers in same boxes or same amplifier power. Many interesting graps is displayed, SPL, magnitude response, electrical and acoustical, power drawn from amplifier (apparent) and power dissipated in driver (true), voltages and power, EQ'ed or not, and power or excursion limited SPL (not shown)...

An externally hosted image should be here but it was not working when we last tested it.


But I still have the trouble in evaluating box stuffing influence on impedance and especially on Qb and Fb values. I did much on my own but I need your help in evaluating formula to calculate Vb (volume) and Fb (resonance f) with given Qb alignment, for example Q=0,5 or 0,71. It would be easy if there was no box losses taken into account. But they are here and I cannot solve the box electrical model in order to find Vb and fb with given Qb (alignment Q) and Qa (absorbtion)...

If someone evaluate such a formula for me I can even give this spreadsheet to him for free. :)
An externally hosted image should be here but it was not working when we last tested it.
 
Last edited:
Can someone help me on influence of driver's inductance on SPL and excursion? Now it is calculated from purely mechanical box-driver model so it can't take inductance into account. I saw in many software that large inductances makes SPL descending at higher frequencies and 'peaky' around box resonance. How to calculate it? Thanks!
 
Status
This old topic is closed. If you want to reopen this topic, contact a moderator using the "Report Post" button.