
Home  Forums  Rules  Articles  diyAudio Store  Blogs  Gallery  Wiki  Register  Donations  FAQ  Calendar  Search  Today's Posts  Mark Forums Read  Search 
Software Tools SPICE, PCB CAD, speaker design and measurement software, calculators 

Please consider donating to help us continue to serve you.
Ads on/off / Custom Title / More PMs / More album space / Advanced printing & mass image saving 

Thread Tools  Search this Thread 
25th September 2017, 03:10 PM  #1 
diyAudio Member
Join Date: Nov 2014

How to calculate SPL with filters in Excel
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! 
25th September 2017, 11:44 PM  #2 
diyAudio Member
Join Date: Jun 2002
Location: USA, MN

2nd order transfer functions
HP H(s)=s^2/(s^2+s*wo/Q+wo^2) LP H(s)=wo^2/(s^2+s*wo/Q+wo^2) wo=filter cutoff s=complex frequency Q is Q Butterworth Q is 0.707 Linkwitz Riley 0.5 Bessel 0.577
__________________
Our species needs, and deserves, a citizenry with minds wide awake and a basic understanding of how the world works. Carl Sagan Armaments, universal debt, and planned obsolescencethose are the three pillars of Western prosperity. —Aldous Huxley 
26th September 2017, 11:29 AM  #3 
diyAudio Member
Join Date: Nov 2014

Thanks,
But still I quite don't know how to interpret "complex frequency" term. If I substitute 's' with sound frequency in cycles per second (Hz) I am still getting wrong results from this formulae. 
26th September 2017, 01:09 PM  #4 
diyAudio Member
Join Date: Oct 2008

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).
__________________
www.akitika.com Featuring the GT102 Power Amp and PR101 Preamp kits www.updatemydynaco.com Featuring upgrades for classic Dynaco Solid State Equipment 
26th September 2017, 01:35 PM  #5 
R.I.P.
Join Date: Jul 2004
Location: Scottish Borders

your results Raw vs Filtered show the SPL going down as frequency increases.
This seems to be a low pass filter. Surely your 10Hz filter should be a high pass.
__________________
regards Andrew T. 
26th September 2017, 04:19 PM  #6  
diyAudio Member
Join Date: Nov 2014

Quote:


26th September 2017, 05:07 PM  #7 
diyAudio Member
Join Date: May 2008


26th September 2017, 05:11 PM  #8 
diyAudio Member

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. 
26th September 2017, 08:07 PM  #9 
diyAudio Member
Join Date: Nov 2014

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 logscaled 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 by Windforce85; 26th September 2017 at 08:10 PM. 
26th September 2017, 08:08 PM  #10  
diyAudio Member
Join Date: Oct 2008

Quote:
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.
__________________
www.akitika.com Featuring the GT102 Power Amp and PR101 Preamp kits www.updatemydynaco.com Featuring upgrades for classic Dynaco Solid State Equipment 

Thread Tools  Search this Thread 


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
How to calculate series notch filters  merlin el mago  MultiWay  41  14th March 2014 11:47 AM 
anyone know how to calculate this??  kendt  Construction Tips  2  7th January 2014 08:04 PM 
Calculate SPL  exuviae  Subwoofers  3  5th July 2006 07:51 AM 
help ! how to calculate??????????  bogdan_borko  Solid State  3  18th January 2006 10:15 PM 
EMI ac line filters & RFI ac line filters. Are they one and the same?  KevinLee  Parts  1  8th January 2003 12:18 AM 
New To Site?  Need Help? 